Магия Excel

Магия табличных формул Что там с видеоуроками на Sponsr? Более 30 видеоуроков уже доступны — это 5 модулей: 1 Основы формул. Все нюансы, формулы в Google Таблицах, ошибки в формулах и т.д. 2 Даты и время. От текущей даты до рабочих дней и извлечения элементов даты 3 Текст. Основы, функции, регулярки. 4 Логические значения и условия. От флажков до расширенного фильтра с формулами. Формулы в проверке данных и условном форматировании. 5 Поиск данных. ВПР, ПРОСМОТРX, магия функции ИНДЕКС (ей будут посвящены несколько видеоуроков), поиск по 2 критериям, на разных листах, и многое другое Что впереди? — Еще видео про поиск, включая функцию СМЕЩ и полностью универсальную формулу поиска — Формулы массива — старые, новые и гуглотабличные — LET и LAMBDA — Формулы кубов и Power Pivot — Пользовательские функции — И многое другое. Пожелания и заявки принимаются 😸 Все это с файлами-примерами. Где подписаться? Тут: https://sponsr.ru/excel_magic

Выделение цветом (не)защищаемых ячеек. Условное форматирование и функция ЯЧЕЙКА / CELL Длительность: 4 мин Все ячейки в Excel по умолчанию являются защищаемыми (и эта защита вступает в силу, когда защищен лист). Как найти те ячейки, у которых защиты нет (или только те, у которых она есть)? Будем использовать функцию ЯЧЕЙКА / CELL, которая в числе прочего возвращает информацию о защите. И применим ее в формуле условного форматирования, чтобы автоматически раскрашивать незащищаемые ячейки. Видео на Kinescope (доступно в России) Видео на Youtube Также можно посмотреть на сайте, как и десятки других видео

Число прописью на новых функциях Воспринимайте это скорее как развлечение и демонстрацию новых функций, чем как реальное решение. Все-таки для нормальной суммы прописью лучше использовать пользовательские функции на макросах (такие можно найти и отдельно, и в виде надстроек — например, функция есть в составе PLEX Николая Павлова для Excel, и есть надстройка сугубо с подобной функцией NUMBERTEXT Александра Иванова для Google Таблиц) Что же происходит в формуле на скриншоте: — получаем число прописью на тайском с помощью старой функции БАТТЕКСТ — переводим его на русский новой функцией ПЕРЕВОД — удаляем слова "бат" или "бата" и точку с пробелом или без него из перевода с помощью РЕГЗАМЕНИТЬ.

Условное форматирование с формулами Длительность: 13 мин — Общие принципы условного форматирования с формулами — Выделяем строку целиком — по значению или отдельному слову — Выделяем заголовки столбцов, в которых есть хотя бы одна ошибка — Выделяем всю строку при выполнении плана Файл с примерами Видео на Kinescope (доступно в России) Видео на Youtube Также можно посмотреть на сайте, как и десятки других видео

Границы ячеек: быстро добавляем и убираем Два сочетания клавиш: Ctrl + Shift + 7 — добавляем внешнюю границу у выделенного диапазона (диапазонов) Ctrl + SHift + - — удаляем границы У вас не работает? Скорее всего, причина в том, что перед открытием Excel была русская раскладка. По той же причине могут не работать сочетания для вставки текущей даты/времени. Напоминаем, там картина следующая: Если при открытии Excel у вас была русская раскладка: Ctrl + Shift + 4 для даты Ctrl + Shift + 6 для времени Ctrl + Shift + 4 + пробел + Ctrl + Shift + 6 для даты и времени в одной ячейке Если была английская: Ctrl + ; для даты Ctrl + Shift + ; для времени Ctrl + ;+ пробел + Ctrl + Shift + ; для даты и времени в одной ячейке

Вот вам немного ощущения власти при работе с диаграммами: Alt + F1 — вставить стандартную диаграмму (внедренную) F11 — вставить диаграмму на отдельном листе Ctrl + Shift + C — скопировать форматирование выделенного элемента диаграммы (например, оси или подписи данных) Ctrl + Shift + V — вставить форматирование. Бывает удобно, когда вы настроили сразу несколько аспектов форматирования (шрифт, начертание, цвет и т.д.) и надо их все перенести на другой элемент. "Формат по образцу" в диаграммах тоже работает 🐁 P.S. У вас есть любимые приколы про Excel? Кидайте в комментарии 👉🏻

Срезы: несколько советов Видео без звука: напоминание о том, что с зажатой клавишей Alt срезы (а также другие объекты графического слоя — внедренные диаграммы, фигуры, временные шкалы) двигаются и меняют размеры по границам ячеек. А также: — в срезах можно убрать заголовок (часто и так понятно по вариантам, о чем идет речь) — в срезах можно сделать несколько столбцов — получится более компактный список. А здесь можно прочитать про подключение одного среза к нескольким сводным таблицам.

Ссылки на несколько листов в формулах Excel Общий вид ссылки на несколько листов (это ссылка на листы от первого и до последнего по ярлыкам слева направо; если порядок листов в книге изменится, ссылка в формуле не поменяется): 'Первый лист:Последний лист'!Диапазон Следующая формула суммирует числа из ячеек B2 на листах от "$ счет" и до "Счет в юанях" включительно: =СУММ('$ счет:Счет в юанях'!B2) В названиях листов можно использовать символ подстановки — звездочку. Если в книге много листов со словом "Расходы" в названии ("Расходы январь", "Расходы февраль", . . . ). Следующая формула позволит просуммировать ячейки A1 со всех этих листов: =СУММ('Расходы*'!A1) Правда, в отличие от ссылки с двоеточием, звездочка в формуле не сохранится - после ввода такой формулы ссылка на лист со звездочкой превратится в формулу с отдельными ссылками: =СУММ('Расходы январь'!A1;'Расходы февраль'!A1;'Расходы март'!A1;...)

Быстрая фильтрация в сводной таблице Если вам нужно быстро исключить некоторые значения из сводной: выделите то, что нужно убрать (в строках или столбцах отчета сводной таблицы) и нажмите Ctrl + - (минус). Данные будут отфильтрованы, те значения, что вы выделяли, будут исключены в фильтре.

Гистограммы Excel: несколько приемов Длительность: 9 мин — гистограммы с отрицательными и положительными значениями (план-факт) — гистограммы с текстом (связанный рисунок) — гистограммы в сводной таблице — отдельно от поля со значениями — применяем гистограммы только к топ-N значений Ссылка на файл с примерами Видео на Kinescope (доступно в России) Видео на Youtube Также можно посмотреть на сайте, как и десятки других видео

Макрос: создаем по отдельному файлу для каждого продукта/города/клиента (для каждого уникального значения в столбце) Итак, вы хотите быстро получить отдельные файлы с данными по каждому значению в том или ином столбце. Забирайте этот макрос, добавляйте его в личную книгу макросов, добавляйте кнопку на панель быстрого доступа и теперь вы можете в любом файле выбрать заголовок любой таблицы/диапазона, нажать эту кнопку и произойдет следующее: 1 В папке с вашей книгой Excel будет создана папка с заголовком ("Продукт", если у вас была активна ячейка с таким заголовком перед вызовом макроса) 2 В этой новой папке будет созданы книги для каждого значения из столбца — по одной на значение. В каждой книге будет данные только по одному этому значению (в случае с продуктом — по одной книге с данными по каждому продукту). Как добавить макрос в личную книгу макросов, чтобы он был доступен при работе с любыми файлами Excel — читайте здесь. Сам макрос ниже по ссылке (сохраняйте файл с макросом, заходите Alt+F11 в редактор макросов, добавляйте файл в личную книгу макросов PERSONAL.xlsb — для этого выберите Import File в контекстном меню по правой кнопке мыши) В очень коротком видео со звуком показываю пример, как именно происходит магия. Ссылка на макрос