Табличка с примерами записи условий в функциях СУММЕСЛИМН / SUMIFS и других подобных функций.
Если вам нужно брать условие из ячейки и при этом добавлять к нему знаки сравнения, то приходится склеивать общее условие из двух частей:
— знаки сравнения, буду текстом, который "живет" в формуле, берутся в кавычки
— мы добавляем знак & (амперсанд), объединяющий текстовые строки в одну
— добавляем ссылку на ячейку.
Если вам нужно суммировать (усреднять, подсчитывать) данные за период, то условий будет два — на один и тот же столбец с датами. Одно — нижняя граница, второе — верхняя. Например, если в столбце B даты продаж, а нам нужны продажи за 2 квартал 2023, функция будет выглядеть так:
Если вам нужно брать условие из ячейки и при этом добавлять к нему знаки сравнения, то приходится склеивать общее условие из двух частей:
— знаки сравнения, буду текстом, который "живет" в формуле, берутся в кавычки
— мы добавляем знак & (амперсанд), объединяющий текстовые строки в одну
— добавляем ссылку на ячейку.
Если вам нужно суммировать (усреднять, подсчитывать) данные за период, то условий будет два — на один и тот же столбец с датами. Одно — нижняя граница, второе — верхняя. Например, если в столбце B даты продаж, а нам нужны продажи за 2 квартал 2023, функция будет выглядеть так:
=СУММЕСЛИМН(диапазон суммирования; B:B; ">=01.04.2023"; B:B; "<=30.06.2023")
В функциях СУММЕСЛИМН / SUMIFS и других для вычислений с условиями диапазоны могут быть и строками, а не столбцами.
Например, если нам нужно суммировать не все столбцы, а только те, в которых есть слово "количество" и год 2023 (то есть продажи в штуках, а не деньгах, и за 2023 год, а не другие) — диапазоном условий будет строка с заголовками. А диапазоном суммирования — текущая строка с числовыми данными.
Условие будет в нашем примере такое:
Не забудьте закрепить в такой ситуации строку с заголовками, сделав ее абсолютной (F4) — потому что при протягивании формулы вниз строка для суммирования будет меняться, и это необходимо, а вот заголовки для проверки условий всегда находятся в одной и той же строке.
Например, если нам нужно суммировать не все столбцы, а только те, в которых есть слово "количество" и год 2023 (то есть продажи в штуках, а не деньгах, и за 2023 год, а не другие) — диапазоном условий будет строка с заголовками. А диапазоном суммирования — текущая строка с числовыми данными.
Условие будет в нашем примере такое:
количество*2023У нас задано начало и окончание ячейки, а месяц между "количество" и годом может быть любой.
Не забудьте закрепить в такой ситуации строку с заголовками, сделав ее абсолютной (F4) — потому что при протягивании формулы вниз строка для суммирования будет меняться, и это необходимо, а вот заголовки для проверки условий всегда находятся в одной и той же строке.
Окно «Найти и заменить» (Find and Replace) во многих случаях помогает решить задачи по обработке текстовых значений (и не только) без применения сложных функций и формул. Это окно позволяет исправить большое количество формул, поменять форматирование всех однотипных ячеек, удалить определенные слова или символы из диапазона или из всей книги Excel.
Его можно вызвать сочетаниями клавиш Ctrl + F (⌘ + F) или Ctrl + H (⌃ + H) — в обоих случаях откроется одно и то же диалоговое окно, но в первом случае на вкладке «Найти» (Find), а во втором — «Заменить» (Replace).
Вот несколько нюансов:
— Если вы предварительно выделили диапазон ячеек, то поиск/замена будут производиться в пределах этого диапазона. Если же нет — то на листе или в книге (изменить этот параметр можно в поле «Искать» (Within) в окне «Найти и заменить»; по умолчанию будет лист).
— Если вы хотите что-то удалять, а не заменять, просто оставьте поле «Заменить на» пустым. Заменить на ничто = удалить, не так ли?
— Можно производить изменения сразу с большим количеством формул. Например, вам нужно поменять диапазон или функцию во многих формулах. Выделите диапазон с формулами, вызовите окно «Найти и заменить» и введите в поле «Найти» тот фрагмент формул, который вы хотите изменить, а в «Заменить на» — то, на что хотите его изменить. Убедитесь, что в списке «Область поиска» (Look in) заданы «Формулы» (Formulas).
Его можно вызвать сочетаниями клавиш Ctrl + F (⌘ + F) или Ctrl + H (⌃ + H) — в обоих случаях откроется одно и то же диалоговое окно, но в первом случае на вкладке «Найти» (Find), а во втором — «Заменить» (Replace).
Вот несколько нюансов:
— Если вы предварительно выделили диапазон ячеек, то поиск/замена будут производиться в пределах этого диапазона. Если же нет — то на листе или в книге (изменить этот параметр можно в поле «Искать» (Within) в окне «Найти и заменить»; по умолчанию будет лист).
— Если вы хотите что-то удалять, а не заменять, просто оставьте поле «Заменить на» пустым. Заменить на ничто = удалить, не так ли?
— Можно производить изменения сразу с большим количеством формул. Например, вам нужно поменять диапазон или функцию во многих формулах. Выделите диапазон с формулами, вызовите окно «Найти и заменить» и введите в поле «Найти» тот фрагмент формул, который вы хотите изменить, а в «Заменить на» — то, на что хотите его изменить. Убедитесь, что в списке «Область поиска» (Look in) заданы «Формулы» (Formulas).
А еще в окне «Найти и заменить» (как и в случае с рядом других инструментов и функций Excel) можно использовать символы подстановки!
* — любой текст, в том числе нулевой длины (то есть на месте звездочки может не быть ничего);
? — один любой символ (на месте знака вопроса обязательно должен быть символ).
Например, если вам нужно найти/заменить/удалить любой текст в скобках (вместе с самими скобками), то в поле «Найти» нужно ввести:
* — любой текст, в том числе нулевой длины (то есть на месте звездочки может не быть ничего);
? — один любой символ (на месте знака вопроса обязательно должен быть символ).
Например, если вам нужно найти/заменить/удалить любой текст в скобках (вместе с самими скобками), то в поле «Найти» нужно ввести:
(*)А если нужно найти все скобки, в которых внутри слова строго из 4 букв (или 4 цифры или же 4 любых символа), нужно указать четыре знака вопроса в скобках:
(????)Если вам нужно найти именно звездочки или знаки вопроса (например, чтобы удалить все звездочки в какой-то таблице), поставьте перед символом тильду (~).
~* — поиск звездочки,~? — поиск знака вопроса,~~ — поиск самой тильды.This media is not supported in your browser
VIEW IN TELEGRAM
Группировка нескольких текстовых элементов в сводной
Допустим, в ваших исходных данных есть категория товара. Мониторы, ноутбуки и прочее, прочее.
Вы хотите объединить несколько категорий в одну группу в сводной таблице, чтобы смотреть на общие данные (продажи, остатки...) по всем сразу.
Для этого:
1 Выделяем несколько элементов (зажав клавишу Ctrl);
2 Щелкаем правой кнопкой и в контекстном меню выбираем Группировать / Group
или
2 Нажимаем на ленте на вкладке "Анализ сводной таблицы" (PivotTable Analyze) — "Группировка по выделенному" (Group Selection)
3 Щелкаем на название группы (по умолчанию будет "Группа1") и переименовываем.
Если хотите научиться всем основным заклинаниям в сводных таблицах, приходите на практикум в июне, который мы с Лемуром проведем в МИФе. Будет три очень интенсивных учебных дня с домашкой!
Допустим, в ваших исходных данных есть категория товара. Мониторы, ноутбуки и прочее, прочее.
Вы хотите объединить несколько категорий в одну группу в сводной таблице, чтобы смотреть на общие данные (продажи, остатки...) по всем сразу.
Для этого:
1 Выделяем несколько элементов (зажав клавишу Ctrl);
2 Щелкаем правой кнопкой и в контекстном меню выбираем Группировать / Group
или
2 Нажимаем на ленте на вкладке "Анализ сводной таблицы" (PivotTable Analyze) — "Группировка по выделенному" (Group Selection)
3 Щелкаем на название группы (по умолчанию будет "Группа1") и переименовываем.
Если хотите научиться всем основным заклинаниям в сводных таблицах, приходите на практикум в июне, который мы с Лемуром проведем в МИФе. Будет три очень интенсивных учебных дня с домашкой!
Стиль (Cell Styles) — это готовый набор параметров форматирования ячейки, стилевого и/или числового. У стилей есть имена, их можно менять, удалять и создавать с нуля.
Чем полезны стили?
— Можно настроить совокупность параметров форматирования (числовой формат, выравнивание, заливка, шрифт, границы) и использовать в будущем для разных ячеек «в один клик».
— Сам стиль можно поменять в любой момент (нажмите для этого в списке стилей правой кнопкой мыши на тот, что хотите настроить), и изменения будут применяться ко всем ячейкам с этим стилем (например, можно не переживать, что заголовки в документе будут разные — если применять к ним один стиль, то сможете регулировать внешний вид всех заголовков через настройку этого стиля).
Стили существуют в рамках одной рабочей книги Excel. Можно забрать стили из другой открытой книги ("Объединить стили", Merge Styles).
Чем полезны стили?
— Можно настроить совокупность параметров форматирования (числовой формат, выравнивание, заливка, шрифт, границы) и использовать в будущем для разных ячеек «в один клик».
— Сам стиль можно поменять в любой момент (нажмите для этого в списке стилей правой кнопкой мыши на тот, что хотите настроить), и изменения будут применяться ко всем ячейкам с этим стилем (например, можно не переживать, что заголовки в документе будут разные — если применять к ним один стиль, то сможете регулировать внешний вид всех заголовков через настройку этого стиля).
Стили существуют в рамках одной рабочей книги Excel. Можно забрать стили из другой открытой книги ("Объединить стили", Merge Styles).
Добавляем комментарий к функции
Немного экзотики. Функция с очень коротким названием N / Ч превращает ИСТИНА / TRUE в единицу, ЛОЖЬ / FALSE в ноль, числа оставляет как есть, текст превращает в ноль.
Последним и можно воспользоваться, если очень хочется добавить в формулу текст без искажения результата.
Например:
Заодно кот Лемур напоминает: в формулах можно использовать переход на следующую строку для лучшей читаемости (Alt + Enter).
Немного экзотики. Функция с очень коротким названием N / Ч превращает ИСТИНА / TRUE в единицу, ЛОЖЬ / FALSE в ноль, числа оставляет как есть, текст превращает в ноль.
Последним и можно воспользоваться, если очень хочется добавить в формулу текст без искажения результата.
Например:
=E2*15% + Ч("Вычисляем комиссию менеджера как 15% от суммы сделки")
Первая часть (E2*15%) здесь — это вычисление комиссии, а вторая — текст внутри функции Ч, которая превратит его в ноль. Так что внутри функции текст есть, а к результату эта часть ничего не добавляет.Заодно кот Лемур напоминает: в формулах можно использовать переход на следующую строку для лучшей читаемости (Alt + Enter).
Заменяем в сводной таблице пустые значения нулями
По умолчанию в области значений в сводной нулевые значения будут пустыми (допустим, если по конкретному параметру или сочетанию параметров нет остатков / не было продаж и т.п.)
Если хотите заменить пустые ячейки нулями:
1 Вкладка "Анализ сводной таблицы" (PivotTable Analyze) —> Параметры или правая кнопка мыши по сводной и в контекстном меню "Параметры сводной таблицы" (PivotTable Options).
2 На первой вкладке "Макет и формат" (Layout & Format) вводим значение 0 в поле "Для пустых ячеек отображать" (For empty cells show).
По умолчанию в области значений в сводной нулевые значения будут пустыми (допустим, если по конкретному параметру или сочетанию параметров нет остатков / не было продаж и т.п.)
Если хотите заменить пустые ячейки нулями:
1 Вкладка "Анализ сводной таблицы" (PivotTable Analyze) —> Параметры или правая кнопка мыши по сводной и в контекстном меню "Параметры сводной таблицы" (PivotTable Options).
2 На первой вкладке "Макет и формат" (Layout & Format) вводим значение 0 в поле "Для пустых ячеек отображать" (For empty cells show).
Еще один вариант для комментариев в формуле — функция LET. Но ее нет в Excel вплоть до версии 2019 😿
Комментарии — лишь повод про нее напомнить, так как функционал у нее шире.
Она нужна в ситуациях, когда в формуле приходится использовать какой-то промежуточный результат много раз.
Синтаксис функции: несколько пар аргументов, в которых вы задаете в первом аргументе переменную, а во втором — выражение для нее. В конце вычисление с использованием этих переменных.
В выражениях для вычисления переменных можно использовать предыдущие переменные. В следующем случае мы вычисляем b как 10*a:
Конечно, на практике для таких простых выражений функция LET не нужна. Но если у вас сложная формула, в которой одно и то же промежуточное выражение нужно вычислять несколько раз или вы хотите в итоговой формуле ссылаться на промежуточные шаги по имени для лучшей читаемости — LET поможет.
Возвращаясь к нашей теме с "комментариями": можно задать переменную (с любым названием) и присвоить ей текстовое значение.
Комментарии — лишь повод про нее напомнить, так как функционал у нее шире.
Она нужна в ситуациях, когда в формуле приходится использовать какой-то промежуточный результат много раз.
Синтаксис функции: несколько пар аргументов, в которых вы задаете в первом аргументе переменную, а во втором — выражение для нее. В конце вычисление с использованием этих переменных.
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(переменная; "комментарий"; [другие переменные для вычислений]; ... ; вычисление).Хотите, чтобы сводная таблица обновлялась автоматически при открытии файла? Например, если вы отправляете книгу Excel с отчетом коллегам и не уверены, что все обновят сводную сами.
1 Правая кнопка по сводной — "Параметры сводной таблицы" (PivotTable Options)
2 Переходим на вкладку "Данные" (Data)
3 Включаем флажок "Обновить при открытии файла" (Refresh Data When Opening The File)
1 Правая кнопка по сводной — "Параметры сводной таблицы" (PivotTable Options)
2 Переходим на вкладку "Данные" (Data)
3 Включаем флажок "Обновить при открытии файла" (Refresh Data When Opening The File)
Один из множества слайдов, которые мы с котом Лемуром готовим к июньскому практикуму по сводным таблицам (самому мощному инструменту для анализа данных).
Правда, на встречах слушатели этих слайдов не увидят. Вот еще — время тратить на презентации на уроках :)
Все время (3 по 2 часа) проведем в Excel (ну и малость в Google Таблицах), а слайды — это как мини-методичка для участников, чтобы потом освежить в памяти знания.
Еще будут домашки, их разбор (и подарки чемпионам), файлы-примеры до и после, ответы на вопросы.
Приходите, встречи будут 14, 20 и 23 июня (а если есть вопросы по формату обучения — спрашивайте в ЛС или комментариях):
https://www.mann-ivanov-ferber.ru/courses/practicum-excel/
Правда, на встречах слушатели этих слайдов не увидят. Вот еще — время тратить на презентации на уроках :)
Все время (3 по 2 часа) проведем в Excel (ну и малость в Google Таблицах), а слайды — это как мини-методичка для участников, чтобы потом освежить в памяти знания.
Еще будут домашки, их разбор (и подарки чемпионам), файлы-примеры до и после, ответы на вопросы.
Приходите, встречи будут 14, 20 и 23 июня (а если есть вопросы по формату обучения — спрашивайте в ЛС или комментариях):
https://www.mann-ivanov-ferber.ru/courses/practicum-excel/
Серая тема для черно-белой печати диаграмм и других объектов в Excel
При подготовке книги к печати столкнулись с типовой проблемой: когда вы печатаете цветные диаграммы, все может сливаться, если печать не цветная.
Слева сверху вы видите диаграмму, как она выглядела в Excel изначально (и как будет выглядеть этот фрагмент в электронной — цветной — книге), справа — то, что получается при ч/б печати. Как видите, совсем грустно. Гистограмма с накоплением выглядит как столбики одного цвета, словно и нет у нас двух товарных категорий.
Поэтому лучше использовать специальную серую цветовую схему, в которой будут контрастные серые оттенки и будут видны отличия.
Итак, если вы планируете печатать в ч/б ваш отчет:
Вкладка "Разметка страницы" — группа "Темы" — Цвета — Серая
Page Layout — Themes — Colors — Grayscale
При подготовке книги к печати столкнулись с типовой проблемой: когда вы печатаете цветные диаграммы, все может сливаться, если печать не цветная.
Слева сверху вы видите диаграмму, как она выглядела в Excel изначально (и как будет выглядеть этот фрагмент в электронной — цветной — книге), справа — то, что получается при ч/б печати. Как видите, совсем грустно. Гистограмма с накоплением выглядит как столбики одного цвета, словно и нет у нас двух товарных категорий.
Поэтому лучше использовать специальную серую цветовую схему, в которой будут контрастные серые оттенки и будут видны отличия.
Итак, если вы планируете печатать в ч/б ваш отчет:
Вкладка "Разметка страницы" — группа "Темы" — Цвета — Серая
Page Layout — Themes — Colors — Grayscale
Если мы склеиваем текстовые значения и числа формулой, например, к фиксированному тексту "Средние продажи:" добавляем функцию СРЗНАЧ/AVERAGE, которая будет возвращать среднее значение:
Формат ячейки с формулой менять бесполезно: в ней уже хранится текст (потому что на выходе в результате работы операции "склеивания", конкатенации, которая происходит благодаря символу &, мы получаем именно текстовое значение). И отформатировать числовой кусочек текста отдельно через формат ячейки нельзя.
Выход — использовать функцию ТЕКСТ / TEXT. Она может любое значение (в том числе число, взятое из ячейки или полученное вычислением) отформатировать по заданному числовому формату и возвращает результат в виде текста.
Синтаксис:
Так что в нашем случае, если мы хотим, чтобы все средние значения были с одним знаком после запятой, понадобится следующая формула:
="Средние продажи: "&СРЗНАЧ(C2:C13)То форматирование чисел "потеряется" и будет не как в исходных ячейках, а без всяких атрибутов форматирования. Не будет, например, разделителей групп разрядов, а после запятой будут отображаться все цифры.
Формат ячейки с формулой менять бесполезно: в ней уже хранится текст (потому что на выходе в результате работы операции "склеивания", конкатенации, которая происходит благодаря символу &, мы получаем именно текстовое значение). И отформатировать числовой кусочек текста отдельно через формат ячейки нельзя.
Выход — использовать функцию ТЕКСТ / TEXT. Она может любое значение (в том числе число, взятое из ячейки или полученное вычислением) отформатировать по заданному числовому формату и возвращает результат в виде текста.
Синтаксис:
=ТЕКСТ(число; формат).Формат указывается как в пользовательских форматах (подробнее о них см видео).
Так что в нашем случае, если мы хотим, чтобы все средние значения были с одним знаком после запятой, понадобится следующая формула:
="Средние продажи: "&ТЕКСТ(СРЗНАЧ(B2:B13);"0,0")
У вас есть список в несколько столбцов, а вам нужно сделать его одномерным?
Например, как на скриншоте — есть выступающие в нескольких залах (каждый зал в своем столбце), а нужно получить один список всех выступающих.
Тогда используем функцию TOCOL / ПОСТОЛБЦ (увы, недоступную в старых версиях Excel).
Чтобы сделать список без повторов (то есть если человек выступает в разное время или разные дни, мы все равно упоминаем его один раз) и отсортировать, можно добавить функции УНИК / UNIQUE (только уникальные значения) и СОРТ / SORT (сортировка).
Например, как на скриншоте — есть выступающие в нескольких залах (каждый зал в своем столбце), а нужно получить один список всех выступающих.
Тогда используем функцию TOCOL / ПОСТОЛБЦ (увы, недоступную в старых версиях Excel).
Чтобы сделать список без повторов (то есть если человек выступает в разное время или разные дни, мы все равно упоминаем его один раз) и отсортировать, можно добавить функции УНИК / UNIQUE (только уникальные значения) и СОРТ / SORT (сортировка).
Пользуетесь окном поиска в Excel?
Если нет, уберите его в параметрах:
Файл — Параметры — Общие — Свернуть поле Поиска
File — Options — General — Collapse the Microsoft Search Box
В любом случае справа у вас останется иконка лупы для раскрытия этого поля. Но зато оно не будет все время занимать часть панели наверху.
Если нет, уберите его в параметрах:
Файл — Параметры — Общие — Свернуть поле Поиска
File — Options — General — Collapse the Microsoft Search Box
В любом случае справа у вас останется иконка лупы для раскрытия этого поля. Но зато оно не будет все время занимать часть панели наверху.
Схватка двух ёкодзун: сравнение Excel и Google Spreadsheets.
Друзья, в этой (регулярно обновляемой) табличке — подробное сравнение Excel (не Online, а обычной версии) и Google Таблиц: какие функции есть только в одном из редакторов, что ломается и что сохраняется при переносе файлов, какие есть нюансы в сводных, пользовательских форматах и других темах.
По некоторым темам и функциями есть ссылки на дополнительные материалы (видео, посты).
https://docs.google.com/spreadsheets/d/10jhUBPLhmtx-km5sdITumlmkgIn8DKqsdxyULChVaq8
Друзья, в этой (регулярно обновляемой) табличке — подробное сравнение Excel (не Online, а обычной версии) и Google Таблиц: какие функции есть только в одном из редакторов, что ломается и что сохраняется при переносе файлов, какие есть нюансы в сводных, пользовательских форматах и других темах.
По некоторым темам и функциями есть ссылки на дополнительные материалы (видео, посты).
https://docs.google.com/spreadsheets/d/10jhUBPLhmtx-km5sdITumlmkgIn8DKqsdxyULChVaq8
Google Docs
Сравнение Excel и Google Таблиц [https://t.me/google_sheets]
This media is not supported in your browser
VIEW IN TELEGRAM
Фильтр по выделенному
Итак, вы хотите фильтровать данные по значению выделенной ячейки. Для этого можно добавить кнопку на панель быстрого доступа. Называется она "Автофильтр", но это именно фильтр по выделенному значению.
Параметры Excel — Настроить панель быстрого доступа (панель инструментов — все команды или команды не на ленте — Автофильтр — Добавить
Customize Quick Access Toolbar — Commands Not in The Ribbon — AutoFilter — Add
Теперь ставим фильтр в диапазоне (если его еще не было), выделяем ячейку, по которой нужно отфильтровать, и нажимаем на добавленную на панель быстрого доступа кнопку!
А еще кот Лемур напоминает: команды на панели можно вызывать сочетанием Alt (или косой черты или F10) и цифры (соответствующей расположению нужной команды по порядку). Так что у нас с вами теперь есть сочетание клавиш для фильтрации по выбранному значению!
Итак, вы хотите фильтровать данные по значению выделенной ячейки. Для этого можно добавить кнопку на панель быстрого доступа. Называется она "Автофильтр", но это именно фильтр по выделенному значению.
Параметры Excel — Настроить панель быстрого доступа (панель инструментов — все команды или команды не на ленте — Автофильтр — Добавить
Customize Quick Access Toolbar — Commands Not in The Ribbon — AutoFilter — Add
Теперь ставим фильтр в диапазоне (если его еще не было), выделяем ячейку, по которой нужно отфильтровать, и нажимаем на добавленную на панель быстрого доступа кнопку!
А еще кот Лемур напоминает: команды на панели можно вызывать сочетанием Alt (или косой черты или F10) и цифры (соответствующей расположению нужной команды по порядку). Так что у нас с вами теперь есть сочетание клавиш для фильтрации по выбранному значению!