Магия Excel

Серая тема для черно-белой печати диаграмм и других объектов в Excel При подготовке книги к печати столкнулись с типовой проблемой: когда вы печатаете цветные диаграммы, все может сливаться, если печать не цветная. Слева сверху вы видите диаграмму, как она выглядела в Excel изначально (и как будет выглядеть этот фрагмент в электронной — цветной — книге), справа — то, что получается при ч/б печати. Как видите, совсем грустно. Гистограмма с накоплением выглядит как столбики одного цвета, словно и нет у нас двух товарных категорий. Поэтому лучше использовать специальную серую цветовую схему, в которой будут контрастные серые оттенки и будут видны отличия. Итак, если вы планируете печатать в ч/б ваш отчет: Вкладка "Разметка страницы" — группа "Темы" — Цвета — Серая Page Layout — Themes — Colors — Grayscale

Один из множества слайдов, которые мы с котом Лемуром готовим к июньскому практикуму по сводным таблицам (самому мощному инструменту для анализа данных). Правда, на встречах слушатели этих слайдов не увидят. Вот еще — время тратить на презентации на уроках :) Все время (3 по 2 часа) проведем в Excel (ну и малость в Google Таблицах), а слайды — это как мини-методичка для участников, чтобы потом освежить в памяти знания. Еще будут домашки, их разбор (и подарки чемпионам), файлы-примеры до и после, ответы на вопросы. Приходите, встречи будут 14, 20 и 23 июня (а если есть вопросы по формату обучения — спрашивайте в ЛС или комментариях): https://www.mann-ivanov-ferber.ru/courses/practicum-excel/

Хотите, чтобы сводная таблица обновлялась автоматически при открытии файла? Например, если вы отправляете книгу Excel с отчетом коллегам и не уверены, что все обновят сводную сами. 1 Правая кнопка по сводной — "Параметры сводной таблицы" (PivotTable Options) 2 Переходим на вкладку "Данные" (Data) 3 Включаем флажок "Обновить при открытии файла" (Refresh Data When Opening The File)

Еще один вариант для комментариев в формуле — функция LET. Но ее нет в Excel вплоть до версии 2019 😿 Комментарии — лишь повод про нее напомнить, так как функционал у нее шире. Она нужна в ситуациях, когда в формуле приходится использовать какой-то промежуточный результат много раз. Синтаксис функции: несколько пар аргументов, в которых вы задаете в первом аргументе переменную, а во втором — выражение для нее. В конце вычисление с использованием этих переменных. LET(имя1; значение_имени1; [имя2; значение_имени2]; …; вычисление) Давайте посмотрим на совсем простой пример — зададим две переменных a и b, присвоим им значения 50 и 10 и вычислим их произведение в последнем, единственном непарном, аргументе функции LET: =LET(a;50;b;10;a*b) На выходе будет 500. В выражениях для вычисления переменных можно использовать предыдущие переменные. В следующем случае мы вычисляем b как 10*a: =LET(a;50;b;10*a;a*b) На выходе будет 25000. Конечно, на практике для таких простых выражений функция LET не нужна. Но если у вас сложная формула, в которой одно и то же промежуточное выражение нужно вычислять несколько раз или вы хотите в итоговой формуле ссылаться на промежуточные шаги по имени для лучшей читаемости — LET поможет. Возвращаясь к нашей теме с "комментариями": можно задать переменную (с любым названием) и присвоить ей текстовое значение. =LET(переменная; "комментарий"; [другие переменные для вычислений]; ... ; вычисление).

Заменяем в сводной таблице пустые значения нулями По умолчанию в области значений в сводной нулевые значения будут пустыми (допустим, если по конкретному параметру или сочетанию параметров нет остатков / не было продаж и т.п.) Если хотите заменить пустые ячейки нулями: 1 Вкладка "Анализ сводной таблицы" (PivotTable Analyze) —> Параметры или правая кнопка мыши по сводной и в контекстном меню "Параметры сводной таблицы" (PivotTable Options). 2 На первой вкладке "Макет и формат" (Layout & Format) вводим значение 0 в поле "Для пустых ячеек отображать" (For empty cells show).

Добавляем комментарий к функции Немного экзотики. Функция с очень коротким названием N / Ч превращает ИСТИНА / TRUE в единицу, ЛОЖЬ / FALSE в ноль, числа оставляет как есть, текст превращает в ноль. Последним и можно воспользоваться, если очень хочется добавить в формулу текст без искажения результата. Например: =E2*15% + Ч("Вычисляем комиссию менеджера как 15% от суммы сделки") Первая часть (E2*15%) здесь — это вычисление комиссии, а вторая — текст внутри функции Ч, которая превратит его в ноль. Так что внутри функции текст есть, а к результату эта часть ничего не добавляет. Заодно кот Лемур напоминает: в формулах можно использовать переход на следующую строку для лучшей читаемости (Alt + Enter).

Если в диалоговом окне нет подчеркнутых букв в названии вкладок, все равно можно перемещаться с помощью сочетаний клавиш Ctrl + PgDn (к следующей) и Ctrl + PgUp (к предыдущей, налево). Движение идет по кругу. То есть если вы на первой вкладке, Ctrl + Page Up откроет последнюю.

У вас открыто диалоговое окно в Excel и есть несколько вкладок/разделов? Если в их названиях есть подчеркнутые буквы, то можно нажимать Alt в сочетании с этими буквами для перемещения без мышки. На скриншоте окно вставки гиперссылки (вызывается по сочетанию Ctrl + K).

Стиль (Cell Styles) — это готовый набор параметров форматирования ячейки, стилевого и/или числового. У стилей есть имена, их можно менять, удалять и создавать с нуля. Чем полезны стили? — Можно настроить совокупность параметров форматирования (числовой формат, выравнивание, заливка, шрифт, границы) и использовать в будущем для разных ячеек «в один клик». — Сам стиль можно поменять в любой момент (нажмите для этого в списке стилей правой кнопкой мыши на тот, что хотите настроить), и изменения будут применяться ко всем ячейкам с этим стилем (например, можно не переживать, что заголовки в документе будут разные — если применять к ним один стиль, то сможете регулировать внешний вид всех заголовков через настройку этого стиля). Стили существуют в рамках одной рабочей книги Excel. Можно забрать стили из другой открытой книги ("Объединить стили", Merge Styles).

Группировка нескольких текстовых элементов в сводной Допустим, в ваших исходных данных есть категория товара. Мониторы, ноутбуки и прочее, прочее. Вы хотите объединить несколько категорий в одну группу в сводной таблице, чтобы смотреть на общие данные (продажи, остатки...) по всем сразу. Для этого: 1 Выделяем несколько элементов (зажав клавишу Ctrl); 2 Щелкаем правой кнопкой и в контекстном меню выбираем Группировать / Group или 2 Нажимаем на ленте на вкладке "Анализ сводной таблицы" (PivotTable Analyze) — "Группировка по выделенному" (Group Selection) 3 Щелкаем на название группы (по умолчанию будет "Группа1") и переименовываем. Если хотите научиться всем основным заклинаниям в сводных таблицах, приходите на практикум в июне, который мы с Лемуром проведем в МИФе. Будет три очень интенсивных учебных дня с домашкой!

А еще в окне «Найти и заменить» (как и в случае с рядом других инструментов и функций Excel) можно использовать символы подстановки! * — любой текст, в том числе нулевой длины (то есть на месте звездочки может не быть ничего); ? — один любой символ (на месте знака вопроса обязательно должен быть символ). Например, если вам нужно найти/заменить/удалить любой текст в скобках (вместе с самими скобками), то в поле «Найти» нужно ввести: (*) А если нужно найти все скобки, в которых внутри слова строго из 4 букв (или 4 цифры или же 4 любых символа), нужно указать четыре знака вопроса в скобках: (????) Если вам нужно найти именно звездочки или знаки вопроса (например, чтобы удалить все звездочки в какой-то таблице), поставьте перед символом тильду (~). ~* — поиск звездочки, ~? — поиск знака вопроса, ~~ — поиск самой тильды.

Окно «Найти и заменить» (Find and Replace) во многих случаях помогает решить задачи по обработке текстовых значений (и не только) без применения сложных функций и формул. Это окно позволяет исправить большое количество формул, поменять форматирование всех однотипных ячеек, удалить определенные слова или символы из диапазона или из всей книги Excel. Его можно вызвать сочетаниями клавиш Ctrl + F (⌘ + F) или Ctrl + H (⌃ + H) — в обоих случаях откроется одно и то же диалоговое окно, но в первом случае на вкладке «Найти» (Find), а во втором — «Заменить» (Replace). Вот несколько нюансов: — Если вы предварительно выделили диапазон ячеек, то поиск/замена будут производиться в пределах этого диапазона. Если же нет — то на листе или в книге (изменить этот параметр можно в поле «Искать» (Within) в окне «Найти и заменить»; по умолчанию будет лист). — Если вы хотите что-то удалять, а не заменять, просто оставьте поле «Заменить на» пустым. Заменить на ничто = удалить, не так ли? — Можно производить изменения сразу с большим количеством формул. Например, вам нужно поменять диапазон или функцию во многих формулах. Выделите диапазон с формулами, вызовите окно «Найти и заменить» и введите в поле «Найти» тот фрагмент формул, который вы хотите изменить, а в «Заменить на» — то, на что хотите его изменить. Убедитесь, что в списке «Область поиска» (Look in) заданы «Формулы» (Formulas).