Володар таблиць 10 маловідомих фішок для бізнесу в excel
Ви хочете ввести список товарів в прайс-лист так, щоб вони не повторювалися. Ви можете встановити заборону на такий повтор. У прикладі вказана формула для стовпця з 10 осередків, але їх, звичайно, може бути будь-яка кількість.
- Виділяємо комірки А1: А10, на які буде поширюватися заборона.
- У вкладці «Дані» натискаємо кнопку «Перевірка даних».
- У вкладці «Параметри» зі списку «Тип даних» вибираємо варіант «Інший» (картинка 1).
- У графі «Формула» вбиваємо = СЧЁТЕСЛІ ($ A $ 1: $ A $ 10; A1)<=1.
- У цьому ж вікні переходимо на вкладку «Повідомлення про помилку» і там вводимо текст, який буде з'являтися при спробі ввести дублікати (картинка 2).
вибіркове підсумовування
Перед вами таблиця, з якої видно, що різні замовники кілька разів купували у вас різні товари на певні суми. Ви хочете дізнатися, на яку загальну суму замовник по імені ANTON купив у вас крабового м'яса (Boston Crab Meat).
- У осередок G4 ви вводите ім'я замовника ANTON.
- У комірки G5 - назва продукту Boston Crab Meat.
- встає на осередок G7, де у вас буде підрахована сума, і пишете для неї формулу. Спочатку вона лякає своїми обсягами, але якщо писати поступово, то вона має сенс стає зрозумілий.
- Спочатку вводимо
- Перший множник (С3: С21 = G4) шукає в зазначеному списку клієнтів згадки ANTON.
- Другий множник (B3: B21 = G5) робить те ж саме з Boston Crab Meat.
- Третій множник D3: D21 відповідає за стовпець вартості, після нього ми закриваємо дужки.
- Замість Enter при написанні формул в Excel потрібно вводити Ctrl + Shift + Enter.
Зведена таблиця
- Створюємо в книзі ще один лист і копіюємо в нього списки товарів і з першого, і з другого прайсу (картинка 3).
- Щоб позбутися від дублів товарів, виділяємо весь список товарів, включаючи його назву.
- У меню вибираємо «Дані» - «Фільтр» - «Розширений фільтр» (картинка 4).
- У вікні відзначаємо три речі: а) скопіювати результат в інше місце; б) помістити результат в діапазон - виберіть місце, куди хочете записати результат, в прикладі це осередок D4; в) поставте галочку на «Тільки унікальні записи» (картинка 5).
- Натискаємо кнопку «ОК» і, починаючи з комірки D4, отримуємо список без дублів (картинка 6).
- Видаляємо початковий список товарів.
- Додаємо колонки для завантаження значень прайса за 4 і 11 травня і колонку порівняння.
- Вводимо в колонку порівняння формулу = D5-C5, яка буде обчислювати різницю (картинка 7).
- «шукане_значення» - це рядок, яку ми будемо шукати в таблиці прайса. Найлегше шукати товари за їх найменуванням (картинка 8).
- «Таблиця» - це масив даних, в якому ми будемо шукати потрібне нам значення. Він повинен посилатися на таблицю, яка містить прайс від 4-го числа (картинка 9).
- «номер_стовпчика» - це порядковий номер стовпчика в діапазоні, який ми задали для пошуку даних. Для пошуку ми визначили таблицю з двох стовпців. Ціна міститься в другому з них (картинка 10).
- Простягніть формулу вниз, не забувши закріпити діапазони. Для цього поставте перед буквою стовпця і перед номером рядка значок долара (це можна зробити, виділивши потрібний діапазон і натиснувши клавішу F4).
- У підсумковому стовпці відображається різниця в цінах по тих позиціях, які є і в тому і в іншому прайсі. Якщо в підсумковому стовпці відбивається # Н / Д, це означає, що вказаний товар є тільки в одному з прайсів, а отже, різницю обчислити неможливо.