Хто вважає, що знає про програму Excel все, — той стверджує, що охопив неосяжне. Зазвичай ми використовуємо лише 5% від можливостей цієї найпоширенішої програми Microsoft Office та навіть не цікавимося її прихованими функціями. При тому Excel дозволяє порівнювати прайс-листи, приховувати інформацію, формувати найскладніші аналітичні звіти.  

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

1. Прихований лист

Доволі відомий класичний спосіб сховати лист Excel – достатньо кликнути правою кнопкою по назві листа та обрати пункт «Сховати», й інші користувачі не зможуть побачити зміст. Але назва прихованого листа й сам факт приховання все одно будуть видимі. Зробити інформацію абсолютно недосяжною для інших користувачів можна так:

— Натиснути ALT+F11.

— У вікні, яке з’явилося, зліва вгорі обрати номер листа.

— У нижній частині вікна наприкінці переліку обрати властивість Visible та обрати опцію xlSheetVeryHidden. Тепер лист буде абсолютно невидимий для інших користувачів.

2. Блокування змін заднім числом

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

— Навести курсор на клітинку з датою в графі «Дата» та обрати в меню пункт «Дані».

— Натиснути на кнопку «Перевірка даних» та викликати таблицю.

— У переліку «Тип даних» оберіть «Інший».

— У графі «Формула» напишіть =А2=СЕГОДНЯ().

— Заберіть галочку з «Ігнорувати порожні клітинки».

— Натисніть кнопку «ОК». Тепер, якщо хтось захоче ввести іншу дату, з’явиться попереджувальний напис.

— За таким самим алгоритмом можна заборонити зміну даних в графі «Кількість» та будь-якій іншій.

3. Заборона на введення дублів

Дуже корисна функція в роботі з великими переліками однотипних товарів чи інших найменувань. Щоб товари у прайс-листі не повторювалися, можна встановити заборону на введення дублів.  

— Виділіть потрібну кількість клітинок, на які поширюватиметься заборона, наприклад, А1:А10.

— У вкладці «Дані» натисніть кнопку «Перевірка даних».

— У вкладці «Параметри» з переліку, що випадає — «Тип даних», — оберіть варіант «Інший».

— У графі «Формула» треба вбити =COUNTIF($A$1:$A$10;A1)<=1. Залиште вікно відкритим.

— У цьому ж вікні перейдіть на вкладку «Повідомлення про помилку» й там введіть попереджувальний текст.

— «ОК» — підтвердження завершення операції.

4. Вибіркове підсумовування

Для роботи з постійними клієнтами знадобиться функція, яка збирає дані про одного замовника з різних категорій товарів й різних транзакцій. Щоб дізнатися загальну суму, на яку Ivan купив, наприклад, італійської ковбаси (Salame di Milano):

— У клітинку G4 введіть ім’я замовника Ivan.

— У клітинку G5 — назву продукту Salame di Milano.

— Cтаньте на клітинку G7, де й буде підрахована сума, та напишіть для неї формулу {=SUM((С3:С21=G4)*( B3:B21=G5)*D3:D21)}. Зміст формули ми пояснюємо нижче:

— Спершу введіть {=SUM та відкрийте дужки, в яких буде три множники.

— Перший множник (С3:С21=G4) шукає у вказаному переліку клієнтів згадування імені Ivan.

— Другий множник (B3:B21=G5) робить те саме з Salame di Milano.

— Третій множник D3:D21 відповідає за стовпчик вартості, після нього треба закрити дужки.

— Замість Enter при написанні формул в Excel введіть Ctrl + Shift + Enter.

5. Зведена таблиця

Коли назбирується багато інформації в одній таблиці: хто, якому замовнику, який товар і на яку суму продав, у масиві даних важко щось знайти. Щоб отримувати з таблиці вибіркові дані, наприклад, у якого менеджера було більше продажів чи на яку суму продали довідників, можна використовувати зведені таблиці Excel:

— У вкладці «Вставка» натисніть кнопку «Зведена таблиця».

— У вікні, що з’явилося, підтвердіть дію («ОК»).

— У вікні, що з’явилося потім, сформуйте нову таблицю, використовуючи лише ті дані, що вас цікавлять.

 

6. Товарний чек

Порахувати загальну суму замовлення можна двома способами: традиційним і просунутим. Традиційно: додати стовпчик, де перемножити ціну й кількість, потім порахувати суму цього стовпчика. Знавець Excel обере новаторський шлях:

— Виділити клітинку C7.

— Ввести =SUM(.

— Виділити діапазон B2:B5.

— Ввести знак множення Excel­ — зірочку.

— Виділити діапазон C2:C5 й закрити дужку.

— Замість Enter при написанні формул в Excel введіть Ctrl + Shift + Enter.

7. Порівняння прайсів

Якщо потрібно порівняти наявність товарів й ціни у прайсах різного часу чи різних компаній, можна використати спеціальні функції Excel. Наприклад:

— Створити у книзі ще один лист й скопіювати до нього переліки товарів і з першого, і з другого прайсу.

— Щоб позбутися дублів товарів, виділяємо весь перелік товарів, включно з його назвою.

— У меню обрати «Дані» — «Фільтр» — «Розширений фільтр».

— У вікні, що з’явилося, помітити: а) скопіювати результат в інше місце; б) розмістити результат у діапазон — обираємо ім’я клітинки, куди треба записати результат; в) поставити галочку на «Лише унікальні записи».

— Натиснути кнопку «ОК» і, починаючи з клітинки D4, отримати перелік без дублів.

— Видалити вихідний перелік товарів.

— Додати колонки для завантаження значень прайсу за дату-1 і дату-2 й колонку порівняння.

— Ввести до колонки порівняння формулу =D5-C5, що відповідає за отримання різниці.

— Щоб автоматично завантажити до колонок «дата-1» й «дата-2» значення з прайсів, використовувати функцію =VLOOKUP(lookup_value; таблиця; column_number; range_lookup).

— «Lookup_value» — це рядок, який ми будемо шукати в таблиці прайсу. Легше за все шукати товари за їхнім найменуванням.

— «Таблиця» — це масив даних, у якому ми будемо шукати потрібне нам значення. Він має посилатися на таблицю, яка містить прайс від дати-2.

— «Column_number» — порядковий номер стовпчика в діапазоні, який задано для пошуку даних. Для пошуку визначена таблиця з двох стовпчиків, у другому з яких – ціна.

— «Range_lookup». Якщо таблиця, в якій треба знайти значення, відсортована за зростанням чи за спаданням, поставте значення ІСТИНА, якщо не відсортована — пишіть ХИБНІСТЬ.

— Протягнути формулу донизу, не забуваючи закріпити діапазони. Для цього треба поставити перед літерою стовпчика й перед номером рядка значок долару (щоб це зробити, виділіть потрібний діапазон й натисніть клавішу F4).

— У підсумковому стовпчику відіб’ється різниця в цінах за тими позиціями, що є і в тому, і в іншому прайсі. Якщо у підсумковому стовпчику відбивається #N/A, це означає, що вказаний товар є лише в одному з прайсів, відповідно, різницю вирахувати неможливо.