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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Инструмент "Мгновенное заполнение" в Excel есть уже достаточно давно (с 2013 версии). И спектр задач, которые он может помочь решить, достаточно широк. Вот одна из них.

Есть список сотрудников в формате ФИО. Нужно изменить порядок так, чтобы фамилия была в конце. Задача несложная. Можно разбить данные на столбцы с помощью "Текст по столбцам", а потом пересобрать формулой в нужном порядке. Но с мгновенным заполнением это делается ещё быстрее. Вводим пару примеров результата - и Excel угадывает, что мы пытаемся сделать.

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

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

То есть, формула =SIN(45) посчитает не синус 45 градусов, а синус 45 радиан. А это совсем не одно и то же. Чтобы избежать ошибок в расчетах, градусные меры нужно переводить в радианы соответствующей функцией. Правильный вариант для 45 градусов: =SIN(РАДИАНЫ(45))

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

Обычно мы нумеруем недели внутри каждого года. Но иногда требуется выполнить возобновляемую нумерацию внутри каждого нового месяца. То есть с 1-ого числа по 7-ое число - первая неделя, с 8-ого по 14-ое - вторая неделя и т.д.

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

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

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

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

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

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

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

#УР1 #Имена

В Excel 2013 появилась небольшая, но полезная функция - ЕФОРМУЛА. Она позволяет проверить, что находится в ячейке: формула или какая-то константа. Если формула, то функция вернёт ИСТИНА, иначе - ЛОЖЬ.

Это может пригодится, например, в условном форматировании. Часто бывает что в большом массиве формул вместо одной из них оказывается введено значение и все расчеты дают ошибку. ЕФОРМУЛА позволит легко подсветить такие ячейки и сразу же обнаружить ошибку.

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

В одном из уроков мы уже рассказывали о применении функции КОНМЕСЯЦА. Тогда мы рассчитывали количество дней в месяце, к которому относится указанная дата.

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

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

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

С её помощью можно заблокировать любые изменения, связанные с листами файла: их изменение, удаление, скрытие/открытие, переимнование и даже просто перемещение ярлычков относительно друг друга. Можно защищать как с использованием пароля, так и без него.

#УР2 #Особенности_совместной_работы

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

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

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

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

Чтобы создать ряд десятичных чисел с нужной точностью, можно использовать формулу, которая на ходу будет округлять значение. Или использовать инструмент Прогрессия для создания арифметической прогрессии. Тоже очень удобно.

#УР1 #Простые_Вычисления

У нас на канале уже был урок про то, как получить из даты название месяца. Но что, если имеется только номер месяца (от 1 до 12)? И нужно по этому номеру получить название.

Вариантов решения есть множество. ВПР с небольшой справочной табличкой, функция ВЫБОР и т.д. Показываем пару вариантов на основе функции ТЕКСТ. Вся суть в том, чтобы из номера месяца получить любую дату этого месяца. А уже из нее ТЕКСТ легко извлечет название.

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

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

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

Получается наглядная даиграмма а-ля условное форматирование.

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