Кто думает, что знает о программе 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).

— В итоговом столбце отразится разница в ценах по тем позициям, которые есть и в том, и в другом прайсе. Если в итоговом столбце отражается #Н/Д, это значит, что указанный товар есть только в одном из прайсов, а следовательно, разницу вычислить невозможно.