Скачать "Горизонтальная фильтрация столбцов в Excel"
%3Aformat(webp)%2Fi.ytimg.com%252Fvi_webp%252FefK_jeE8vkQ%252Fmaxresdefault.webp&w=1139&q=75)
Похожие ролики из нашего каталога
![(Partie 4) Application simplifiée de gestion de stock : Création de A à Z [Excel et VBA]](/_next/image?url=https%3A%2F%2Funidownloader.com%2FcontentImages%2FrfHSVzv3O4I1ppjLtkvcDoTi7JM%3D%2Ffilters%3Aquality(80)%3Aformat(webp)%2Fi.ytimg.com%252Fvi_webp%252FEthK25hAWtQ%252Fmaxresdefault.webp&w=1139&q=75)
%3Aformat(webp)%2Fi.ytimg.com%252Fvi_webp%252FGt340w9t-20%252Fmaxresdefault.webp&w=1139&q=75)
Теги видео
Субтитры
Английский
00:00:02
и с вами снова я николай павлов тренер и00:00:04
эксперт по продуктам microsoft office00:00:06
автор проекта планета excel точка ру на00:00:09
2 теплекс кучей полезных макросов и00:00:11
нескольких книг и онлайн курсов по этой00:00:14
теме если вы не совсем уж начинающий00:00:17
пользователь то должно быть уже заметили00:00:19
что на 99 процентов все в экселе00:00:22
устроено для работы именно с00:00:23
вертикальными таблицами где по строчкам00:00:26
располагается информация об объектах или00:00:29
событиях так называемой записи апостол00:00:31
цам идут параметры или атрибуты этих00:00:34
объектов как их ещё называют поля00:00:36
сводные таблицы промежуточные итоги даже00:00:39
копирование формул двойным щелчком вниз00:00:41
все это заточены именно под такой формат00:00:44
данных однако же как и почти всегда нет00:00:47
правил без исключений из достаточно00:00:49
регулярной периодичностью меня вот00:00:51
спрашивают что делать если в работе00:00:54
попалась таблица с горизонтальной00:00:56
смысловой ориентацией либо же таблица00:00:58
где строки и столбцы имеют одинаковые по00:01:01
смыслу вес ну вот например такая и если00:01:04
сортировать по горизонтали excel еще00:01:06
как-то худо-бедно научился на вкладке00:01:09
данные нужно пойти в сортировку а дальше00:01:12
в открывшемся окне параметры и выбрать00:01:14
сортировку не строк и столбцов00:01:16
то фильтрации например все обстоит00:01:20
гораздо хуже встроенных инструментов для00:01:22
фильтрации столбцов они строк в excel00:01:24
просто нет так что если перед вами00:01:26
встала такая задача то придется00:01:28
придумывать обходные пути и костыли00:01:31
разной степени вы отчетности давайте мы00:01:34
по ним и пробежимся00:01:36
если вы работаете на новой версии excel00:01:39
2021 или в подписки office 365 то можно00:01:43
воспользоваться недавно появившиеся00:01:45
функции фильтр умеющий фильтровать00:01:48
исходные данные не только по строчкам но00:01:51
и по столбцам для работой эта функция00:01:53
требует вспомогательный горизонтальный00:01:56
одномерный массив строку где каждым00:01:59
элементом будет логическое значение00:02:01
истина или ложь который определяет00:02:04
показываемый или наоборот скрываем00:02:06
очередной столбец допустим мы с вами00:02:09
всегда хотим отображать первый и00:02:11
последний столбцы00:02:13
город и итогов нашей таблице поэтому для00:02:15
них соответствующих ячейках нашего00:02:17
массива введем значение равно истина00:02:23
для остальных же столбцов содержимое со00:02:25
следующих ячеек будет формулы которая00:02:28
проверяет нужные нам условий предположим00:02:31
мы хотим отфильтровать столбцы показать00:02:33
только те из них где значение итога для00:02:36
соответствующего менеджера лежит в00:02:38
диапазоне от 500 до 700 соответственно00:02:41
формула который проверяет это условие00:02:43
будет выглядеть следующим образом00:02:45
используем логическую функцию и так как00:02:49
данном случае нам надо чтобы оба условия00:02:50
обязательно выполнялись и дальше через00:02:53
точку запятой зададим эти критерии00:02:55
итоговая сумма по 1 менеджеру у нас00:02:58
должна быть больше либо равна первому00:03:02
значению от то есть 500 и через точку ,00:03:05
и это же значение то есть ячейка е14 у00:03:08
нас должно быть меньше либа равна00:03:11
верхнему пределу то есть 700 чтобы00:03:14
впоследствии эту формулу можно было00:03:16
протянуть вправо черным крестиком на00:03:17
всех остальных менеджеров естествен00:03:20
нужно закрепить ссылки на желтые ячейки00:03:22
используем для этого клавишу f4 на00:03:25
клавиатуре00:03:26
все жмем enter тянем формула вправо и00:03:30
получаем логические значения ложь и00:03:33
истина который впоследствии для функций00:03:35
фильтр будут индикатором того надо или00:03:38
не надо отображать соответствующий00:03:39
столбец ну и осталось собственно эту00:03:41
функцию ввести выделим пустую ячейку00:03:44
снизу под таблицей например и введём00:03:47
фильтр первый аргумент здесь наша00:03:50
фильтруем аят таблица второй аргумент00:03:53
через точку запятой подготовленный00:03:55
горизонтальный массив критериев ну и00:03:58
третий необязательный аргумент значение00:04:00
которое нужно вывести если ничего при00:04:02
фильтрации в результаты не попала но у00:04:05
нас такого очевидно быть не может00:04:07
поэтому скобку закрываем и жмем на антон00:04:10
теперь если менять критерии отбора ну00:04:12
например вместо 500 написать 300 наш00:04:16
функция будет отфильтровывать только те00:04:18
столбцы которые под эти критерии00:04:20
попадают развивая эту же идею можно00:04:23
легко реализовать совершенно аналогичным00:04:25
образом00:04:26
и фильтрацию столбцов по их именам то00:04:28
есть по заданному списку предположим у00:04:31
нас с вами есть отдельная умная таблица00:04:32
который называется мои столбцы где00:04:35
перечислены имена столбцов которые нужно00:04:37
отобразить в этом случае для заполнения00:04:40
горизонтального массива индикаторов00:04:42
поможет стандартных сельская функция00:04:44
счет если проверяющие вхождение00:04:46
очередного название столбца из шапки в00:04:49
наш разрешенный список формула будет00:04:51
выглядеть как счет если и в качестве00:04:55
первого аргумента мы указываем диапазон00:04:57
с которым ищем он у нас назывался мои00:04:59
столбцы и а в качестве второго что ищем00:05:02
заголовок очередного столбца из шапки и00:05:06
затем нам надо проверить нашлось ли00:05:08
что-то в результатах то есть найденное00:05:10
количество совпадений больше нуля или00:05:12
нет все жмем на enter копируем нашу00:05:15
формулу в право на весь горизонтальный00:05:18
массив получаем истину напротив текста00:05:21
псов которые были в списке и повторно00:05:24
используем уже знакомую функцию filter00:05:27
первый аргумент если помните наша00:05:29
таблица фильтруем она 2 горизонтальный00:05:33
массив индикаторов истинна и лож для00:05:35
каждого столбца все жмем на enter и00:05:38
получаем в результатах только те столбцы00:05:40
которые были в разрешенном списке00:05:42
сотрудников при добавлении нового00:05:43
человека00:05:46
он естественно тут же появляется как00:05:48
столбец в результатах нашей фильтрации00:05:51
на самом деле когда в начале этого00:05:53
ролика я говорил что excel не имеет00:05:55
встроенных инструментов для00:05:57
горизонтальной фильтрации столбцов я00:05:59
немножко слушаю потому что на самом деле00:06:02
горизонтальной фильтрация в excel и есть00:06:04
очень давно только не в обычных а в00:06:06
сводных таблицах поэтому если нам00:06:08
удастся преобразовать нашу исходную00:06:10
таблицу в сводную аналогичного виды то00:06:13
можно будет воспользоваться всем этим00:06:15
встроенным функционал для такого00:06:17
преобразования наша исходная таблица00:06:19
должна удовлетворять однако нескольким00:06:21
условий а именно иметь правильную 100:06:24
строчную строку заголовка без пустых и00:06:27
объединенных ячеек иначе просто не00:06:29
получится построить сводную таблицу не00:06:32
содержать дубликатов в подписях строк и00:06:34
столбцов то есть повторов среди городов00:06:37
и менеджеров сводные таблицы так повторы00:06:39
схлопнуться в список только уникальных00:06:42
значений и третье условие составить том00:06:44
что наши данные должны содержать только00:06:46
числа в области значений то есть на00:06:49
пересечении строк и столбцов так как00:06:51
сводная таблица обязательно применить к00:06:53
ним какую-то агрегируются функцию там00:06:55
сумму среднее минимум максимум и с00:06:57
текстом как вы понимаете эту функцию00:06:59
конечно не сработает если все эти00:07:01
условия выполняются то для построения00:07:04
сводный внешне максимально похожей на00:07:06
нашу исходную таблицу нужно будет эту00:07:09
исходную таблицу развернуть из кросс00:07:12
таблицы в плоскую или как еще говорят00:07:14
нормализовать в английской версии от00:07:16
называется он пилот а это проще всего00:07:18
сделать при помощи надстройки парк вере00:07:20
мощного инструмента преобразования любых00:07:23
данных встроенного в excel начиная с00:07:25
2016 версий выделим любую ячейку внутри00:07:28
наших данных на жмем сочитание клавиш00:07:31
kontrol т и на enter для того чтоб00:07:34
превратить ее в умную таблицу и загрузим00:07:37
в парк вере на вкладке данные при помощи00:07:39
кнопки из таблицы диапазона00:07:43
поверх окна microsoft excel откроется00:07:45
окошко редактора запросов по work very в00:07:48
котором можно сразу во первых избавиться00:07:50
от строки итогов она нам не нужна так00:07:52
как в сводные таблицы все равно будет00:07:54
своя строка с итогами для этого давайте00:07:56
выполним фильтрацию по столбцу город и00:07:59
есть у меня галочку и того ну а00:08:02
во-вторых и от самое главное давайте00:08:04
выполним отмену свертывания то есть00:08:06
нормализацию или разворачивание этой00:08:08
двумерной таблицы в полоску для этого в00:08:10
парк вере достаточно щелкнуть правой00:08:12
кнопкой мышки по шапке первого столбца00:08:15
город и выбрать команду отменить00:08:18
свертывание других столбцов все не00:08:22
выделены и столбцы как видите00:08:23
преобразуются в 2 имя сотрудника и00:08:26
значение его показатель таблица стала00:08:28
уже но длине не и визуально может быть00:08:32
не такой наглядный красивый как было до00:08:33
этого но зато теперь по ней мы можем00:08:36
легко построить сводную нужного нам вида00:08:39
опять же давайте при помощи фильтр00:08:41
избавимся от лишнего столбца и того00:08:44
который если помните был самым правом у00:08:46
нас в таблице снимаем галочку00:08:49
окей и на этом подготовку данных для00:08:52
обстояний сводный можно считать00:08:53
законченной идем на вкладку главное00:08:56
разворачиваем выпадающий список закрыть00:08:59
и загрузите выбираем вторую опцию00:09:01
закрыть и загрузить в а00:09:04
затем сводную таблицу на новом листе как00:09:08
вариант выгрузки наших домах00:09:11
в появившейся справа панели сводных00:09:14
таблиц который надеюсь вам знакомо город00:09:17
закидываем в область строк атрибут то00:09:20
бишь менеджера вобла из столбцов ну а00:09:22
значение на их пересечении для пущей00:09:25
красоты на вкладке конструктор можно00:09:28
переключиться в макет отчета который00:09:30
называется табличная форма по виду он00:09:33
ближе всего к обычной классической00:09:34
таблицы вот и все теперь можно00:09:37
пользоваться имеющейся в сводных00:09:39
таблицах возможностью фильтрации00:09:41
столбцов привычными галочками напротив00:09:43
имен и пунктами фильтры по подписи или00:09:46
фильтры по значению если нужно ну и само00:09:49
собой при изменении исходных данных00:09:51
нужно будет обновить наш запрос и нашу00:09:53
сложную кнопкой обновить все на вкладке00:09:56
данные или при помощи сочетание клавиш00:09:59
control alt00:10:01
f50 он конец в качестве еще одного00:10:04
способа решения нашей задачи фильтрации00:10:06
по горизонтали давайте используем00:10:09
макросы все предыдущие способы как легко00:10:12
заметить представляет собой на самом00:10:14
деле не совсем фильтрацию мы не скрываем00:10:16
столбцы и в исходном списке а формируем00:10:19
новую таблицу заданным набором столбцов00:10:22
из исходной если же требуется именно00:10:25
фильтровать скрывать столбцы и в00:10:27
исходных данных то нужен принципиально00:10:29
другой подход а именно макрос как и00:10:32
ранее нам все также потребуется00:10:34
горизонтальная строка со значениями00:10:37
истинна и лож для определения включать00:10:40
или не включать соответствующий столбец00:10:42
в результат фильтрации ну и все также в00:10:44
первой и последней чеки этого массива я00:10:47
ввел значение истина потому что эти два00:10:49
столбца мы хотим показывать всегда но с00:10:52
остальными давайте решим мы уже отбирали00:10:54
в прошлых примерах столбцы по значению00:10:56
итога и по заданному списку сейчас же00:11:00
для разнообразия давайте попробуем00:11:01
отобрать столбцы по более хитрому00:11:03
критерию например всех менеджеров имя00:11:05
которых начинается с буквы а звёздочка в00:11:08
данном случае представляет собой маску00:11:10
символ подстановки который показывает00:11:13
excel что там где стоит звездочка нам00:11:15
все равно что будет то есть сначала а а00:11:17
дальше все равно какие символы чтобы00:11:20
проверить именно мин взрыв на выполнение00:11:22
этого критерия напишем следующую формулу00:11:25
во-первых сначала нам надо проверить не00:11:28
пусто ли вообще желтая чек из критерия00:11:30
потому что если она пустая то мы хотим00:11:33
отображать все столбцы для этого00:11:35
используем классическую функцию если а в00:11:38
качестве условия проверку на пустоту при00:11:40
помощи опять же стандартной сельской00:11:43
функции ей пусто аргументом будет наша00:11:46
желтой чека критерия и вот если в ней00:11:49
пустота мы хотим выводить значение00:11:51
истина в любом случае иначе то есть если00:11:55
критерий все-таки задам надо проверить00:11:57
имя очередного менеджера из шапки на00:11:59
предмет соответствия нашему условию к00:12:02
сожалению функция если у microsoft excel00:12:04
не умеет работать со звездочками зато00:12:07
это умеет делать функция счет если00:12:09
которая нам уже знакомо в качестве00:12:11
проверяемого диапазону здесь будет имя00:12:13
нашего минус р а 1 в данном случая в00:12:16
качестве критерия желтая ячейка если00:12:20
условие выполняется то мы получим00:12:21
единичку если не выполняется то норик00:12:24
так что добавляем после закрывающей00:12:26
скобки и равно и единичку чтобы в итоге00:12:29
получить логическую проверку и значение00:12:32
истина или ложь на выходе закрываю00:12:35
скобку ну и конечно не забываем про f400:12:38
наши ссылки в частности ссылку на желтую00:12:40
ячейку с критериям 4 закрепляем клавиши00:12:44
f4 и00:12:46
можно нажимать на enter и копировать00:12:48
нашу формулу вправо на всех менеджеров00:12:52
те из них у кого ими начинается с буквы00:12:54
а как видите имеют истину в нашем00:12:58
диапазоне критериев дальше в дело00:13:01
вступает макросы visual basic нам нужно00:13:04
написать макрос который проходит по всем00:13:06
сером ячейкам sd2 pao2 и если в00:13:10
очередной чеки значения ложь то скрывает00:13:13
весь столбец причем для пущего удобства00:13:15
давайте сразу сделаем так чтобы этот00:13:16
макрос автоматически запускался при00:13:19
любых изменениях желтой ячейки надо было00:13:22
запускать его каждая с руками для этого00:13:24
код нашу макроса должен быть помещен в00:13:27
обработчик события изменения листа чтобы00:13:30
это сделать щёлкнем по ярлычку листа00:13:32
правой кнопкой мышки и выберем команду00:13:35
просмотреть код00:13:36
в открывшемся окне редактора visual00:13:39
basic набираем но лучше уж копируем и00:13:41
вставляем код нашего макроса его можно00:13:44
будет найти по ссылке в описании под00:13:46
этим видео логика здесь очень простая00:13:49
это обработчик события изменения листа00:13:51
рокси чинить где ссылка на измененную00:13:54
чеку всегда будет лежать в переменной00:13:56
target для начала нам с вами нужно00:13:58
убедиться что изменили именно желтую00:14:01
ячейку а4 поэтому мы проверяем адрес00:14:04
нашу измененного диапазону из переменной00:14:06
target и если это a 4to запускаем00:14:09
перебор ячеек в диапазоне00:14:12
d2o 2 это серый ячейки на таблицы со00:14:16
словами истина и ложь и если в очередной00:14:18
проверяемые чеки лежит значение истина00:14:20
то бишь труп то весь столбец00:14:22
соответствующий этой ячейки используем00:14:25
свойство интерполом мы не скрываем00:14:27
поэтому свойства heddon будет равно фолз00:14:30
противном случае скрывая то есть00:14:32
свойства хидан переключаем в true вот00:14:34
собственно и вся логика давайте00:14:36
попробуем как это работает у вернемся в00:14:38
excel00:14:40
стираем старый критерий а вместо него00:14:42
вводим ну например вот такой и00:14:46
тут же как видите все менеджеру которых00:14:49
вы меня есть слог все автоматически00:14:52
фильтруется у нас с вами если мы стираем00:14:55
критерий то видим обратно полный список00:14:58
всех столбцов если напишем звездочка а00:15:00
дальше русскую букву а то получим всех00:15:04
чьи именно заканчиваются на ну и так00:15:06
далее я думаю вы ухватили идеи как00:15:08
обычно в описании под этим видео вы00:15:10
найдёте файл с примером откуда можно00:15:13
скопировать все формулы и макросы и00:15:15
текстовые описания всех способов спасибо00:15:18
что смотрели берегите себя и00:15:20
возвращайтесь сюда за новыми фишками00:15:22
здесь когда знаешь все просто00:15:27
[музыка]00:15:32
[музыка]Описание:
Три способа реализовать горизонтальную фильтрацию столбцов, которая изначально отсутствует в 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
Готовим варианты загрузки
* — Если видео проигрывается в новой вкладке, перейдите в неё, а затем кликните по видео правой кнопкой мыши и выберите пункт "Сохранить видео как..."
** — Ссылка предназначенная для онлайн воспроизведения в специализированных плеерах
Вопросы о скачивании видео
Как можно скачать видео "Горизонтальная фильтрация столбцов в Excel"?
Сайт http://unidownloader.com/ — лучший способ скачать видео или отдельно аудиодорожку, если хочется обойтись без установки программ и расширений. Расширение UDL Helper — удобная кнопка, которая органично встраивается на сайты YouTube, Instagram и OK.ru для быстрого скачивания контента.
Программа UDL Client (для Windows) — самое мощное решение, поддерживающее более 900 сайтов, социальных сетей и видеохостингов, а также любое качество видео, которое доступно в источнике.
UDL Lite — представляет собой удобный доступ к сайту с мобильного устройства. С его помощью вы можете легко скачивать видео прямо на смартфон.
Какой формат видео "Горизонтальная фильтрация столбцов в Excel" выбрать?
Наилучшее качество имеют форматы FullHD (1080p), 2K (1440p), 4K (2160p) и 8K (4320p). Чем больше разрешение вашего экрана, тем выше должно быть качество видео. Однако следует учесть и другие факторы: скорость скачивания, количество свободного места, а также производительность устройства при воспроизведении.
Почему компьютер зависает при загрузке видео "Горизонтальная фильтрация столбцов в Excel"?
Полностью зависать браузер/компьютер не должен! Если это произошло, просьба сообщить об этом, указав ссылку на видео. Иногда видео нельзя скачать напрямую в подходящем формате, поэтому мы добавили возможность конвертации файла в нужный формат. В отдельных случаях этот процесс может активно использовать ресурсы компьютера.
Как скачать видео "Горизонтальная фильтрация столбцов в Excel" на телефон?
Вы можете скачать видео на свой смартфон с помощью сайта или pwa-приложения UDL Lite. Также есть возможность отправить ссылку на скачивание через QR-код с помощью расширения UDL Helper.
Как скачать аудиодорожку (музыку) в MP3 "Горизонтальная фильтрация столбцов в Excel"?
Самый удобный способ — воспользоваться программой UDL Client, которая поддерживает конвертацию видео в формат MP3. В некоторых случаях MP3 можно скачать и через расширение UDL Helper.
Как сохранить кадр из видео "Горизонтальная фильтрация столбцов в Excel"?
Эта функция доступна в расширении UDL Helper. Убедитесь, что в настройках отмечен пункт «Отображать кнопку сохранения скриншота из видео». В правом нижнем углу плеера левее иконки «Настройки» должна появиться иконка камеры, по нажатию на которую текущий кадр из видео будет сохранён на ваш компьютер в формате JPEG.
Сколько это всё стоит?
Нисколько. Наши сервисы абсолютно бесплатны для всех пользователей. Здесь нет PRO подписок, нет ограничений на количество или максимальную длину скачиваемого видео.