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