Магия Excel

Что почитать про сводные таблицы? Конечно, книги Билла Джелена. У него выходит издание по каждой версии Excel. Шикарная книга, без альтернатив для глубокого погружения в сводные таблицы. Тут и все мыслимые нюансы "классических сводных" (построенных по данным в Excel), и сводные диаграммы, и Power Query, и работа со сводными через макросы, и функция GETPIVOTDATA, и сводные по модели данных (Power Pivot), и функции кубов (превращение сводной, основанной на модели данных, в формулы). Слева обновление по последней версии 365. То есть это и для тех, у кого "коробочная" версия Excel 2021, и для подписчиков 365, и для тех, кто использует Excel Online в браузере, где сводные наконец появились (последние два пункта будут вместе, скорее всего). Есть пара новых техник в главе с лайфхаками, но главное — новые главы. Про сводные таблицы в Excel Online, про построение "сводных таблиц" без собственно сводных - с помощью новых формул с динамическими массивами (которые как раз появились в 365 и 2021) или внутри Power Query. Также есть глава про "анпивот" — unpivoting в Power Query. Книга по версии 2021 должна выйти на русском уже буквально на днях. Обратите внимание, что в российском издании скриншоты будут с оригинальным (англоязычным) интерфейсом, как и файлы-примеры. Команды и функции будут на 2 языках. Это особенно удобно для тех, кто работает в Excel с англоязычным интерфейсом, но читать хочет на русском. В книге по 2019 версии, выходившей в другом издательстве, скриншоты и файлы на русском, команды в тексте только на русском. Ссылка на полный обзор книг по Excel: https://teletype.in/@renat_shagabutdinov/excellent_books

Быстрая фильтрация в сводной таблице Если вам нужно быстро исключить некоторые значения из сводной: выделите то, что нужно убрать (в строках или столбцах отчета сводной таблицы) и нажмите Ctrl + - (минус). Данные будут отфильтрованы, те значения, что вы выделяли, будут исключены в фильтре. P.S. Если тема сводных для вас актуальна и вы любите запрыгивать на подножку уезжающего поезда, то завтра начинается практикум по сводным таблицам. Три встречи по 2 часа, возможность выиграть призы за лучшую домашку, десятки слайдов, файлы с примерами в исходном и готовом состоянии, кот Лемур и пес Штрудель в качестве соавторов ДЗ — вот такой комплект! Ловите промокод, с ним совокупная скидка на практикум составит 35%: Lemur_35 Записываться здесь: https://www.mann-ivanov-ferber.ru/courses/practicum-excel/

Считаем уникальные значения в сводной таблице Допустим, у нас есть таблица со сделками: в разных городах с разными клиентами. Мы хотим понять, сколько в каждом городе у нас клиентов. Если в сводной считать "Количество" по городам, то это будет количество строк, то есть сделок, а не уникальных значений. Увы, в стандартном наборе вычислений (из 11 операций) в сводных подсчета уникальных значений нет. Но если добавить наши данные в модель данных (Power Pivot) при создании сводной, то такая возможность появится! В сам Power Pivot можно даже не заходить, и не обязательно собственно строить модель данных, добавляя туда еще какие-то таблицы. Если вам нужна только эта возможность — просто включите флажок "Добавить эти данные в модель данных" (Add this data to the Data Model) при вставке сводной. И далее в параметрах поля значений выбирайте операцию "Число разных элементов" (Distinct Count). P.S. А в Google Таблицах функция для подсчета уникальных в сводных есть — COUNTUNIQUE (как и обычная функция рабочего листа, не в сводных, с таким именем).

Ищем данные в разных таблицах с помощью ВПР / VLOOKUP и ДВССЫЛ / INDIRECT Вот такая задача от подписчика: есть сотрудники разных специальностей (должностей), и в зависимости от отдела (или другого параметра) нам нужно искать их разряд в разных таблицах. У разных подразделений разная шкала оценки — например, где-то третий разряд присваивается с 60 лет, а где-то с 50. Как быть? Если бы задача была с одной таблицей, то все просто решается функцией ВПР / VLOOKUP: ищем возраст сотрудника в таблице, получаем разряд из второго столбца. Последний (четвертый аргумент) ВПР не трогаем, т.к. по умолчанию у этой функции интервальный просмотр, то есть поиск ближайшего наименьшего числа, а именно это нам и нужно в данном случае. =ВПР(возраст сотрудника; таблица с возрастами и разрядами; 2) Но у нас таблица не одна! Во втором аргументе ВПР могут быть разные таблицы, в зависимости от должности. Поступим так: — превратим таблицы для каждого отдела в "умные" таблицы (Форматировать как таблицу / Format as Table или Ctrl + T или Ctrl + L) — назовем каждую по имени отдела — теперь можно ссылаться на таблицы по имени. Нам надо получить название отдела по сотруднику (найти должность в списке "должность-отдел" и подтянуть отдел) — это и будет название нужной таблицы. Чтобы название таблицы из текста стало ссылкой, мы засовываем всю конструкцию в ДВССЫЛ / INDIRECT — функцию, превращающую текст в ссылку. В общем виде будет так: =ВПР(возраст сотрудника; ДВССЫЛ(формула для определения названия нужной таблицы); 2) Разбор задачи — в видео, а в соседнем посте файл (книга Excel) с формулой. Эту идею можно использовать в любой подобной задаче, когда нужно искать значение в нескольких диапазонах, а не в одном.

Друзья, привет! Обновляем подборку самых сочных постов канала, посмотрите, не пропустили ли чего полезного: Ctrl + Backspace - очень удобное сочетание клавиш для возвращения к активной ячейке Макрос для сравнения двух файлов (книг Excel) Удаляем строки с пустыми ячейками в одном из столбцов Макрос: создаем оглавление в книге Анализируем сезонность в сводной таблице Поиск по двум критериям План-факт через комбинированную диаграмму Видеоурок: "старые" и новые формулы массивов Функция СУММЕСЛИМН / SUMIFS: сумма по условиям Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)

А еще можно фильтровать по значению, цвету заливки / шрифта и даже значку (из наборов условного форматирования) с помощью контекстного меню. Щелкайте правой кнопкой по ячейке с нужным значением/цветом и выбирайте "Фильтр" — и далее нужный вариант.

Фильтр по выделенному Итак, вы хотите фильтровать данные по значению выделенной ячейки. Для этого можно добавить кнопку на панель быстрого доступа. Называется она "Автофильтр", но это именно фильтр по выделенному значению. Параметры Excel — Настроить панель быстрого доступа (панель инструментов — все команды или команды не на ленте — Автофильтр — Добавить Customize Quick Access Toolbar — Commands Not in The Ribbon — AutoFilter — Add Теперь ставим фильтр в диапазоне (если его еще не было), выделяем ячейку, по которой нужно отфильтровать, и нажимаем на добавленную на панель быстрого доступа кнопку! А еще кот Лемур напоминает: команды на панели можно вызывать сочетанием Alt (или косой черты или F10) и цифры (соответствующей расположению нужной команды по порядку). Так что у нас с вами теперь есть сочетание клавиш для фильтрации по выбранному значению!

Схватка двух ёкодзун: сравнение Excel и Google Spreadsheets. Друзья, в этой (регулярно обновляемой) табличке — подробное сравнение Excel (не Online, а обычной версии) и Google Таблиц: какие функции есть только в одном из редакторов, что ломается и что сохраняется при переносе файлов, какие есть нюансы в сводных, пользовательских форматах и других темах. По некоторым темам и функциями есть ссылки на дополнительные материалы (видео, посты). https://docs.google.com/spreadsheets/d/10jhUBPLhmtx-km5sdITumlmkgIn8DKqsdxyULChVaq8

Пользуетесь окном поиска в Excel? Если нет, уберите его в параметрах: Файл — Параметры — Общие — Свернуть поле Поиска File — Options — General — Collapse the Microsoft Search Box В любом случае справа у вас останется иконка лупы для раскрытия этого поля. Но зато оно не будет все время занимать часть панели наверху.

У вас есть список в несколько столбцов, а вам нужно сделать его одномерным? Например, как на скриншоте — есть выступающие в нескольких залах (каждый зал в своем столбце), а нужно получить один список всех выступающих. Тогда используем функцию TOCOL / ПОСТОЛБЦ (увы, недоступную в старых версиях Excel). Чтобы сделать список без повторов (то есть если человек выступает в разное время или разные дни, мы все равно упоминаем его один раз) и отсортировать, можно добавить функции УНИК / UNIQUE (только уникальные значения) и СОРТ / SORT (сортировка).

Если мы склеиваем текстовые значения и числа формулой, например, к фиксированному тексту "Средние продажи:" добавляем функцию СРЗНАЧ/AVERAGE, которая будет возвращать среднее значение: ="Средние продажи: "&СРЗНАЧ(C2:C13) То форматирование чисел "потеряется" и будет не как в исходных ячейках, а без всяких атрибутов форматирования. Не будет, например, разделителей групп разрядов, а после запятой будут отображаться все цифры. Формат ячейки с формулой менять бесполезно: в ней уже хранится текст (потому что на выходе в результате работы операции "склеивания", конкатенации, которая происходит благодаря символу &, мы получаем именно текстовое значение). И отформатировать числовой кусочек текста отдельно через формат ячейки нельзя. Выход — использовать функцию ТЕКСТ / TEXT. Она может любое значение (в том числе число, взятое из ячейки или полученное вычислением) отформатировать по заданному числовому формату и возвращает результат в виде текста. Синтаксис: =ТЕКСТ(число; формат). Формат указывается как в пользовательских форматах (подробнее о них см видео). Так что в нашем случае, если мы хотим, чтобы все средние значения были с одним знаком после запятой, понадобится следующая формула: ="Средние продажи: "&ТЕКСТ(СРЗНАЧ(B2:B13);"0,0")