Горизонтальная фильтрация столбцов в Excel
Вставка
- Опубліковано 18 тра 2022
- Три способа реализовать горизонтальную фильтрацию столбцов, которая изначально отсутствует в 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
Я Ваш фанат! Дякую Вам за Вашу працю і хотілось частіши чути ваш голос та ваші уроки !
Спасибі! Буду намагатися радувати вас і далі! Бережіть себе.
И вновь открывшиеся горизонты!
Спасибо, Николай 👍
Вы правы "Когда знаешь - ВСЕ просто" 🙂 ..... Благодарю
А когда знаешь, где посмотреть, то еще проще :)
@@3855298 если знаешь КАК делать, то и смотреть не нужно 🙂 ... просто берешь и делаешь 🙂
Спасибо, Николай. Сколько всего уже применил по вашим урокам. И каждый раз новые являются актуальными. Ну и отдельное спасибо за Plex.
Столько лет обучать людей как оптимально пользоваться экселем, моё почтение
Чудеса Николая!) спасибо из Израиля!
Сказать, что это КРУТО, это ничего не сказать. Это КРУТИЩЕ!!!
Волшебник 🪄!!!!!!! Спасибо Николай!!!
Низкий поклон и большой респект за ваши труды в области эксел образования!
Громадное спасибо ❤️ всегда с нетерпением жду Ваши видео!
В примере с макросом надо добавить использование функции Aggregate для вывода итогов и будет вообще замечательно
Отличный ролик, нужная в жизни функция. Спасибо большое, Николай
Николай, Вы лучший! Спасибо Вам за Ваш труд!
Уау ! Найду применение обязательно! Николай, РЕСПЕКТ!
Как всегда СУПЕР ПОМОГАЛОЧКА! Спасибо огромное!👏👏👏
Николай, спасибо Вам большое, очень помогают мне ваши видео и Ваш прекрасный мужской голос ❤
🙏😉
Спасибо огромное.Ещё один очень полезный урок.
Спасибо за видео! Очень понравилась функция фильтр, и Спасибо, что выпустили бумажную версию про pq, давно ждала !!!
Спасибо за видео. Вариант с макросом особенно понравился. Но самый простой способ просто скопировать и вставить транспонировав.
Пока не знаю, как этот урок мне может пригодится. Но буду знать, что это возможно. И буду знать где найти информацию. Спасибо.
Просто тупо надо накапливать знания а где и как их применять мозг потом сам решит )))
Шикарный ролик. Спасибо.
Спасибо, очень полезный урок.
Отличный ролик. Спасибо, Николай.
Спасибо! Очень полезные приемы!
Как всегда видео очень познавательное! Спасибо за ваши труды. Пользуюсь инструментами Plex и всем рекомендую!
Спасибо вам большое за ваш труд
Я с работы два часа назад пришёл. Хочу обратно на работу что бы попробовать реализовать это.
Сегодня утром нужно было скрыть все столбцы с выручкой за каждый день месяца, а с количеством оставить)))) завтра попробую, спасибо)
😊 🙏
уоу, я бы не поверила в это:)👍👍👍👏👏👏
Спасибо за видеоурок!
Николай, спасибо за очередной отличный материал! Привет из Киева
Спасибо, как всегда - магия!
Как всегда гениально
Можно еще так:
-создаем формулой список заголовков столбцов на отдельном листе
-на этом листе создаем колонку с чекбоксами, по значению в которой будет определяться скрывать или показывать столбцы в основной таблице.
- далее макросом производим скрытие или отображение нужных столбцов.
вот этого как раз и не хватало мне
Спасибо!! Полезно. Но в ролике 4 способа, ещебы временные метки к ним было бы супер для последующего поиска.
Благодарю!
Спасибо
Вам спасибо!
Браво!
Какой же Вы умный!
🙄
Спасибо!
Круто!
Это не просто, но интересно)
Ты крут!
Николай, спасибо большое, очень помогли ваши разъяснения по фильтру с помощью макроса. А можно ли сделать двух и более фильтрацию, что бы из первично отфильтрованных данных можно было дальше их фильтровать? Моя попытка ввести допстроку "истина/ложь" и доп.код не дали фильтр из фильтра, они работают сами по себе. Возможно нужна связка в коде какой-то командой.... Подскажите, пожалуйста, решение. Спасибо.
Да, сколько смотрю Ваши уроки, могу сказать, что Вы очень крутой 😎 жалко что мало видео по power pivot, мне интересно просто может ли power pivot объединять 2 таблицы в одну по более одного ключа, просто понять целесообразность отличия от power query. Ну или видео с олап кубами)
Точно не может - только одна активная связь может быть между двумя таблицами в Power Pivot. Поэтому иногда проще в Power Query по множеству столбцов объединять, да.
@@planetaexcel спасибо большое!
Первый:)
Шикарно!
Николай, подскажите, пожалуйста, где и как можно прочитать про все такие нюансы, как «функция ЕСЛИ не умеет читать критерий А*, а функция СЧЁТЕСЛИ может»?
Это уже собственный опыт и метод тыка, обычно :)
В справке по табличным функциям, которая до офиса 2010 была автономная, а теперь в интернете, как и help по visual basic, и конечно же, в большинстве компаний корпоративный IT доступ туда заблокировал 🤬
Хотел бы добавить еще одну вишенку -формула ТРАНСП - простейшее решение.
Не совсем - во всех версиях Excel за исключением последней, её надо было вводить как формулу массива :)
Бывают случаи, когда данные нельзя транспонировать. Поэтому решения просто волшебные. Век живи , век учись.
Спасибо, Николай Павлов за уроки. Я записал макрос где с первого листа копирует выделенные ячейки и вставляет на второй лист, как сделать что бы на втором листе активная ячейка переходила на следующую строку не подскажите пжл...
ActiveCell.Offset(1,0).Select
Только сначала выбрать этот лист, т.к. двигать курсор можно только на активном листе:
Sheets("имя_листа2").Select
А потом не забыть вернуться.
И чтобы в глазах не рябило, нужно в начале поставить
Application.ScreenUpdating=False
В копилку знаний по эксель!
Интересно, можно ли как то сделать фильтр с выпадающим списком с возможностью выбора нескольких пунктов чекбоксом. Просто выпадающий список в ячейке А2 я сделал, это интересно, но было бы здорово выбрать несколько столбцов чтобы сравнить данные например.
Здравствуйте, Николай. У меня вопрос: Есть ли такая формула, где ФИО прописана CAPS'ом и чтоб на другом ячейке (или листе) будет прописана прописью полностью без CAPS'а?
Очень хорошее видео. А можно ли сохранить условное форматирование в виде какого ни будь макроса, что бы оно было доступно для нескольких подобных файлов
Можно включить запись макроса при создании или применении форматирования, потом подправить в коде ссылки и экспортировать макрос в файл.
Но у этого способа есть минусы, и много. Мне кажется, для сохранения пользовательских форматов проще создать шаблон.
Здравствуйте Николай! А в Exel можно чертежи рисовать? Есть ли такая возможность?
Коль, а может ли гугл эксель автоматически добавлять какое-то число в ячейку по таймингу и суммировать его? К примеру - надо, что б в ячейке А1 через каждый час прибавлялось допустим число 10 и суммировалось? ну и так до бесконечности, пока не остановлю
Николай, Сводная таблица требуется не "в табличной форме", а "в сжатой форме" - в поле строк несколько позиций. Сводная делает "сортировку от А до Я" того, что в верхней строки в поле строк. Как сделать порядок строк как в источнике данных - верхняя строка в поле строк? где для уровней идентификаторы типа "1.1", под которыми много нижних уровней. Сделал слева сводной доп. столбец - мгновенное заполнение - с цифрами 1,1; 1,2 вместо 1.1.; 1.2. и т.д. - сортировка по нему, но формула ИНДЕКС(; ПОИСКПОЗ ()) находит только первое совпадение, а наименования того, что в верхней строке в поле строк - повторяются в Источнике данных.
добрый день. Как в экселе посчитать максимальное среднее значение стоимости (не от всего документа, а при включенном фильтре)? У меня считает только всего документа вне зависимости от фильтра. Ну или как например сделать зависимость, чтобы я мог выбрать максимальный средний РОИ и таблица отфильтровала нужное доходы, расходы и другие параметры, чтобы его получить? Или высчитать при каких параметрах я получу максимальный средний РОИ?
интересно, а можно простыми формулами брать данные из мадели данных? не зогружая их на лист
Николай, какой из форматов Excel будет при сохранении занимать меньший объём файла при той же функциональности? Столкнулся с тем, что xlsx (Книга Excel) почти в 2 раза больше, чем xlsb (Двоичная книга Excel), при этом так же работает Сводная таблица, Условное форматирование, Срезы. Что же это за "фокус-покус" с увеличением объёма xlsx?
Файл xlsb - 39 Мб; тот же файл xlsx - 66 Мб.
В теории, формат xlsb может быть как больше, так и меньше по объёму. На больших файлах Excel при работе с xlsb вроде как быстрее должен быть, при одинаковой функциональности.
Николай, для сохранения xlsx как xlsb - надо открыть файл xlsx и заново сохранить как xlsb - выбрать Тип файла - Двоичная книга Excel. Простое изменение расширения xlsx на xlsb не работает - будет ошибка при открытии файла. Можно ли не открывая файла менять тип файла - xlsx на xlsb и наоборот? Хотелось бы групповое сохранение файлов xlsx как xlsb и наоборот - не открывая файлов. Есть ли способы это сделать?
Музычка бодрая)
Николай, порекомендуете пжл курс по vba
А как сделать, чтобы по нажатию кнопки/установке флажка/переключателя скрывались или отображались определенные столбцы?
У меня вот проблема, уже два дня не могу решить.
Например, есть ячейка A10
В ячейку А10 вписать 5
И есть ячейка B1.
Например в B1 вписать 10
И есть еще 3-я ячейка С1.
В С1 вписать =A(B1)
Я хотел что бы тут: =A(B1), и
B1 превратилась в число 10, и стало A10, и ячейка С1 вывела 5.
Возможно такое?
Подскажите, пожалуйста, есть 2 книги с данными. Задача сделать так, чтобы данные из одной книги подтягивались автоматически в другую. Таблицы не сводные. ВПР тут поможет?
=Индекс , поискпоз попробуйте
@@MsPatriot1970 И про них я знаю. Вопрос в автоматизации...
@@Artem_Klopov если с Excel у вас всё всерьез и надолго, нужно переходить от табличных формул на VBA. Тогда на ЛЮБОЙ вопрос "а можно ли в Excel..." у вас всегда будет ответ "Да" 😎
Добрый день! У меня нет такой функции ФИЛЬТР, если ее набирать, говорит, что функция неверная. но зато есть функция ФИЛЬТР.xml ее действие другое. Как быть? (ексель 2019)
👏👏👏👏🔥🔥🔥
подскажите пожалуйста, как при распечатке проставлять нумерацию страниц?
Меню Файл - Печать - Параметры страницы - Колонтитулы - Верхний (или нижний) колонтитулы - в строке после "А" - выбрать значок "Вставить номер страницы".
спасибо
я бы последний макрос написал так:
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
Вопрос не в тему немного - можно ли нажатием клавиш протянуть формулу вниз до конца диапазона? Как альтернатива двойному клику по черному уголку ячейки
Точно нет. Я для такого в свое время писал макрос (он есть в PLEX, кстати).
Находясь в ячейке с формулой Shift ↓ помечаем диапазон вниз и
Ctrl D, т.е. “Fill DOWN from cell above”
Чтобы пометить до конца диапазона вниз подходит Ctrl Shift End если это, например, новый правый столбец с формулой в первой строке.
А Ctrl+C / Ctrl+V не устраивает?
@@user-br2rk6qd8u с Ctrl+D меньше на одну комбинацию нажатий :)
Кстати, в быстром наборе и протягивании формул я долго себя приучал к Ctrl+Enter чтобы оставаться в "несущей" ячейке
0:08 Эксэл 😁
а там не опечатка закралась в текст макроса: Entire - Entre ?
Обновлять запрос-таблицу удобнее: ПКМ - Обновить
А вот как сделать фильтрацию по стоке, скажем Москва, и нужно отфильтровать данные в диапазоне от 30 до 80 и если столбцов будет 150?
В макросе дописать 1 строчку.
Вообще, если пользоваться VBA, то нерешаемые проблемы и ограничения просто не существуют.
Долой костыли! Всем учиться работать в Excel по-человечески - с узкой таблицей.
А почему человек Анна не имела данных в сводной таблице при выгрузке из Power query?
В смысле? А как же столбец В на моменте 15:49?
@@planetaexcel так это момент окончания видео... Или я не так понял?
Здравствуйте Николай! Вы большой специалист по программе EXCEL. У меня есть проблема по установки даты без времени. В ячейке дата (не сегодняшняя) и время. Как мне удалить время но оставить дату. Пробовал нажимать на кнопку ДАТА во ВСТАВКЕ. Устанавливается сегодняшняя дата. А мне надо оставить старую дату. Никак не найду как это сделать. Помогите пожалуйста...
Если дата с временем в А1, то можно в соседней ячейке ввести формулу =ОТБР(A1) и поставить для неё формат даты. А в общем и целом очень советую глянуть www.planetaexcel.ru/techniques/6/88/ чтобы понимать как Excel внутри работает с датами и временем.
@@planetaexcel Большое спасибо!!!
13:45 должно быть Else cell.EntireColumn.Hidden в обеих строках? а то "entre" - не по-английски написано, а как-бы по-французски.
Всё правильно написано. Там свойству hidden задается значение True или False.
Получается Expected End Sub в Excel 2010
Беру на вооружение фразу "не забываем проэфчетырить"
😊👌
В последнем способе итого последнего столбца получается не соответствует отображаемому, а так огонь
Можно использовать функцию АГРЕГАТ для этого - она умеет суммировать только видимые (нескрытые) ячейки.
Кто нибудь знает альтернативу данной формулы: =если(и(B2>=сегодня();B2=сегодня();C2=сегодня();D2
Ошибка всех начинающих в том, что они сразу строят подобие Сводной таблицы, а это надо предоставить программе Excel, а не мучить себя самих, и других своими "таблицами".
Не получается - "команда не может быть выполнена над указанным диапазоном . Выберите одну ячейку из диапазона и повторите попытку . " как же заманал этот говноексель , с гугл таблицами куда проще !
Разве эксель не объявил санкции? Или вы пираткой пользуетесь? Вообще, рекламировать вражеский софт, это непатриотично.
Предложите аналогичный по возможностям отечественный - с удовольствием "прорекламирую".
Помогите с данным кодом, нужна помощь. Сделать для множества фигур
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
Спасибо!