В связи с недавним выходом новинки, посвященной языку M (он используется в Power Query в Excel и Power BI) время обновить обзор основных книг по каждой эксельной теме 😸
Итак, если вы хотите изучить конкретную тему в рамках Excel, вот по одной книге на каждую.
Excel в целом
Microsoft Excel Inside Out (Office 2021 and Microsoft 365)
На русском:
Excel 2019. Библия пользователя — Куслейка, Александер
Макросы
Microsoft Excel VBA and Macros — Bill Jelen
На русском: Excel 2016. Профессиональное программирование на VBA — Александер, Куслейка (не пугайтесь версии 2016 — макросы не меняются десятилетиями)
Сводные таблицы
Сводные таблицы в Microsoft Excel 2021 и Microsoft 365 — Джелен
Power Query
Скульптор данных в Excel с Power Query — Николай Павлов
или / и
Приручи данные с помощью Power Query в Excel и Power Bi — Пульс, Эскобар
Язык M
The Definitive Guide to Power Query (M): Mastering complex data transformation with Power Query
(книга для глубокого погружения именно в язык M, то есть ее лучше читать с опытом работы в интерфейсе Power Query и при желании решать там нестандартные задачи и писать код самостоятельно)
Power Pivot и язык формул DAX (который используется и в Power BI / других решениях Microsoft)
Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel — Руссо, Феррари
Очень глубоко и основательно про DAX: Подробное руководство по DAX: бизнес-аналитика с Microsoft Power BI, SQL Server Analysis Services и Excel — Руссо, Феррари
Для первого ознакомления с Power Pivot можно начать с глав в книге Джелена про сводные
Формулы в целом
С новыми формулами (LAMBDA, новые массивы), от начального до продвинутого уровня: главы про формулы в Microsoft Excel Inside Out.
С новыми формулами посложнее: Advanced Excel Formulas: Unleashing Brilliance with Excel Formulas
На русском с новыми формулами: главы про формулы у меня в "Магии таблиц"
На русском до 2019 включительно от начального до продвинутого: главы про формулы в Excel 2019. Библия пользователя
На русском до 2019 включительно посложнее: Мастер формул — Николай Павлов
Старые формулы массива (до 2019 включительно)
Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic — Girvin
На русском: Мастер формул — Николай Павлов
Новые формулы массива (динамические массивы)
Up Up and Array!: Dynamic Array Formulas for Excel 365 and Beyond
На русском: немного есть у меня в "Магии таблиц"
Визуализация
Визуализация данных при помощи дашбордов и отчетов в Excel — Куслейка
Итак, если вы хотите изучить конкретную тему в рамках Excel, вот по одной книге на каждую.
Excel в целом
Microsoft Excel Inside Out (Office 2021 and Microsoft 365)
На русском:
Excel 2019. Библия пользователя — Куслейка, Александер
Макросы
Microsoft Excel VBA and Macros — Bill Jelen
На русском: Excel 2016. Профессиональное программирование на VBA — Александер, Куслейка (не пугайтесь версии 2016 — макросы не меняются десятилетиями)
Сводные таблицы
Сводные таблицы в Microsoft Excel 2021 и Microsoft 365 — Джелен
Power Query
Скульптор данных в Excel с Power Query — Николай Павлов
или / и
Приручи данные с помощью Power Query в Excel и Power Bi — Пульс, Эскобар
Язык M
The Definitive Guide to Power Query (M): Mastering complex data transformation with Power Query
(книга для глубокого погружения именно в язык M, то есть ее лучше читать с опытом работы в интерфейсе Power Query и при желании решать там нестандартные задачи и писать код самостоятельно)
Power Pivot и язык формул DAX (который используется и в Power BI / других решениях Microsoft)
Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel — Руссо, Феррари
Очень глубоко и основательно про DAX: Подробное руководство по DAX: бизнес-аналитика с Microsoft Power BI, SQL Server Analysis Services и Excel — Руссо, Феррари
Для первого ознакомления с Power Pivot можно начать с глав в книге Джелена про сводные
Формулы в целом
С новыми формулами (LAMBDA, новые массивы), от начального до продвинутого уровня: главы про формулы в Microsoft Excel Inside Out.
С новыми формулами посложнее: Advanced Excel Formulas: Unleashing Brilliance with Excel Formulas
На русском с новыми формулами: главы про формулы у меня в "Магии таблиц"
На русском до 2019 включительно от начального до продвинутого: главы про формулы в Excel 2019. Библия пользователя
На русском до 2019 включительно посложнее: Мастер формул — Николай Павлов
Старые формулы массива (до 2019 включительно)
Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic — Girvin
На русском: Мастер формул — Николай Павлов
Новые формулы массива (динамические массивы)
Up Up and Array!: Dynamic Array Formulas for Excel 365 and Beyond
На русском: немного есть у меня в "Магии таблиц"
Визуализация
Визуализация данных при помощи дашбордов и отчетов в Excel — Куслейка
OZON
Excel 2019. Библия пользователя | Куслейка Ричард, Александер Майкл купить на OZON по низкой цене (152942947)
Excel 2019. Библия пользователя | Куслейка Ричард, Александер Майкл – покупайте на OZON по выгодным ценам! Быстрая и бесплатная доставка, большой ассортимент, бонусы, рассрочка и кэшбэк. Распродажи, скидки и акции. Реальные отзывы покупателей. (152942947)
Новая функция REGEXEXTRACT в Excel: извлекаем электронную почту, даты и другие фрагменты из текста
Весной 2024 года в Excel были анонсированы функции для работы с регулярными выражениями. Ранее они были доступны в Google Spreadsheets. Разбираем несколько примеров. Извлекаем:
- вес из длинного названия товара
- все адреса электропочты из ячейки с текстом
- даты в разных форматах
- текст в скобках.
https://www.youtube.com/watch?v=xylfF5lS3WY
Весной 2024 года в Excel были анонсированы функции для работы с регулярными выражениями. Ранее они были доступны в Google Spreadsheets. Разбираем несколько примеров. Извлекаем:
- вес из длинного названия товара
- все адреса электропочты из ячейки с текстом
- даты в разных форматах
- текст в скобках.
https://www.youtube.com/watch?v=xylfF5lS3WY
YouTube
Новая функция REGEXEXTRACT в Excel: извлекаем электронную почту, даты и другие фрагменты из текста
Весной 2024 года в Excel были анонсированы функции для работы с регулярными выражениями. Ранее они были доступны в Google Spreadsheets. Разбираем несколько примеров. Извлекаем:
- вес из длинного названия товара
- все адреса электропочты из ячейки с текстом…
- вес из длинного названия товара
- все адреса электропочты из ячейки с текстом…
This media is not supported in your browser
VIEW IN TELEGRAM
Если у вас есть формула, возвращающая динамический массив (то есть результат может быть разных размеров; допустим, функция FILTER / ФИЛЬТР будет возвращать разное количество строк в разное время, если в исходном диапазоне будут добавляться строки или меняться текущие), результат работы такой формулы можно отправить в Power Query.
И в запрос будут попадать новые данные, которые вернет формула в будущем, то есть это не будет статичная таблица с результатом на момент загрузки данных в Power Query.
Смотрим на коротком видео (без звука).
Выделяем любую ячейку с результатом работы формулы:
Данные — Из таблицы/диапазона
Data — From Table/Range
Или просто кликайте правой кнопкой на любую ячейку формулы и в контекстном меню выберите "Получить данные из таблицы/диапазона" (Get Data from Table/Range...)
Про динамические массивы можно подробнее узнать в следующем видео:
https://www.tg-me.com/lemur_excel/95
И в запрос будут попадать новые данные, которые вернет формула в будущем, то есть это не будет статичная таблица с результатом на момент загрузки данных в Power Query.
Смотрим на коротком видео (без звука).
Выделяем любую ячейку с результатом работы формулы:
Данные — Из таблицы/диапазона
Data — From Table/Range
Или просто кликайте правой кнопкой на любую ячейку формулы и в контекстном меню выберите "Получить данные из таблицы/диапазона" (Get Data from Table/Range...)
Про динамические массивы можно подробнее узнать в следующем видео:
https://www.tg-me.com/lemur_excel/95
Статья для тех, кто работает в Р7-Офис. Но и пользователям Excel будет полезно пробежаться и вспомнить про принципы работы формул массива :)
В российском офисном пакете Р7-Офис в таблицах есть некоторые функции, появившиеся в Excel только в 2021 версии вместе с динамическими массивами. При этом формулы массива в Р7 работают как “старые” формулы массива (возможно, это когда-нибудь изменится). Так что новыми функциями пользоваться не так удобно, как в новом Excel, но зато они в принципе есть, в отличие от Excel 2019, допустим 🙂
В статье разбираем:
— Как вводятся и работают старые и новые формулы массива в Excel
— Какие новые функции появились в Excel 2021/365 благодаря динамическим массивам Excel
— И разбираем, как работать с новыми функциями в Р7, где принципы работы формул массива старые, несмотря на наличие новых функций :)
https://shagabutdinov.ru/r7array/
В российском офисном пакете Р7-Офис в таблицах есть некоторые функции, появившиеся в Excel только в 2021 версии вместе с динамическими массивами. При этом формулы массива в Р7 работают как “старые” формулы массива (возможно, это когда-нибудь изменится). Так что новыми функциями пользоваться не так удобно, как в новом Excel, но зато они в принципе есть, в отличие от Excel 2019, допустим 🙂
В статье разбираем:
— Как вводятся и работают старые и новые формулы массива в Excel
— Какие новые функции появились в Excel 2021/365 благодаря динамическим массивам Excel
— И разбираем, как работать с новыми функциями в Р7, где принципы работы формул массива старые, несмотря на наличие новых функций :)
https://shagabutdinov.ru/r7array/
Teletype
Формулы массива и новые функции (как СОРТ, ФИЛЬТР, ПОСЛЕД и другие) в Р7-Офис
В российском офисном пакете Р7-Офис в таблицах есть некоторые функции, появившиеся в Excel только в 2021 версии вместе с динамическими...
This media is not supported in your browser
VIEW IN TELEGRAM
Парочка горячих клавиш для тех, кто работает с Power Query
Многие из вас знают, что сочетание клавиш Alt — F11 открывает редактор Visual Basic (макросы).
А есть еще одно, очень похожее — Alt — F12 — для открытия другого редактора, Power Query.
Внутри самого Power Query можно менять масштаб с помощью сочетаний:
Ctrl — Shift — + (плюс)
Ctrl — Shift — - (минус)
Это будет влиять на масштаб всего, кроме ленты инструментов.
Многие из вас знают, что сочетание клавиш Alt — F11 открывает редактор Visual Basic (макросы).
А есть еще одно, очень похожее — Alt — F12 — для открытия другого редактора, Power Query.
Внутри самого Power Query можно менять масштаб с помощью сочетаний:
Ctrl — Shift — + (плюс)
Ctrl — Shift — - (минус)
Это будет влиять на масштаб всего, кроме ленты инструментов.
Количество листов в строке состояния
В этой самой строке вообще много полезного можно отобразить — кликните по ней правой кнопкой🐭 и убедитесь.
Например, если выбрать "Номер листа" (Sheet Number), то вы будете видеть общее количество листов и порядковый номер активного.
В этой самой строке вообще много полезного можно отобразить — кликните по ней правой кнопкой
Например, если выбрать "Номер листа" (Sheet Number), то вы будете видеть общее количество листов и порядковый номер активного.
Please open Telegram to view this post
VIEW IN TELEGRAM
Media is too big
VIEW IN TELEGRAM
Ищем данные в разных таблицах с помощью ВПР / VLOOKUP и ДВССЫЛ / INDIRECT
Вот такая задача от подписчика: есть сотрудники разных специальностей (должностей), и в зависимости от отдела (или другого параметра) нам нужно искать их разряд в разных таблицах.
У разных подразделений разная шкала оценки — например, где-то третий разряд присваивается с 60 лет, а где-то с 50.
Как быть?
Если бы задача была с одной таблицей, то все просто решается функцией ВПР / VLOOKUP: ищем возраст сотрудника в таблице, получаем разряд из второго столбца. Последний (четвертый аргумент) ВПР не трогаем, т.к. по умолчанию у этой функции интервальный просмотр, то есть поиск ближайшего наименьшего числа, а именно это нам и нужно в данном случае.
Но у нас таблица не одна! Во втором аргументе ВПР могут быть разные таблицы, в зависимости от должности.
Поступим так:
— превратим таблицы для каждого отдела в "умные" таблицы (Форматировать как таблицу / Format as Table или Ctrl + T или Ctrl + L)
— назовем каждую по имени отдела
— теперь можно ссылаться на таблицы по имени. Нам надо получить название отдела по сотруднику (найти должность в списке "должность-отдел" и подтянуть отдел) — это и будет название нужной таблицы. Чтобы название таблицы из текста стало ссылкой, мы засунем всю конструкцию в ДВССЫЛ / INDIRECT — функцию, превращающую текст в ссылку.
В общем виде будет так:
Разбор задачи — в видео, а в соседнем посте файл (книга Excel) с формулой. Эту идею можно использовать в любой подобной задаче, когда нужно искать значение в нескольких диапазонах, а не в одном.
Вот такая задача от подписчика: есть сотрудники разных специальностей (должностей), и в зависимости от отдела (или другого параметра) нам нужно искать их разряд в разных таблицах.
У разных подразделений разная шкала оценки — например, где-то третий разряд присваивается с 60 лет, а где-то с 50.
Как быть?
Если бы задача была с одной таблицей, то все просто решается функцией ВПР / VLOOKUP: ищем возраст сотрудника в таблице, получаем разряд из второго столбца. Последний (четвертый аргумент) ВПР не трогаем, т.к. по умолчанию у этой функции интервальный просмотр, то есть поиск ближайшего наименьшего числа, а именно это нам и нужно в данном случае.
=ВПР(возраст сотрудника; таблица с возрастами и разрядами; 2)
Но у нас таблица не одна! Во втором аргументе ВПР могут быть разные таблицы, в зависимости от должности.
Поступим так:
— превратим таблицы для каждого отдела в "умные" таблицы (Форматировать как таблицу / Format as Table или Ctrl + T или Ctrl + L)
— назовем каждую по имени отдела
— теперь можно ссылаться на таблицы по имени. Нам надо получить название отдела по сотруднику (найти должность в списке "должность-отдел" и подтянуть отдел) — это и будет название нужной таблицы. Чтобы название таблицы из текста стало ссылкой, мы засунем всю конструкцию в ДВССЫЛ / INDIRECT — функцию, превращающую текст в ссылку.
В общем виде будет так:
=ВПР(возраст сотрудника; ДВССЫЛ(формула для определения названия нужной таблицы); 2)
Разбор задачи — в видео, а в соседнем посте файл (книга Excel) с формулой. Эту идею можно использовать в любой подобной задаче, когда нужно искать значение в нескольких диапазонах, а не в одном.
Интерфейс Excel: приемы и горячие клавиши для ускорения работы
Настраиваем интерфейс Excel:
— закрепляем и скрываем ленту инструментов
— вызываем команды с помощью клавиш
— "создаем" собственные сочетания клавиш для команд
— добавляем на панель быстрого доступа любые инструменты и команды — даже те, которых нет на ленте.
Лемур уверен: хоть что-то из этого видео вы раньше не знали :) Например, что можно настраивать панель быстрого доступа для отдельных файлов или добавлять туда любые команды, даже те, что с ленты инструментов не добавляются или добавляются только вместе со всей своей коллекцией.
Пишите в комментариях, что оказалось наиболее полезным!
Кстати, эти знания пригодятся вам и для настройки других приложений MS Office.
https://www.youtube.com/watch?v=hRGpkN787Yw
Настраиваем интерфейс Excel:
— закрепляем и скрываем ленту инструментов
— вызываем команды с помощью клавиш
— "создаем" собственные сочетания клавиш для команд
— добавляем на панель быстрого доступа любые инструменты и команды — даже те, которых нет на ленте.
Лемур уверен: хоть что-то из этого видео вы раньше не знали :) Например, что можно настраивать панель быстрого доступа для отдельных файлов или добавлять туда любые команды, даже те, что с ленты инструментов не добавляются или добавляются только вместе со всей своей коллекцией.
Пишите в комментариях, что оказалось наиболее полезным!
Кстати, эти знания пригодятся вам и для настройки других приложений MS Office.
https://www.youtube.com/watch?v=hRGpkN787Yw
YouTube
Интерфейс Excel: приемы и горячие клавиши для ускорения работы
Настраиваем интерфейс Excel:
- закрепляем и скрываем ленту инструментов
- вызываем команды с помощью клавиш
- "создаем" собственные сочетания клавиш для команд
- добавляем на панель быстрого доступа любые инструменты и команды - даже те, которых нет на ленте.
- закрепляем и скрываем ленту инструментов
- вызываем команды с помощью клавиш
- "создаем" собственные сочетания клавиш для команд
- добавляем на панель быстрого доступа любые инструменты и команды - даже те, которых нет на ленте.
Как я готовлю и провожу корпоративное обучение по Excel и Google Таблицам
Решили с Лемуром немного поделиться внутренней кухней и рассказать, как мы готовим и проводим корпоративное обучение. Если вкратце, то Лемур готовит файлы с примерами, которые кажутся ему забавными, а потом сутками спит, ну а я доделываю все остальное.
А подробнее — в статье. Обычно в "комплект" обучения входят не только собственно вебинары (для которых нужно подготовить и файлы с примерами, часто на основе рабочих файлов заказчика), но и слайды, ответы на вопросы, чат обучения, домашние работы с проверкой — так что одними уроками дело не ограничивается.
Если захотите организовать обучение для своей команды — пишите на почту ([email protected]) или в телеграм @r_shagabutdinov, буду рад помочь!
https://shagabutdinov.ru/sheets-excel-training/
Решили с Лемуром немного поделиться внутренней кухней и рассказать, как мы готовим и проводим корпоративное обучение. Если вкратце, то Лемур готовит файлы с примерами, которые кажутся ему забавными, а потом сутками спит, ну а я доделываю все остальное.
А подробнее — в статье. Обычно в "комплект" обучения входят не только собственно вебинары (для которых нужно подготовить и файлы с примерами, часто на основе рабочих файлов заказчика), но и слайды, ответы на вопросы, чат обучения, домашние работы с проверкой — так что одними уроками дело не ограничивается.
Если захотите организовать обучение для своей команды — пишите на почту ([email protected]) или в телеграм @r_shagabutdinov, буду рад помочь!
https://shagabutdinov.ru/sheets-excel-training/
Teletype
Как я готовлю и провожу корпоративное обучение по Excel и Google Таблицам
Решили с Лемуром немного поделиться внутренней кухней и рассказать, как мы готовим и проводим корпоративное обучение. Если вкратце...
Флажки (checkboxes) есть и в Excel, и в Google Таблицах. Это переключатели, которые могут в быть в одном из двух положений — вкл/выкл и не зависят при этом друг от друга (в отличие от радиокнопок).
В Google Таблицах флажки в ячейках появились несколько лет назад, в Excel — в 2024 году только в Microsoft 365. Но в старых версиях флажки тоже были, только они не привязаны к ячейкам и поэтому не так удобны (каждый флажок вставляется отдельно, а сразу в целый диапазон ячеек добавить флажки нельзя).
Как их можно использовать?
— Чек-лист: поставили галочку, пункт списка закрасился/зачеркнулся.
— Столбец с неким условием: даем ли скидку, едет ли сотрудник на корпоратив и так далее. Затем этот столбец используем в расчетах (формулах). Или формируем выборку по этому столбцу: два отдельных списка (со скидкой и без, едут и не едут).
— Задаем параметры, используем флажки в интерактивных отчетах: сортируем ли данные, выводим ли их по месяцам или дням, какие столбцы забираем из исходника и так далее.
— Выбираем, какие ряды (регионы/филиалы/показатели и что угодно) отображать в диаграмме.
— И многое другое, на что хватит фантазии :)
Рассматриваем некоторые сценарии в статье:
https://shagabutdinov.ru/checkboxes-excel-sheets/
Или смотрим видео:
https://youtu.be/12k5sVL5iu4
В Google Таблицах флажки в ячейках появились несколько лет назад, в Excel — в 2024 году только в Microsoft 365. Но в старых версиях флажки тоже были, только они не привязаны к ячейкам и поэтому не так удобны (каждый флажок вставляется отдельно, а сразу в целый диапазон ячеек добавить флажки нельзя).
Как их можно использовать?
— Чек-лист: поставили галочку, пункт списка закрасился/зачеркнулся.
— Столбец с неким условием: даем ли скидку, едет ли сотрудник на корпоратив и так далее. Затем этот столбец используем в расчетах (формулах). Или формируем выборку по этому столбцу: два отдельных списка (со скидкой и без, едут и не едут).
— Задаем параметры, используем флажки в интерактивных отчетах: сортируем ли данные, выводим ли их по месяцам или дням, какие столбцы забираем из исходника и так далее.
— Выбираем, какие ряды (регионы/филиалы/показатели и что угодно) отображать в диаграмме.
— И многое другое, на что хватит фантазии :)
Рассматриваем некоторые сценарии в статье:
https://shagabutdinov.ru/checkboxes-excel-sheets/
Или смотрим видео:
https://youtu.be/12k5sVL5iu4
Teletype
Флажки (checkboxes) в Excel и Google Spreadsheets
Ссылка на книгу Excel с примерами (скачивайте на локальный диск как книгу Excel)
План-факт через комбинированную диаграмму
Вот такая диаграмма для сравнения двух показателей (план и факт, производство и продажи, ...). Как ее построить?
— Тип диаграммы в целом - комбинированная, тип каждого ряда данных — гистограмма. Один из рядов данных — на вспомогательную ось, сама ось удалена (так как она не отличается по значениям от основной) — это все можно настроить, нажав "Изменить тип диаграммы" на ленте или в контекстном меню.
— Делаем разные значения бокового зазора у обеих гистограмм, чтобы столбики отличались по ширине. Заливку у обеих делаем прозрачной (в примере 40%). Это настраивается в панели "Формат", выделяем столбики и нажимаем Ctrl+1.
— Подписи забираем из ячеек в столбце D. Там формула (просто темп прироста, факт делим на план и вычитаем единицу) и пользовательский формат:
(смайлики выберите по вкусу)
— Добавляем таблицу данных вместо основных подписей и легенды и убираем всякое ненужное (линии сетки, например).
P.S. Файл с диаграммой прикреплен в отдельном сообщении выше — забирайте!
Вот такая диаграмма для сравнения двух показателей (план и факт, производство и продажи, ...). Как ее построить?
— Тип диаграммы в целом - комбинированная, тип каждого ряда данных — гистограмма. Один из рядов данных — на вспомогательную ось, сама ось удалена (так как она не отличается по значениям от основной) — это все можно настроить, нажав "Изменить тип диаграммы" на ленте или в контекстном меню.
— Делаем разные значения бокового зазора у обеих гистограмм, чтобы столбики отличались по ширине. Заливку у обеих делаем прозрачной (в примере 40%). Это настраивается в панели "Формат", выделяем столбики и нажимаем Ctrl+1.
— Подписи забираем из ячеек в столбце D. Там формула (просто темп прироста, факт делим на план и вычитаем единицу) и пользовательский формат:
+0%* 🔥;-0%* 👎
(смайлики выберите по вкусу)
— Добавляем таблицу данных вместо основных подписей и легенды и убираем всякое ненужное (линии сетки, например).
P.S. Файл с диаграммой прикреплен в отдельном сообщении выше — забирайте!
Выпадающие списки в Excel с автоматическим добавлением новых значений
Зачастую мы создаем выпадающие списки (проверку данных) на основе списка (товаров/фамилий/городов и т.д.), который меняется (дополняется).
Как сделать, чтобы новые значения попадали в выпадающий список автоматически? И чтобы сами списки автоматически появлялись в новых строках вашей таблицы?
Решения могут быть разные в зависимости от того, на разных ли листах данные.
Данные на том же листе — превращаем справочник в Таблицу и ссылаемся на него.
Данные на другом листе и у вас Excel 2010+ — просто ссылаемся на другой лист.
Данные на другом листе и будут пополняться новыми и/или у вас Excel 2007 — ссылаемся через функцию ДВССЫЛ / INDIRECT или через именованный диапазон.
Обсуждаем все нюансы в видео:
https://youtu.be/Mf9fMmWPUCw
Зачастую мы создаем выпадающие списки (проверку данных) на основе списка (товаров/фамилий/городов и т.д.), который меняется (дополняется).
Как сделать, чтобы новые значения попадали в выпадающий список автоматически? И чтобы сами списки автоматически появлялись в новых строках вашей таблицы?
Решения могут быть разные в зависимости от того, на разных ли листах данные.
Данные на том же листе — превращаем справочник в Таблицу и ссылаемся на него.
Данные на другом листе и у вас Excel 2010+ — просто ссылаемся на другой лист.
Данные на другом листе и будут пополняться новыми и/или у вас Excel 2007 — ссылаемся через функцию ДВССЫЛ / INDIRECT или через именованный диапазон.
Обсуждаем все нюансы в видео:
https://youtu.be/Mf9fMmWPUCw
YouTube
Выпадающие списки в Excel с автоматическим добавлением новых значений
Настраиваем выпадающие списки в Excel (проверку данных):
- Превращаем диапазон в Таблицу, чтобы для новых данных проверка данных применялась автоматически
- Превращаем справочник в Таблицу, чтобы новые значения попадали в выпадающие списки автоматически
…
- Превращаем диапазон в Таблицу, чтобы для новых данных проверка данных применялась автоматически
- Превращаем справочник в Таблицу, чтобы новые значения попадали в выпадающие списки автоматически
…
Возрастно-половая пирамида в Excel через условное форматирование (гистограммы)
Алгоритм такой:
1 Создаем два отдельных правила
выделяем по очереди данные по женщинам и мужчинам и —> Главная — Условное форматирование — Гистограммы
Заходим в параметры каждого правила здесь:
Главная — Условное форматирование — Управление правилами — выделяем правило — Изменить правило
И:
2.1 У левого столбца (Женщины в примере) в параметрах меняем направление на "Справа налево"
2.2 У обоих задаем одинаковый максимум, чтобы масштаб тоже был одинаковый. С той же целью обязательно делаем ширины столбцов с гистограммами одинаковыми.
Алгоритм такой:
1 Создаем два отдельных правила
выделяем по очереди данные по женщинам и мужчинам и —> Главная — Условное форматирование — Гистограммы
Заходим в параметры каждого правила здесь:
Главная — Условное форматирование — Управление правилами — выделяем правило — Изменить правило
И:
2.1 У левого столбца (Женщины в примере) в параметрах меняем направление на "Справа налево"
2.2 У обоих задаем одинаковый максимум, чтобы масштаб тоже был одинаковый. С той же целью обязательно делаем ширины столбцов с гистограммами одинаковыми.
Еще один трюк с гистограммами: вставляем сверху текст
Как?
1 Сначала берем нужные данные. Они могут уже быть в ячейках, как у нас в столбце B, но если вы хотите оставить числа отдельно, то в другом столбце сошлитесь на данные:
=B2
(для первой строки)
2 Далее строим гистограмму. Условное форматирование — Гистограммы — выбираем цвет по вкусу.
3 Эта гистограмма будет отображаться вместе с данными (числами). Уберем числа и оставим только гистограмму:
Условное форматирование — Управление правилами — выбираем нашу гистограмму — Изменить правило — Показывать только столбец
4 Осталось скопировать нужный текст ( у нас это A2:A12), вставить в диапазон с гистограммами (можно в первую ячейку) и далее нажать на смарт-тэг "Параметры вставки" справа внизу (см скриншот) и выбрать "Связанный рисунок".
Как?
1 Сначала берем нужные данные. Они могут уже быть в ячейках, как у нас в столбце B, но если вы хотите оставить числа отдельно, то в другом столбце сошлитесь на данные:
=B2
(для первой строки)
2 Далее строим гистограмму. Условное форматирование — Гистограммы — выбираем цвет по вкусу.
3 Эта гистограмма будет отображаться вместе с данными (числами). Уберем числа и оставим только гистограмму:
Условное форматирование — Управление правилами — выбираем нашу гистограмму — Изменить правило — Показывать только столбец
4 Осталось скопировать нужный текст ( у нас это A2:A12), вставить в диапазон с гистограммами (можно в первую ячейку) и далее нажать на смарт-тэг "Параметры вставки" справа внизу (см скриншот) и выбрать "Связанный рисунок".
Астрологи объявили неделю гистограмм!
Итак, на этот раз мы будем применять их не ко всем значениям, а к верхним N.
Например, к топ-5 регионов.
Для этого сначала вставим гистограммы (Главная — Условное форматирование — Гистограммы) и далее создадим правило условного форматирования, которое будет применять никакое форматирование к последним значениям (в нашем случае последним 6, потому что всего регионов 11, а мы хотим оставить топ-5)
Главная — Условное форматирование — Правила отбора первых и последних значений — Последние 10 элементов - меняем 10 на 6 или другое нужное число — выбираем "пользовательский формат" и в нем не меняем ничего, то есть оставляем форматирование пустым.
Идем в Управление правилами условного форматирования и там:
1 Убеждаемся, что у правила с пустым форматированием верхний приоритет (оно идет первым в списке — если это не так, поменяйте порядок с помощью стрелок справа)
2 Включаем флажок "Остановить, если истина" у этого правила.
Итак, на этот раз мы будем применять их не ко всем значениям, а к верхним N.
Например, к топ-5 регионов.
Для этого сначала вставим гистограммы (Главная — Условное форматирование — Гистограммы) и далее создадим правило условного форматирования, которое будет применять никакое форматирование к последним значениям (в нашем случае последним 6, потому что всего регионов 11, а мы хотим оставить топ-5)
Главная — Условное форматирование — Правила отбора первых и последних значений — Последние 10 элементов - меняем 10 на 6 или другое нужное число — выбираем "пользовательский формат" и в нем не меняем ничего, то есть оставляем форматирование пустым.
Идем в Управление правилами условного форматирования и там:
1 Убеждаемся, что у правила с пустым форматированием верхний приоритет (оно идет первым в списке — если это не так, поменяйте порядок с помощью стрелок справа)
2 Включаем флажок "Остановить, если истина" у этого правила.
Так-так-так, смотрите, какие функции подвезли в Excel!
То, что было доступно в Google Таблицах около 10 000 лет, появилось и в Excel — пока только у инсайдеров, но в любом случае рано или поздно функции доедут до всех подписчиков 365.
Это TRANSLATE и DETECTLANGUAGE. На русском — ПЕРЕВОД и ОПРЕДЕЛИТЬЯЗЫК.
В Google Таблицах эти функции называются GOOGLETRANSLATE и DETECTLANGUAGE.
Вот пример применения функций в Гугл — определяем язык и в зависимости от этого формулой выдаем ссылку на российский или американский книжный магазин.
То, что было доступно в Google Таблицах около 10 000 лет, появилось и в Excel — пока только у инсайдеров, но в любом случае рано или поздно функции доедут до всех подписчиков 365.
Это TRANSLATE и DETECTLANGUAGE. На русском — ПЕРЕВОД и ОПРЕДЕЛИТЬЯЗЫК.
В Google Таблицах эти функции называются GOOGLETRANSLATE и DETECTLANGUAGE.
Вот пример применения функций в Гугл — определяем язык и в зависимости от этого формулой выдаем ссылку на российский или американский книжный магазин.
Media is too big
VIEW IN TELEGRAM
Объединение ячеек: почему это не очень хорошо и чем заменить с тем же визуальным эффектом
Объединение ячеек в Excel приводит к тому, что значение хранится только в одной из объединенных ячеек. Если мы рассчитываем использовать эти ячейки в формулах, мы будем иметь дело с пустыми значениями.
Поэтому, если мы предполагаем производить какие-то манипуляции с формулами, лучше избегать объединения. А сохранить его визуальный эффект (убрать повторы) можно с помощью условного форматирования — как, смотрим в видео (4 минуты со звуком)
Объединение ячеек в Excel приводит к тому, что значение хранится только в одной из объединенных ячеек. Если мы рассчитываем использовать эти ячейки в формулах, мы будем иметь дело с пустыми значениями.
Поэтому, если мы предполагаем производить какие-то манипуляции с формулами, лучше избегать объединения. А сохранить его визуальный эффект (убрать повторы) можно с помощью условного форматирования — как, смотрим в видео (4 минуты со звуком)