7 неизвестных функций Excel

Лайфхаки
Excel, компьютерная грамотность, таблицы, прайсы, бизнес, обучение, лайфхаки

Кто думает, что знает о программе Excel все — тот утверждает, что объял необъятное. Обычно мы используем только 5% от возможностей этой самой распространенной программы Microsoft Office и даже не интересуемся ее скрытыми функциями. А ведь Excel позволяет сравнивать прайс-листы, скрывать информацию, составлять самые сложные аналитические отчеты.

Продвинутые пользователи Excel – особая каста, которая не очень любит делиться секретами. А мы вам раскроем 7 функций Excel для учебы и бизнеса.



1. Скрытый лист
Довольно известен классический способ скрыть лист Excel – достаточно кликнуть правой кнопкой по названию листа и выбрать пункт «Скрыть», и другие пользователи не смогут увидеть содержимое. Однако имя скрытого листа и сам факт скрытия все равно будут видны. Сделать информацию абсолютно недосягаемой для других пользователей можно так:
— Нажать ALT+F11.
— В появившемся слева окне в верхней части выбрать номер листа.
— В нижней части окна в самом конце списка выбрать свойство Visible и далее выбрать опцию xlSheetVeryHidden. Теперь лист будет абсолютно невидимым для других пользователей.

2. Блокирование изменений задним числом
Мощный инструмент контроля за действиями сотрудников – запрет на изменение данных. Допустим, внесены данные о количестве проданного товара и вырученной сумме. Чтобы эти данные нельзя было изменить в пользу того или иного сотрудника, стоит использовать функцию запрета изменения данных задним числом. Порядок действий такой:
— Навести курсор на ячейку с датой в графе «Дата» и выбрать в меню пункт «Данные».
— Нажмите на кнопку «Проверка данных» и вызовите таблицу.
— В выпадающем списке «Тип данных» выберите «Другой».
— В графе «Формула» напишите =А2=СЕГОДНЯ().
— Уберите галочку с «Игнорировать пустые ячейки».
— Нажмите кнопку «ОК». Теперь, если кто-то захочет ввести другую дату, появится предупреждающая надпись.
— По такому же алгоритму можно запретить изменение данных в графе «Количество» и любой другой.

3. Запрет на ввод дублей
Очень полезная функция в работе с большими списками однотипных товаров или других наименований. Чтобы товары в прайс-листе не повторялись, можно установить запрет на ввод дублей.
— Выделите нужное количество ячеек, на которые будет распространяться запрет, например, А1:А10.
— Во вкладке «Данные» нажмите кнопку «Проверка данных».
— Во вкладке «Параметры» из выпадающего списка «Тип данных» выберите вариант «Другой».
— В графе «Формула» нужно вбить =СЧЁТЕСЛИ($A$1:$A$10;A1)<=1. Не закрывайте окно.
— В этом же окне перейдите на вкладку «Сообщение об ошибке» и там введите предупреждающий текст.
— «ОК» — подтвердите завершение операции.

4. Выборочное суммирование
Для работы с постоянными клиентами пригодится функция, собирающая данные по одному заказчику из разных категорий товаров и разных транзакций. Чтобы узнать общую сумму, на которую Ivan купил, например, итальянской колбасы (Salame di Milano).
— В ячейку G4 введите имя заказчика Ivan.
— В ячейку G5 — название продукта Salame di Milano.
— Встаньте на ячейку G7, где и будет подсчитана сумма, и напишите для нее формулу {=СУММ((С3:С21=G4)*( B3:B21=G5)*D3:D21)}. Смысл формулы мы объясняем ниже:
— Сначала введите {=СУММ и откройте скобки, в которых будет три множителя.
— Первый множитель (С3:С21=G4) ищет в указанном списке клиентов упоминания Ivan.
— Второй множитель (B3:B21=G5) делает то же самое с Salame di Milano.
— Третий множитель D3:D21 отвечает за столбец стоимости, после него нужно закрыть скобки.
— Вместо Enter при написании формул в Excel вводите Ctrl + Shift + Enter.

5. Сводная таблица
Когда собирается много информации в одной таблице: кто, какому заказчику, какой товар и на какую сумму продал, в массиве данных сложно что-то найти. Чтобы получать из таблицы выборочные данные, например, у какого менеджера было больше продаж или на какую сумму продали справочников, можно использовать сводные таблицы Excel:
— Во вкладке «Вставка» нажмите кнопку «Сводная таблица».
— В появившемся окне подтвердите действие («ОК»).
— В появившемся окошке сформируйте новую таблицу, используя только интересующие данные.

6. Товарный чек
Посчитать общую сумму заказа можно двумя способами: традиционным и продвинутым. Традиционно: добавить столбец, где перемножить цену и количество, затем посчитать сумму этого столбца. Знаток Excel выберет новаторский путь:
— Выделить ячейку C7.
— Ввести =СУММ(.
— Выделить диапазон B2:B5.
— Ввести знак умножения Excel­ — звездочку.
— Выделить диапазон C2:C5 и закрыть скобку.
— Вместо Enter при написании формул в Excel вводите Ctrl + Shift + Enter.

7. Сравнение прайсов
Если нужно сравнить наличие товаров и цены в прайсах разного времени или разных компаний, можно воспользоваться специальными функциями Excel. Например:
— Создать в книге еще один лист и скопировать в него списки товаров и из первого, и из второго прайса.
— Чтобы избавиться от дублей товаров, выделяем весь список товаров, включая его название.
— В меню выбрать «Данные» — «Фильтр» — «Расширенный фильтр».
— В появившемся окне отметить: а) скопировать результат в другое место; б) поместить результат в диапазон — выбираем имя ячейки, куда нужно записать результат; в) поставить галочку на «Только уникальные записи».
— Нажать кнопку «ОК» и, начиная с ячейки D4, получить список без дублей.
— Удалить первоначальный список товаров.
— Добавить колонки для загрузки значений прайса за дату-1 и дату-2 и колонку сравнения.
— Ввести в колонку сравнения формулу =D5-C5, отвечающую за вычисление разницы.
— Чтобы автоматически загрузить в колонки «дата-1» и «дата-2» значения из прайсов, использовать функцию =ВПР(искомое_значение; таблица; номер_столбца; интервальный _просмотр).
— «Искомое_значение» — это строчка, которую мы будем искать в таблице прайса. Легче всего искать товары по их наименованию.
— «Таблица» — это массив данных, в котором мы будем искать нужное нам значение. Он должен ссылаться на таблицу, содержащую прайс от даты-2.
— «Номер_столбца» — порядковый номер столбца в диапазоне, заданном для поиска данных. Для поиска определена таблица из двух столбцов, во втором из которых – цена.
— Интервальный_просмотр. Если таблица, в которой нужно найти значение, отсортирована по возрастанию или по убыванию, ставьте значение ИСТИНА, если не отсортирована — пишете ЛОЖЬ.
— Протянуть формулу вниз, не забыв закрепить диапазоны. Для этого поставить перед буквой столбца и перед номером строки значок доллара (чтобы это сделать, выделите нужный диапазон и нажмите клавишу F4).
— В итоговом столбце отразится разница в ценах по тем позициям, которые есть и в том, и в другом прайсе. Если в итоговом столбце отражается #Н/Д, это значит, что указанный товар есть только в одном из прайсов, а следовательно, разницу вычислить невозможно.