Download "Горизонтальная фильтрация столбцов в Excel"

"videoThumbnail Горизонтальная фильтрация столбцов в Excel

(Partie 4) Application simplifiée de gestion de stock : Création de A à Z [Excel et VBA]

(Partie 4) Application simplifiée de gestion de stock : Création de A à Z [Excel et VBA]

Channel: Excel & VBA pour Tous !

EXCEL+VISIO+VBA Работаем в Excel - рисуем в Visio

EXCEL+VISIO+VBA Работаем в Excel - рисуем в Visio

Channel: Vigil IT-video

Excel

filtering

VBA

фильтрация

столбцы

по горизонтали

horizontally

00:00:02

and with you again I am Nikolai Pavlov trainer and

00:00:04

expert on microsoft office products

00:00:06

author of the project planet excel dot ru on

00:00:09

2 teplex with a bunch of useful macros and

00:00:11

several books and online courses on this

00:00:14

topic if you are not exactly a novice

00:00:17

user then You must have already noticed

00:00:19

that 99 percent of everything in Excel is

00:00:22

designed to work specifically with

00:00:23

vertical tables where

00:00:26

information about objects or events is located in lines, the

00:00:29

so-called records of the

00:00:31

apostles, parameters or attributes of these

00:00:34

objects, as they are also called fields,

00:00:36

pivot tables, subtotals, even

00:00:39

copying formulas double-click down

00:00:41

all of this is tailored specifically to this

00:00:44

data format; however, as almost always, there are no

00:00:47

rules without exceptions from a fairly

00:00:49

regular periodicity, they

00:00:51

ask me what to do if in my work

00:00:54

I come across a table with a horizontal

00:00:56

semantic orientation, or a table

00:00:58

where the rows and columns have the same

00:01:01

meaning weight, well, for example, this is and if

00:01:04

you sort horizontally in Excel,

00:01:06

somehow I somehow learned on the

00:01:09

data tab you need to go to sorting and then

00:01:12

in the parameters window that opens and select

00:01:14

sorting not by rows and columns,

00:01:16

then filtering, for example, everything is

00:01:20

much worse than the built-in

00:01:22

filtering tools there are simply no columns or rows in Excel,

00:01:24

so if you are

00:01:26

faced with such a task, you will have to come

00:01:28

up with workarounds and crutches of

00:01:31

varying degrees of reporting, let us go

00:01:34

through them and go over them

00:01:36

if you are working on the new version of Excel

00:01:39

2021 or in an Office 365 subscription, then you can

00:01:43

use the recently appeared The

00:01:45

filter function is able to filter the

00:01:48

source data not only by rows but

00:01:51

also by columns. To work, this function

00:01:53

requires an auxiliary horizontal

00:01:56

one-dimensional array of a row where each

00:01:59

element will be a logical value

00:02:01

true or false that determines

00:02:04

what is shown or, on the contrary, we hide the

00:02:06

next column, let’s say you and I

00:02:09

always want to display the first and the

00:02:11

last columns are

00:02:13

city and totals in our table, so for

00:02:15

them in the corresponding cells of our

00:02:17

array we enter the value equal to true

00:02:23

for the remaining columns, the contents from the

00:02:25

following cells will be formulas that

00:02:28

check the conditions we need, suppose

00:02:31

we want to filter the columns to show

00:02:33

only those of them where the value of the total for the

00:02:36

corresponding manager lies in

00:02:38

the range from 500 to 700, respectively,

00:02:41

the formula that checks this condition

00:02:43

will look like this:

00:02:45

we use a logical function and since

00:02:49

in this case we need both conditions to

00:02:50

be met, and then

00:02:53

we will set these criteria separated by a comma; the

00:02:55

total amount for 1 manager

00:02:58

must be greater than or equal to the first

00:03:02

value from, that is, 500 and through the point,

00:03:05

and this same value, that is, cell e14,

00:03:08

we must have less than or equal to the

00:03:11

upper limit, that is, 700 so that

00:03:14

later this formula can be

00:03:16

extended to the right with a black cross to

00:03:17

all other managers, naturally, you

00:03:20

need to secure links to yellow cells,

00:03:22

use the f4 key on the keyboard for this,

00:03:26

press enter, drag the formula to the right and

00:03:30

get the logical values false and

00:03:33

true, which later for

00:03:35

filter functions will be an indicator of whether or

00:03:38

not the corresponding column should be displayed,

00:03:39

and all that remains is to actually

00:03:41

enter this function, select the empty cell

00:03:44

below the table for example, let’s enter a

00:03:47

filter, the first argument here is our

00:03:50

filter ayat table, the second argument is separated

00:03:53

by a comma, a prepared

00:03:55

horizontal array of criteria, and

00:03:58

the third optional argument is the value

00:04:00

that needs to be output if nothing was

00:04:02

included in the results during filtering, but

00:04:05

we obviously can’t have that,

00:04:07

so we close the bracket and click to Anton,

00:04:10

now if you change the selection criteria, for

00:04:12

example, instead of 500 write 300, our

00:04:16

function will filter only those

00:04:18

columns that fall under these criteria,

00:04:20

developing the same idea can be

00:04:23

easily implemented in a completely similar

00:04:25

way

00:04:26

and filtering columns by their names, that

00:04:28

is, by a given list, let’s assume

00:04:31

with us You have a separate smart table

00:04:32

called my columns where

00:04:35

the names of the columns that need to be

00:04:37

displayed are listed. In this case, to fill out the

00:04:40

horizontal array of indicators, the

00:04:42

standard rural function

00:04:44

count will help if those checking the entry of the

00:04:46

next column name from the header into

00:04:49

our allowed list, the formula will

00:04:51

look like count if and as the

00:04:55

first argument we indicate the range

00:04:57

with which we are looking, it was called my

00:04:59

columns and as the second, we are looking for the

00:05:02

header of the next column from the header and

00:05:06

then we need to check whether

00:05:08

something was found in the results, that is, the

00:05:10

number of matches found is greater than zero or

00:05:12

not, we all click on enter we copy our

00:05:15

formula to the right over the entire horizontal

00:05:18

array, we get the truth opposite the text of

00:05:21

the dogs that were in the list and

00:05:24

reuse the already familiar filter function, the

00:05:27

first argument, if you remember, our

00:05:29

table is filtered, it is 2 horizontal

00:05:33

array of indicators true and false for

00:05:35

each column, we all press enter and

00:05:38

we get in the results only those columns

00:05:40

that were in the allowed list of

00:05:42

employees when adding a new

00:05:43

person,

00:05:46

it naturally immediately appears as a

00:05:48

column in the results of our filtering.

00:05:51

In fact, when at the beginning of this

00:05:53

video I said that excel does not have

00:05:55

built-in tools for

00:05:57

horizontal filtering of columns, I’m

00:05:59

listening a little because that in fact,

00:06:02

horizontal filtering in Excel has been around for a

00:06:04

very long time, just not in ordinary tables, but in

00:06:06

pivot tables, so if we

00:06:08

manage to convert our original

00:06:10

table into a pivot table of a similar type, then we

00:06:13

can use all this

00:06:15

built-in functionality for such a

00:06:17

transformation; however, our original table

00:06:19

must satisfy several

00:06:21

conditions, namely to have the correct 1-line

00:06:24

header line without empty and

00:06:27

merged cells, otherwise it simply

00:06:29

will not be possible to build a pivot table, do not

00:06:32

contain duplicates in the row and

00:06:34

column labels, that is, repetitions among cities

00:06:37

and managers, pivot tables so that repetitions

00:06:39

collapse into a list of only unique

00:06:42

values and the third condition is to create the fact

00:06:44

that our data should contain only

00:06:46

numbers in the range of values, that is, at the

00:06:49

intersection of rows and columns, since the

00:06:51

pivot table must necessarily apply

00:06:53

some kind of aggregated function to them, there is the

00:06:55

sum of the average minimum maximum and with

00:06:57

text, as you understand, this function

00:06:59

of course will not work if all these

00:07:01

conditions are met, then to build a

00:07:04

summary table that is as similar in appearance as possible to

00:07:06

our original table, you will need to

00:07:09

expand this original table from a cross

00:07:12

table into a flat one, or, as they also say,

00:07:14

normalize in the English version from

00:07:16

it is called a pilot, and this is easiest to

00:07:18

do using the Park Vere add-on, a

00:07:20

powerful tool for converting any

00:07:23

data built into excel starting from

00:07:25

2016 versions, select any cell inside

00:07:28

our data, press the key combination

00:07:31

kontrol t and enter to

00:07:34

turn it into a smart table and load it

00:07:37

into the park on the data tab using the

00:07:39

button from the range table

00:07:43

on top of the microsoft excel window will open the

00:07:45

query editor window for work very in

00:07:48

which you can immediately get rid

00:07:50

of the totals line first, we don’t need it

00:07:52

since the pivot tables will still have

00:07:54

their own line with the totals, for this let’s

00:07:56

filter by the city column and

00:07:59

I have a checkmark and that’s

00:08:02

it -secondly and most importantly, let’s

00:08:04

undo the collapse, that is,

00:08:06

normalize or expand this

00:08:08

two-dimensional table into a strip; for this, in the

00:08:10

park, just right-click

00:08:12

on the header of the first column

00:08:15

city and select the command to undo the

00:08:18

collapse of the other columns; all are not

00:08:22

selected and the columns, as you can see,

00:08:23

are transformed in 2, the name of the employee and the

00:08:26

value of his indicator, the table has become narrower

00:08:28

but not longer and visually it may

00:08:32

not be as visually beautiful as it was

00:08:33

before, but now from it we can

00:08:36

easily build a summary of the type we need.

00:08:39

Again, let’s use a filter

00:08:41

to get rid of the extra column and the one

00:08:44

which, if you remember, was the very right one

00:08:46

in our table, uncheck

00:08:49

ok and at this point the preparation of data for the

00:08:52

summary circumstances can be considered

00:08:53

complete, go to the main tab,

00:08:56

expand the drop-down list close

00:08:59

and load, select the second option

00:09:01

close and load into and

00:09:04

then the summary table on a new sheet like

00:09:08

the option of unloading our houses

00:09:11

in the panel of pivot tables that appears on the right,

00:09:14

which I hope you are familiar with the city;

00:09:17

we drop the attribute, that is, the

00:09:20

roach manager from the columns, into the row area, and the

00:09:22

value at their intersection, for greater

00:09:25

beauty, on the designer tab, you can

00:09:28

switch to the report layout, which is

00:09:30

called a tabular form by its appearance

00:09:33

closest to an ordinary classic

00:09:34

table, that's all, now you can

00:09:37

use the ability to filter columns available in pivot

00:09:39

tables with the

00:09:41

usual checkboxes opposite

00:09:43

names and items, filters by signature or

00:09:46

filters by value if necessary, and of

00:09:49

course, when the source data changes, we

00:09:51

will need to update our query and our

00:09:53

complex button refresh all on the

00:09:56

data tab or using the keyboard shortcut

00:09:59

control alt

00:10:01

f50 this is the end as another

00:10:04

way to solve our problem of filtering

00:10:06

horizontally let's use

00:10:09

macros all the previous methods as it is easy to

00:10:12

notice is

00:10:14

not really filtering at all we do not hide

00:10:16

the columns and in source list and we form a

00:10:19

new table with a given set of columns

00:10:22

from the original one, but if you need to

00:10:25

filter and hide columns in the

00:10:27

source data, then you need a fundamentally

00:10:29

different approach, namely a macro, as

00:10:32

before, we will also need a

00:10:34

horizontal row with the values

00:10:37

true and false to determine whether to include

00:10:40

or not include the corresponding column

00:10:42

in the filtering result, and everything is the same in the

00:10:44

first and last checks of this array, I

00:10:47

entered the value true because

00:10:49

we always want to show these two columns, but with the

00:10:52

rest, let's decide, we have already selected

00:10:54

columns in previous examples by the

00:10:56

total value and by the given list now

00:11:00

for variety, let's try to

00:11:01

select the columns according to a more tricky

00:11:03

criterion, for example, all managers

00:11:05

whose name begins with a letter and the asterisk in

00:11:08

this case is a mask, a

00:11:10

wildcard symbol that shows

00:11:13

Excel that where the asterisk is, we don’t care

00:11:15

what it will be, that is, first and

00:11:17

then it doesn’t matter what symbols to

00:11:20

check exactly the mine explosion for the fulfillment of

00:11:22

this criterion, we will write the following formula,

00:11:25

firstly, first we need to check

00:11:28

whether the yellow check from the criterion is empty at all,

00:11:30

because if it is empty, then we want to

00:11:33

display all columns; for this

00:11:35

we use the classic function if and

00:11:38

as a condition check to emptiness,

00:11:40

again using the standard rural

00:11:43

function, its empty argument will be our

00:11:46

yellow criterion check, and if there

00:11:49

is emptiness in it, we want to output the value

00:11:51

true in any case, otherwise, that is, if I

00:11:55

still set the criterion, we need to check the

00:11:57

name of the next manager from the header for

00:11:59

compliance to our condition,

00:12:02

unfortunately, the function if Microsoft Excel does

00:12:04

not know how to work with asterisks, but the

00:12:07

counting function can do this, if

00:12:09

which we are already familiar with as

00:12:11

the range being checked, here will be the name of

00:12:13

our minus r and 1 in this case, the

00:12:16

yellow cell is used as a criterion if

00:12:20

the condition is met, then we we get

00:12:21

one if it is not satisfied then the norik

00:12:24

so we add after the closing

00:12:26

bracket and equals and one to eventually

00:12:29

get a logical check and the value

00:12:32

true or false at the output I close

00:12:35

the bracket and of course we don’t forget about f4

00:12:38

our links in particular the link to the yellow

00:12:40

cell with criteria 4 we fix the

00:12:44

f4 keys and

00:12:46

you can press enter and copy

00:12:48

our formula to the right on all managers,

00:12:52

those of them who start with the letter

00:12:54

and as you can see have truth in our

00:12:58

range of criteria, then

00:13:01

visual basic macros come into play, we need to

00:13:04

write a macro that goes through all the

00:13:06

gray cells sd2 pao2 and if in the

00:13:10

next check the value is false, then it hides the

00:13:13

entire column, and for greater convenience,

00:13:15

let’s immediately make this

00:13:16

macro run automatically when

00:13:19

any changes occur in the yellow cell;

00:13:27

change the sheet to

00:13:30

do this, click on the sheet label with the

00:13:32

right mouse button and select the command

00:13:35

view code

00:13:36

in the editor window that opens visual

00:13:39

basic, but it’s better to copy and

00:13:41

paste the code of our macro, it can

00:13:44

be found at the link in the description under

00:13:46

this video, the logic here is very simple,

00:13:49

it’s a handler sheet change events

00:13:51

Roxy fix where the link to the changed

00:13:54

check will always be in the

00:13:56

target variable, first we need to

00:13:58

make sure that it is the yellow

00:14:01

cell a4 that has been changed, so we check the address of

00:14:04

our changed range from the

00:14:06

target variable and if it is a 4to, we start

00:14:09

searching through the cells in the range

00:14:12

d2o 2 is a gray table cell with

00:14:16

the words true and false, and if the next

00:14:18

checked check contains the value true,

00:14:20

that is, a corpse, then the entire column

00:14:22

corresponding to this cell uses the

00:14:25

interpol property, we do not hide,

00:14:27

so the heddon properties will be equal to falls,

00:14:30

otherwise hiding, that is,

00:14:32

we switch the hidan properties to true, that’s

00:14:34

actually all the logic, let’s

00:14:36

try how it works, let’s go back to

00:14:38

excel,

00:14:40

erase the old criterion and instead

00:14:42

enter, for example, this one, and

00:14:46

right there, as you can see, all the managers of whom

00:14:49

you have a syllable, everything is automatically

00:14:52

filtered for you and me if we erase the

00:14:55

criterion, then we see back a complete list of

00:14:58

all columns if we write an asterisk and

00:15:00

then a Russian letter, otherwise we will get everyone

00:15:04

whose exactly ends in well and so

00:15:06

on, I think you got the idea, as

00:15:08

usual, in the description under this video you

00:15:10

will find a file with an example from where you can

00:15:13

copy all the formulas and macros and

00:15:15

text descriptions of all methods thank you

00:15:18

for watching, take care of yourself and

00:15:20

come back here for new tricks

00:15:22

here when you know everything is simple

00:15:27

[music]

00:15:32

[music]

Description:

Три способа реализовать горизонтальную фильтрацию столбцов, которая изначально отсутствует в Microsoft Excel: с помощью функции ФИЛЬТР, запроса Power Query + сводная таблица и макросом на VBA. Скачать пример https://www.planetaexcel.ru/techniques/2/24245/ Мои книги https://www.planetaexcel.ru/books/ Мои онлайн-курсы https://www.planetaexcel.ru/learning/ Моя надстройка PLEX (+150 новых функций для вашего Excel) https://www.planetaexcel.ru/plex/ Наш канал в Telegram https://t.me/planetaexcel

Preparing download options

Popular

HD video

Only sound

All

* — If the video is playing in a new tab, go to it, then right-click on the video and select "Save video as..."

** — Link intended for online playback in specialized players

Questions about downloading video

How can I download "Горизонтальная фильтрация столбцов в Excel" video?

http://unidownloader.com/ website is the best way to download a video or a separate audio track if you want to do without installing programs and extensions.
The UDL Helper extension is a convenient button that is seamlessly integrated into YouTube, Instagram and OK.ru sites for fast content download.
UDL Client program (for Windows) is the most powerful solution that supports more than 900 websites, social networks and video hosting sites, as well as any video quality that is available in the source.
UDL Lite is a really convenient way to access a website from your mobile device. With its help, you can easily download videos directly to your smartphone.

Which format of "Горизонтальная фильтрация столбцов в Excel" video should I choose?

The best quality formats are FullHD (1080p), 2K (1440p), 4K (2160p) and 8K (4320p). The higher the resolution of your screen, the higher the video quality should be. However, there are other factors to consider: download speed, amount of free space, and device performance during playback.

Why does my computer freeze when loading a "Горизонтальная фильтрация столбцов в Excel" video?

The browser/computer should not freeze completely! If this happens, please report it with a link to the video. Sometimes videos cannot be downloaded directly in a suitable format, so we have added the ability to convert the file to the desired format. In some cases, this process may actively use computer resources.

How can I download "Горизонтальная фильтрация столбцов в Excel" video to my phone?

You can download a video to your smartphone using the website or the PWA application UDL Lite. It is also possible to send a download link via QR code using the UDL Helper extension.

How can I download an audio track (music) to MP3 "Горизонтальная фильтрация столбцов в Excel"?

The most convenient way is to use the UDL Client program, which supports converting video to MP3 format. In some cases, MP3 can also be downloaded through the UDL Helper extension.

How can I save a frame from a video "Горизонтальная фильтрация столбцов в Excel"?

This feature is available in the UDL Helper extension. Make sure that "Show the video snapshot button" is checked in the settings. A camera icon should appear in the lower right corner of the player to the left of the "Settings" icon. When you click on it, the current frame from the video will be saved to your computer in JPEG format.

What's the price of all this stuff?

It costs nothing. Our services are absolutely free for all users. There are no PRO subscriptions, no restrictions on the number or maximum length of downloaded videos.