В сводных таблицах мы часто используем промежуточные итоги, которые создаются автоматически. Но мы не ограничены только этим вариантом. Можно переключать расчет промежуточных итогов и делать его отличающимся от основного расчета.

А можно даже включить в сводную несколько промежуточных итогов. Например, считать сумму, количество и среднее по каждой категории (для выбора нескольких расчётов достаточно зажать Ctrl в окне "Параметры поля").

#УР2 #Сводные_таблицы

На клавиатуре есть такая не очень популярная клавиша как Scroll Lock. Многие пользователи вообще никогда её не нажимают. Но с этой клавишей связана одна интересная особенность работы Excel.

Если клавиша была нажата и Scroll Lock активирован (горит его индикатор), то стрелки клавиатуры при нажатии будут не перемещать курсор по ячейкам, а прокручивать лист в разных направлениях. Если заметили такое поведение программы - просто нажмите Scroll Lock, чтобы отключить его и вернуть нормальный режим.

#Справка

Часто приходится слышать вопрос: "Как создать выпадающий список сразу для целой строки, а не для отдельного столбца в строке?". Обычно это нужно для заполнения сразу нескольких столбцов данными при выборе значения из списка. К сожалению, сам выпадающий список такой опции не предусматривает. Но проблема легко решается формулами.

Реализуется такой вариант функцией ВПР или связкой ИНДЕКС+ПОИСКПОЗ. Одно значение, которое служит ключом (является уникальным в справочнике), мы выбираем из списка, а все остальные данные просто подтягиваем в таблицу из справочника по выбранному ключу.

#УР2 #Проверка_данных

Одним из вариантов объединения ячеек является объединение по строкам. Это означает, что в выделенном диапазоне из нескольких строк и столбцов будут объединены не все ячейки в одну большую, а только ячейки в пределах каждой строки. То есть количество строк останется неизменным, а вот столбец будет один. Иногда это бывает очень полезно (например, при создании бланков в Excel).

#УР1 #Оформление_таблиц

В пользовательских правилах условного форматирования можно использовать формулы массива. Это даёт еще больше возможностей для создания кастомизированных правил.

Например, можно создать правило, которое будет выделять 3 строки с наибольшими продажами в таблице, но не по всем товарам, а только по тому, который выбран в выпадающем списке. При этом формула массива в окне правила условного форматирования не требует ввода тремя клавишами. Достаточно просто написать или вставить ее туда.

#УР2 #Условное_форматирование

При работе со значениями даты и времени часто необходимо извлечь в отдельную ячейку только дату, а в другую - только время. Делается это очень просто, если помнить о том, что дата - это целое число, а время - дробная часть целого числа.

Тогда задача сводится к извлечению целой и дробной части по отдельности. Первую можно достать функцией ЦЕЛОЕ, а вторую можно выразить как остаток от деления числа на единицу (остатком в таком случае будет дробная часть). Формулы очень простые и короткие.

#УР2 #Примеры_формул

Для того, чтобы расположить строки или столбцы таблицы в обратном порядке, можно воспользоваться нехитрым приёмом.

Добавляем дополнительный столбец (если меняем сортировку строк) или дополнительную строку (если меняем сортировку столбцов). Заполняем их числами от 1 по возрастанию. А затем просто сортируем всю таблицу по добавленной строке/столбцу в порядке убывания. Для сортировке по строке, правда, придётся использовать команду "Настраиваемая сортировка", но ничего сложного в этом нет.

#УР1 #Обработка_таблиц

Маркер автозаполнения замечательно работает и с датами. Помимо стандартного увеличения на единицу и возможности задать шаг, введя первые два значения, предлагается еще несколько опций.

Если потянуть за маркер автозаполнения правой кнопкой мыши, то откроется контекстное меню, в котором можно выбрать такие варианты как:
- заполнение только рабочими днями
- заполнение с шагом в 1 месяц
- заполнение с шагом в 1 год

#УР1 #Работа_с_листами_книги

В сводной таблице есть возможность вручную группировать значения в строках или столбцах. Достаточно выделить нужные значения (используя CTRL или SHIFT), кликнуть на них правой кнопкой мыши и выбрать команду "Группировать". Останется только поменять название созданной группы.

Для многоуровневых группировок следует соблюдать порядок создания. Сначала создаюстя группы самого глубокого уровня вложенности. Затем - родительские для них и т.д. Использование группировок позволяет мгновенно увидеть в сводной таблице промежуточные итоги по созданным группам.

#УР2 #Сводные_таблицы

При построении диаграммы, в которой по оси расположены даты, есть небольшой нюанс. Если в исходных данных пропущены некоторые даты, то и на диаграмме для этих дат, скорее всего, будут пропуски.

Иногда именно это и нужно и такие пропуски дают понять, что в какие то дни показатель проседал. А иногда вообще не стоит учитывать пропущенные дни, и на графике хочется видеть только те, которые есть в данных. В таких случаях следует переключить тип оси диаграммы на "Ось текста". Она игнорирует пропуски в датах, а также их повторы. А вот "Ось дат" добавляет отсутствующие дни как пустые точки.

#УР3 #Диаграммы

Недавно у нас спросили, как можно быстро "подтянуть" данные к верхнему краю таблицы, если в столбцах имеются пустые ячейки. Быстро сделать подобную трансформацию поможет обычное удаление со сдвигом вверх в сочетании с командой "Выделить группу ячеек".

Сначала выбираем в диапазоне только пустые ячейки, а затем удаляем их, указав нужную опцию сдвига. Быстро и просто.

#УР1 #Обработка_таблиц

Если по какой-то причине имеющаяся "умная" таблица Вам больше не нужна (как объект), а вот информацию в ней надо оставить - используйте команду "Преобразовать в диапазон" на контекстной вкладке "Конструктор". Она сохранит всё форматирование и все данные, но таблица больше не будет специальным объектом, а станет обычным диапазоном ячеек.

Если же форматирование тоже не нужно, можно предварительно применить стиль без оформления, а потом - указанную выше команду.

#УР1 #Оформление_таблиц

Маленький, но полезный приём. Клавиша F4 позволяет повторить последнее выполненное действие. Например, если мы вставили куда-то строку со сдвигом данных вниз, то можно продолжать выбирать нужные ячейки и повторять вставку простым нажатием F4.

#Горячие_клавиши

Чтобы вставить в таблицу с датами колонку с названиями месяцев, используйте функцию ТЕКСТ, указав формат "ММММ". Самый быстрый и изящный способ (без доп.таблиц и ВПРов)

#УР2 #Применение_встроенных_функций

Если Вы постоянно работаете со сводными таблицами, то наверняка уже давно освоили и успешно используете удобнейший элемент фильтрации - срезы. Для сводных они доступны, начиная с Excel 2010.

А вот уже в 2013-ой версии появилась очень классная возможность использовать срезы для фильтрации "Умных таблиц". Все работает точно так же, как и в сводных. Очень просто и удобно, обязательно берите на вооружение.

Ну а что касается "Временных шкал", которые появились для сводных в Excel 2013, то они и по сей день остаются недоступными для "умных таблиц".

#УР1 #Фильтрация_и_сортировка

Встроенных вариантов условного форматирования достаточно много. Если работаете с датами, то можете легко и быстро выделить цветом даты прошлой недели, текущего месяца, последних 7 дней и т.д.

Разумеется, для корректной работы правил нужно, чтобы в диапазоне были действительно правильно введенные даты, а не, например, текстовые значения.

#УР2 #Условное_форматирование

Несмотря на наличие нескольких встроенных опций выделения диапазонов дат условным форматированием, иногда требуется составлять собственные правила на основе формул.

Например, если нужно выделить даты, которые приходились на последние 10 дней или попадут в ближайшие 15 дней. Хорошая новость в том, что формулы для этих задач совсем не сложные и написать их - дело пары минут.

#УР2 #Условное_форматирование

Некоторые стандартные шрифты предоставляют весьма нестандартные символы. Например, шрифт Wingdings позволяет вставлять на лист разные миниатюрные значки. Это можно использовать в формулах как альтернативу условному форматированию или в сочетании с ним.

Например, можно создать формулу, которая будет сравнивать количество "чего-то" в соседних столбцах и показывать направление, в котором нужно переместить это "что-то" для выравнивания значений. А если значения равны ( с точностью до единицы) , будет отображаться галочка.

#УР2 #Условное_форматирование

Недавно показывали как с помощью собственного правила условного форматирования выделить даты, которые попали в диапазон из N последних или ближайших дней. Если диапазон нужно считать не в календарных днях, а в рабочих - то задача решается немного по-другому (но по-прежнему весьма просто).

Достаточно использовать в формуле функцию ЧИСТРАБДНИ для подсчета количества рабочих дней. Если у вас есть список праздничных дат, то можете разместить их в диапазон ячеек и указать третьим аргументом функции. Тогда эти дни не будут считаться рабочими при расчете.

#УР2 #Условное_форматирование

При поиске минимального значения по условию с помощью формул МИНЕСЛИ или формулы массива МИН(ЕСЛИ…)) есть один тонкий момент. Если условие не будет выполнено ни разу, то формула будет возвращать 0. И будет невозможно понять, что это за ноль - это реально минимальное значение по условию или же условие не выполнено ни разу.

Чтобы при невыполнении условия получать вместо нуля значение ошибки - используйте НАИМЕНЬШИЙ вместо МИН. Полученную ошибку можно потом преобразовать во что-то более подходящее функцией ЕСЛИОШИБКА.

#УР2 #Применение_встроенных_функций

Вопросы про создание простых выпадающих (раскрывающихся) списков всё чаще попадаются в нашем боте. На самом деле всё очень просто. Чаще всего для такой задачи используется инструмент "Проверка данных".

Список можно создать из значений, хранящихся в диапазоне ячеек. Или ввести вручную, разделив значения точкой с запятой (или просто запятой - это зависит от ваших региональных настроек).

Помните о том, что вставка в такую ячейку данных, скопированных откуда-то, приведёт к удалению выпадающего списка (кроме случая, когда вы выбираете "Вставить как значение").

#УР2 #Проверка_данных