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

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

[@UC0LMA1CQ6b3s6nrBlO4]

Я Ваш фанат! Дякую Вам за Вашу працю і хотілось частіши чути ваш голос та ваші уроки !

[@planetaexcel]

Спасибі! Буду намагатися радувати вас і далі! Бережіть себе.

[@user-rx1ml9lc9k]

И вновь открывшиеся горизонты! Спасибо, Николай 👍

[@gosha96]

Спасибо, Николай. Сколько всего уже применил по вашим урокам. И каждый раз новые являются актуальными. Ну и отдельное спасибо за Plex.

[@Albertus99]

Низкий поклон и большой респект за ваши труды в области эксел образования!

[@olgan.7857]

Волшебник 🪄!!!!!!! Спасибо Николай!!!

[@vladiumcvet1053]

Николай, Вы лучший! Спасибо Вам за Ваш труд!

[@supersrul]

Чудеса Николая!) спасибо из Израиля!

[@VLA85]

Громадное спасибо ❤️ всегда с нетерпением жду Ваши видео!

[@user-gr8eo6ww9n]

Как всегда СУПЕР ПОМОГАЛОЧКА! Спасибо огромное!👏👏👏

[@user-ep9cm8pc6o]

Вы правы "Когда знаешь - ВСЕ просто" 🙂 ..... Благодарю

[@3855298]

А когда знаешь, где посмотреть, то еще проще :)

[@user-ep9cm8pc6o]

@@3855298 если знаешь КАК делать, то и смотреть не нужно 🙂 ... просто берешь и делаешь 🙂

[@nikodimlad]

Отличный ролик, нужная в жизни функция. Спасибо большое, Николай

[@user-vy9ni4tv8m]

Спасибо за видео! Очень понравилась функция фильтр, и Спасибо, что выпустили бумажную версию про pq, давно ждала !!!

[@favoritechocolate3221]

Николай, спасибо Вам большое, очень помогают мне ваши видео и Ваш прекрасный мужской голос ❤

[@planetaexcel]

🙏😉

[@AndriyTkachenko]

Спасибо огромное.Ещё один очень полезный урок.

[@moiskon]

Уау ! Найду применение обязательно! Николай, РЕСПЕКТ!

[@Velant1]

Сказать, что это КРУТО, это ничего не сказать. Это КРУТИЩЕ!!!

[@lutfullaxolboev2179]

Отличный ролик. Спасибо, Николай.

[@user-pu9kr7ew3l]

Шикарный ролик. Спасибо.

[@Chelskiy]

Как всегда видео очень познавательное! Спасибо за ваши труды. Пользуюсь инструментами Plex и всем рекомендую!

[@yancher_man719]

Столько лет обучать людей как оптимально пользоваться экселем, моё почтение

[@yurd563]

Спасибо за видеоурок!

[@annashcherbachenko6182]

Спасибо, очень полезный урок.

[@theofil6391]

Спасибо! Очень полезные приемы!

[@vladimirmits6181]

Спасибо, как всегда - магия!

[@nadezdaharitonova3223]

Спасибо вам большое за ваш труд

[@user-jc6it7bs1m]

Как всегда гениально

[@SerhiiFandiei]

В примере с макросом надо добавить использование функции Aggregate для вывода итогов и будет вообще замечательно

[@user-11_01]

уоу, я бы не поверила в это:)👍👍👍👏👏👏

[@marat015]

Я с работы два часа назад пришёл. Хочу обратно на работу что бы попробовать реализовать это. Сегодня утром нужно было скрыть все столбцы с выручкой за каждый день месяца, а с количеством оставить)))) завтра попробую, спасибо)

[@planetaexcel]

😊 🙏

[@iafj11]

Пока не знаю, как этот урок мне может пригодится. Но буду знать, что это возможно. И буду знать где найти информацию. Спасибо.

[@Albertus99]

Просто тупо надо накапливать знания а где и как их применять мозг потом сам решит )))

[@ozz5939]

Николай, спасибо за очередной отличный материал! Привет из Киева

[@rellanely5493]

вот этого как раз и не хватало мне

[@umikomidaradzuki7985]

Спасибо за видео. Вариант с макросом особенно понравился. Но самый простой способ просто скопировать и вставить транспонировав.

[@jollyjuln1054]

Благодарю!

[@user-py6mr6iz3g]

Вам спасибо!

[@alav7590]

Браво!

[@max4445]

Спасибо!

[@Midavok]

Круто!

[@kuatle]

Можно еще так: -создаем формулой список заголовков столбцов на отдельном листе -на этом листе создаем колонку с чекбоксами, по значению в которой будет определяться скрывать или показывать столбцы в основной таблице. - далее макросом производим скрытие или отображение нужных столбцов.

[@anton_a388]

Спасибо

[@sergeyshykhovtsev2927]

Спасибо!! Полезно. Но в ролике 4 способа, ещебы временные метки к ним было бы супер для последующего поиска.

[@kakhramonabidjanov4613]

Ты крут!

[@idndex]

Это не просто, но интересно)

[@Ulia_Ya]

Какой же Вы умный!

[@planetaexcel]

🙄

[@msi1180]

Первый:) Шикарно!

[@Rio_Rioz]

В копилку знаний по эксель!

[@user-om5lo3bc2d]

Да, сколько смотрю Ваши уроки, могу сказать, что Вы очень крутой 😎 жалко что мало видео по power pivot, мне интересно просто может ли power pivot объединять 2 таблицы в одну по более одного ключа, просто понять целесообразность отличия от power query. Ну или видео с олап кубами)

[@planetaexcel]

Точно не может - только одна активная связь может быть между двумя таблицами в Power Pivot. Поэтому иногда проще в Power Query по множеству столбцов объединять, да.

[@user-om5lo3bc2d]

@@planetaexcel спасибо большое!

[@user-pr3sq2wj6y]

Николай, спасибо большое, очень помогли ваши разъяснения по фильтру с помощью макроса. А можно ли сделать двух и более фильтрацию, что бы из первично отфильтрованных данных можно было дальше их фильтровать? Моя попытка ввести допстроку "истина/ложь" и доп.код не дали фильтр из фильтра, они работают сами по себе. Возможно нужна связка в коде какой-то командой.... Подскажите, пожалуйста, решение. Спасибо.

[@user-xj6qy7vq5h]

Хотел бы добавить еще одну вишенку -формула ТРАНСП - простейшее решение.

[@planetaexcel]

Не совсем - во всех версиях Excel за исключением последней, её надо было вводить как формулу массива :)

[@mazyankaful]

Бывают случаи, когда данные нельзя транспонировать. Поэтому решения просто волшебные. Век живи , век учись.

[@evgeniylfc]

Николай, подскажите, пожалуйста, где и как можно прочитать про все такие нюансы, как «функция ЕСЛИ не умеет читать критерий А*, а функция СЧЁТЕСЛИ может»?

[@planetaexcel]

Это уже собственный опыт и метод тыка, обычно :)

[@user-br2rk6qd8u]

В справке по табличным функциям, которая до офиса 2010 была автономная, а теперь в интернете, как и help по visual basic, и конечно же, в большинстве компаний корпоративный IT доступ туда заблокировал 🤬

[@LK_M-qy1pi]

Спасибо, Николай Павлов за уроки. Я записал макрос где с первого листа копирует выделенные ячейки и вставляет на второй лист, как сделать что бы на втором листе активная ячейка переходила на следующую строку не подскажите пжл...

[@user-br2rk6qd8u]

ActiveCell.Offset(1,0).Select Только сначала выбрать этот лист, т.к. двигать курсор можно только на активном листе: Sheets("имя_листа2").Select А потом не забыть вернуться. И чтобы в глазах не рябило, нужно в начале поставить Application.ScreenUpdating=False

[@VaredZimu]

Интересно, можно ли как то сделать фильтр с выпадающим списком с возможностью выбора нескольких пунктов чекбоксом. Просто выпадающий список в ячейке А2 я сделал, это интересно, но было бы здорово выбрать несколько столбцов чтобы сравнить данные например.

[@Dezhma]

Очень хорошее видео. А можно ли сохранить условное форматирование в виде какого ни будь макроса, что бы оно было доступно для нескольких подобных файлов

[@user-br2rk6qd8u]

Можно включить запись макроса при создании или применении форматирования, потом подправить в коде ссылки и экспортировать макрос в файл. Но у этого способа есть минусы, и много. Мне кажется, для сохранения пользовательских форматов проще создать шаблон.

[@greshniktarantino7281]

Коль, а может ли гугл эксель автоматически добавлять какое-то число в ячейку по таймингу и суммировать его? К примеру - надо, что б в ячейке А1 через каждый час прибавлялось допустим число 10 и суммировалось? ну и так до бесконечности, пока не остановлю

[@sergey_evseev]

Здравствуйте, Николай. У меня вопрос: Есть ли такая формула, где ФИО прописана CAPS'ом и чтоб на другом ячейке (или листе) будет прописана прописью полностью без CAPS'а?

[@user-ge2jj8hv8r]

интересно, а можно простыми формулами брать данные из мадели данных? не зогружая их на лист

[@bobcat1097]

добрый день. Как в экселе посчитать максимальное среднее значение стоимости (не от всего документа, а при включенном фильтре)? У меня считает только всего документа вне зависимости от фильтра. Ну или как например сделать зависимость, чтобы я мог выбрать максимальный средний РОИ и таблица отфильтровала нужное доходы, расходы и другие параметры, чтобы его получить? Или высчитать при каких параметрах я получу максимальный средний РОИ?

[@isaevaamina7594]

👏👏👏👏🔥🔥🔥

[@user-fs5zi4fp8e]

Здравствуйте Николай! А в Exel можно чертежи рисовать? Есть ли такая возможность?

[@1iuh]

Николай, какой из форматов Excel будет при сохранении занимать меньший объём файла при той же функциональности? Столкнулся с тем, что xlsx (Книга Excel) почти в 2 раза больше, чем xlsb (Двоичная книга Excel), при этом так же работает Сводная таблица, Условное форматирование, Срезы. Что же это за "фокус-покус" с увеличением объёма xlsx?

[@1iuh]

Файл xlsb - 39 Мб; тот же файл xlsx - 66 Мб.

[@user-br2rk6qd8u]

В теории, формат xlsb может быть как больше, так и меньше по объёму. На больших файлах Excel при работе с xlsb вроде как быстрее должен быть, при одинаковой функциональности.

[@1iuh]

Николай, для сохранения xlsx как xlsb - надо открыть файл xlsx и заново сохранить как xlsb - выбрать Тип файла - Двоичная книга Excel. Простое изменение расширения xlsx на xlsb не работает - будет ошибка при открытии файла. Можно ли не открывая файла менять тип файла - xlsx на xlsb и наоборот? Хотелось бы групповое сохранение файлов xlsx как xlsb и наоборот - не открывая файлов. Есть ли способы это сделать?

[@nabum]

Николай, порекомендуете пжл курс по vba

[@VITRWind]

А как сделать, чтобы по нажатию кнопки/установке флажка/переключателя скрывались или отображались определенные столбцы?

[@Prozeher]

Музычка бодрая)

[@1iuh]

Николай, Сводная таблица требуется не "в табличной форме", а "в сжатой форме" - в поле строк несколько позиций. Сводная делает "сортировку от А до Я" того, что в верхней строки в поле строк. Как сделать порядок строк как в источнике данных - верхняя строка в поле строк? где для уровней идентификаторы типа "1.1", под которыми много нижних уровней. Сделал слева сводной доп. столбец - мгновенное заполнение - с цифрами 1,1; 1,2 вместо 1.1.; 1.2. и т.д. - сортировка по нему, но формула ИНДЕКС(; ПОИСКПОЗ ()) находит только первое совпадение, а наименования того, что в верхней строке в поле строк - повторяются в Источнике данных.

[@Artem_Klopov]

Подскажите, пожалуйста, есть 2 книги с данными. Задача сделать так, чтобы данные из одной книги подтягивались автоматически в другую. Таблицы не сводные. ВПР тут поможет?

[@MsPatriot1970]

=Индекс , поискпоз попробуйте

[@Artem_Klopov]

@@MsPatriot1970 И про них я знаю. Вопрос в автоматизации...

[@user-br2rk6qd8u]

@@Artem_Klopov если с Excel у вас всё всерьез и надолго, нужно переходить от табличных формул на VBA. Тогда на ЛЮБОЙ вопрос "а можно ли в Excel..." у вас всегда будет ответ "Да" 😎

[@user-nb5gj1er4s]

я бы последний макрос написал так: Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range If Target.Address = "$A$4" Then For Each cell In Range("D2:O2") cell.EntireColumn = Not cell Next End If End Sub

[@alienworld7159]

У меня вот проблема, уже два дня не могу решить. Например, есть ячейка A10 В ячейку А10 вписать 5 И есть ячейка B1. Например в B1 вписать 10 И есть еще 3-я ячейка С1. В С1 вписать =A(B1) Я хотел что бы тут: =A(B1), и B1 превратилась в число 10, и стало A10, и ячейка С1 вывела 5. Возможно такое?

[@user-hy1tt8vh4d]

Добрый день! У меня нет такой функции ФИЛЬТР, если ее набирать, говорит, что функция неверная. но зато есть функция ФИЛЬТР.xml ее действие другое. Как быть? (ексель 2019)

[@antonkharitonov9007]

Вопрос не в тему немного - можно ли нажатием клавиш протянуть формулу вниз до конца диапазона? Как альтернатива двойному клику по черному уголку ячейки

[@planetaexcel]

Точно нет. Я для такого в свое время писал макрос (он есть в PLEX, кстати).

[@viktorasgolubevas2386]

Находясь в ячейке с формулой Shift ↓ помечаем диапазон вниз и Ctrl D, т.е. “Fill DOWN from cell above” Чтобы пометить до конца диапазона вниз подходит Ctrl Shift End если это, например, новый правый столбец с формулой в первой строке.

[@user-br2rk6qd8u]

А Ctrl+C / Ctrl+V не устраивает?

[@viktorasgolubevas2386]

@@user-br2rk6qd8u с Ctrl+D меньше на одну комбинацию нажатий :) Кстати, в быстром наборе и протягивании формул я долго себя приучал к Ctrl+Enter чтобы оставаться в "несущей" ячейке

[@user-ee6xe6ok6e]

0:08 Эксэл 😁

[@ilshatbikmeyev814]

подскажите пожалуйста, как при распечатке проставлять нумерацию страниц?

[@1iuh]

Меню Файл - Печать - Параметры страницы - Колонтитулы - Верхний (или нижний) колонтитулы - в строке после "А" - выбрать значок "Вставить номер страницы".

[@ilshatbikmeyev814]

спасибо

[@semix74]

А вот как сделать фильтрацию по стоке, скажем Москва, и нужно отфильтровать данные в диапазоне от 30 до 80 и если столбцов будет 150?

[@user-br2rk6qd8u]

В макросе дописать 1 строчку. Вообще, если пользоваться VBA, то нерешаемые проблемы и ограничения просто не существуют.

[@lifelong_student]

а там не опечатка закралась в текст макроса: Entire - Entre ?

[@josephstalin1922]

А почему человек Анна не имела данных в сводной таблице при выгрузке из Power query?

[@planetaexcel]

В смысле? А как же столбец В на моменте 15:49?

[@josephstalin1922]

@@planetaexcel так это момент окончания видео... Или я не так понял?

[@smetnoedelo]

Обновлять запрос-таблицу удобнее: ПКМ - Обновить

[@ievleva81]

Получается Expected End Sub в Excel 2010

[@vitmirof5850]

Здравствуйте Николай! Вы большой специалист по программе EXCEL. У меня есть проблема по установки даты без времени. В ячейке дата (не сегодняшняя) и время. Как мне удалить время но оставить дату. Пробовал нажимать на кнопку ДАТА во ВСТАВКЕ. Устанавливается сегодняшняя дата. А мне надо оставить старую дату. Никак не найду как это сделать. Помогите пожалуйста...

[@planetaexcel]

Если дата с временем в А1, то можно в соседней ячейке ввести формулу =ОТБР(A1) и поставить для неё формат даты. А в общем и целом очень советую глянуть www.planetaexcel.ru/techniques/6/88/ чтобы понимать как Excel внутри работает с датами и временем.

[@vitmirof5850]

@@planetaexcel Большое спасибо!!!

[@1iuh]

Долой костыли! Всем учиться работать в Excel по-человечески - с узкой таблицей.

[@1iuh]

13:45 должно быть Else cell.EntireColumn.Hidden в обеих строках? а то "entre" - не по-английски написано, а как-бы по-французски.

[@user-br2rk6qd8u]

Всё правильно написано. Там свойству hidden задается значение True или False.

[@valera_bahai]

Беру на вооружение фразу "не забываем проэфчетырить"

[@planetaexcel]

😊👌

[@DArkadiy]

В последнем способе итого последнего столбца получается не соответствует отображаемому, а так огонь

[@planetaexcel]

Можно использовать функцию АГРЕГАТ для этого - она умеет суммировать только видимые (нескрытые) ячейки.

[@bahodurtojiddinov4138]

Кто нибудь знает альтернативу данной формулы: =если(и(B2>=сегодня();B2=сегодня();C2=сегодня();D2

[@1iuh]

Ошибка всех начинающих в том, что они сразу строят подобие Сводной таблицы, а это надо предоставить программе Excel, а не мучить себя самих, и других своими "таблицами".

[@MrSvpatrik]

Не получается - "команда не может быть выполнена над указанным диапазоном . Выберите одну ячейку из диапазона и повторите попытку . " как же заманал этот говноексель , с гугл таблицами куда проще !

[@user-ve2uo7wk6u]

Разве эксель не объявил санкции? Или вы пираткой пользуетесь? Вообще, рекламировать вражеский софт, это непатриотично.

[@planetaexcel]

Предложите аналогичный по возможностям отечественный - с удовольствием "прорекламирую".

[@user-ck2ly5er1o]

Помогите с данным кодом, нужна помощь. Сделать для множества фигур Private Sub Worksheet_Change(ByVal Target As Range) 'Updateby Extendoffice 20160704 If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub If IsNumeric(Target.Value) Then If Target.Value < 100 Then ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbRed ElseIf Target.Value >= 100 And Target.Value < 200 Then ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbYellow Else ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbGreen End If End If End Sub

[@dilijator]

Спасибо!