Cоздание уникального списка

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

С помощью запроса Power Query:
На вкладке Данные выбираем Из таблицы. Список форматируется в «умную» таблицу и загружается в редактор Power Query. Затем уже в окне редактора нажимаем: вкладка Главная страница → Удалить строки → Удалить дубликаты. Чтобы выгрузить обратно в документ, используем кнопку Закрыть и загрузить.

👉 @Excel_lifehack

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

На самом деле основных мест, где собраны настройки - всего 3. Можно найти в одном из этих мест.

Плюс не забывайте о разных режимах просмотра книги.

👉 @Excel_lifehack

​​Приёмы работы со сложными формулами

2) Если вы не уверены, что сможете корректно прописать аргументы функции, пользуйтесь окном "Мастер функций"

Эта команда расположена во вкладке Формулы слева. При нажатии на Вставить функцию открывается поисковик функций.

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

👉 @Excel_lifehack

Использовать именованные диапазоны очень удобно. Если Вы создали удобные имена, но уже написаны формулы, в которых указаны просто ссылки на эти диапазоны, то заменить их на новые имена очень просто.

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

👉 @Excel_lifehack

​​Приёмы работы со сложными формулами

3) Чтобы разобраться в сложной формуле, используйте прием "вычисления фрагмента формулы"

Выделите лишь фрагмент сложной формулы и нажмите клавишу F9 — этот фрагмент рассчитается и примет свое итоговое значение прямо в изначальной формуле.
После этого нажмите CTRL + Z, чтобы отменить этот частичный расчет.

Пример использования:
Есть таблица с объемом продаж менеджеров, в которой с помощью функций ИНДЕКС и ПОИСКПОЗ мы ищем объем продаж определенного менеджера.
Выделяем фрагмент формулы с использованием функции ПОИСКПОЗ, затем нажимаем F9 — после чего формула примет значение 5.
Теперь изначальная формула стала намного понятнее, ведь в ней визуально используется только 1 функция.

👉 @Excel_lifehack

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

Сделать это легко. Нужно при задании заливки столбца указать опцию "Инверсия для чисел меньше 0" и выбрать разные цвета для положительных и для отрицательных точек данных.

👉 @Excel_lifehack

​​Приёмы работы со сложными формулами

4) Если формула слишком длинная, разбейте ее на составные части в строке формул

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

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

👉 @Excel_lifehack

Функция СУММЕСЛИ очень хороша, но кое-чего не умеет. Например, подсчитать сумму значений не по полному номеру индекса, а по трем первым символам.

А вот СУММПРОИЗВ такую задачу решает на раз-два. Понадобится просто извлечь 3 символа из ячейки и сравнить с нашим условием.

👉 @Excel_lifehack

Как скрыть формулу в ячейке?

Для этого выделяем нужную ячейку, нажимаем правой кнопкой мыши и выбираем пункт Формат ячеек.
Затем в всплывающем диалоговом окне во вкладке Защита проставляем галочку возле Скрыть формулы и жмем ОК.

Чтобы изменения вступили в силу, необходимо также установить защиту на сам лист (вкладка Рецензирование, группа Изменения, кнопка Защитить лист).

👉 @Excel_lifehack

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

А вот среднее, макс и т.д. - это будут формулы массива (вводятся сочетанием Ctrl+Shift+Enter). Здесь уже СУММПРОИЗВ не понадобится.

👉 @Excel_lifehack

Быстрое введение текущего времени

Для этого используется следующая комбинация — CTRL + : (или CTRL + SHIFT + 6) .

👉 @Excel_lifehack

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

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

👉 @Excel_lifehack

​​Блокирование изменений задним числом

Чтобы установить запрет на изменение данных, можно воспользоваться следующим алгоритмом:

— Шаг 1
Наводим курсор на нужную ячейку, затем в меню выбираем пункт Данные и в этом разделе жмём кнопку Проверка данных.

— Шаг 2
В выпадающем списке Тип данных выбираем Другой и убираем галочку с Игнорировать пустые ячейки.

Затем в графе Формула указываем неизменяемую ячейку и ее неизменяемое значение.
В нашем примере, чтобы запретить изменять срок кредита, необходимо ввести: =B4=4

После этого нажимаем кнопку ОК — теперь, если кто-то захочет ввести другой срок, появится предупреждающая надпись.

👉 @Excel_lifehack

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

Затем нужно нажимая F6 переместить область управления на открывшуюся панель. После чего можно задавать настройки с клавиатуры.

👉 @Excel_lifehack

​​Запрет на ввод дублей

— Шаг 1
Выделяем диапазон ячеек, на которые будет распространяться запрет.
Во вкладке Данные нажимаем кнопку Проверка данных.

— Шаг 2
Во вкладке Параметры из выпадающего списка Тип данных выбираем вариант Другой.
В графе Формула пишем =СЧЁТЕСЛИ($A$1:$A$10;A1)<=1 (для диапазона из 10 значений)

— Шаг 3
В этом же окне переходим на вкладку Сообщение об ошибке и вводим текст, который будет отображаться при попытке ввести дубликаты.
Нажимаем ОК.

👉 @Excel_lifehack

Стандартные функции подсчета и суммирования (СУММ, МАКС, МИН, СРЗНАЧ) не справляются, если диапазон содержит ошибки. На выходе они выдают ошибки.

Но есть функция, которая умеет проводить все описанные подсчеты, да еще и с возможность пропуска ошибок. Это функция АГРЕГАТ. При вводе первого и второго аргументов, выбирайте, что считать и как считать. И функция всё сделает.

👉 @Excel_lifehack

Изменение начертания текста

Для быстрой смены начертания текста существуют сочетания клавиш:
CTRL + B — полужирный;
CTRL + I — курсив;
CTRL + U — подчеркивание (линию подчеркивания можно выбрать одинарную или двойную).

👉 @Excel_lifehack

В Excel 2013 и более новых добавлена удобная возможность фильтрации данных на диаграмме. Можно быстро и просто отключать целые ряды данных или какие-то отдельные точки.

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

👉 @Excel_lifehack

Как быстро перейти к концу таблицы?

CTRL + ↓ перенесет активную ячейку к последней строке таблицы.
Аналогично CTRL + → — к последнему столбцу таблицы.
А если еще и SHIFT удерживать при этом, диапазоны будут полностью выделяться.

👉 @Excel_lifehack

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

Это позволит неопытным пользователям работать с файлом, не отвлекаясь на лишние ячейки.

👉 @Excel_lifehack

​Горячие клавиши для быстрой смены формата ячеек

С помощью сочетания клавиш CTRL + SHIFT + ! можно установить формат отображения дробных чисел с двумя знаками после запятой.
В то же время, CTRL + SHIFT + $, добавляет знак доллара, а CTRL + SHIFT + % добавляет знак процента.

👉 @Excel_lifehack