Практичні лайфхаки Excel: створення розкривних списків через розумну таблицю та консолідація даних з кількох аркушів для підсумків бізнесу.
Key Takeaways
- Розкривні списки на основі розумної таблиці усувають помилки та автоматично оновлюються.
- Консолідація дозволяє швидко отримати загальний підсумок з різних аркушів.
- Гарячі клавіші та структуровані довідники суттєво економлять час роботи в Excel.
What the video covers
- Розкривні списки забезпечують точність, швидкість введення та стандартизацію даних, запобігаючи помилкам на кшталт різного написання імені менеджера.
- Довідники рекомендовано розміщувати на окремому аркуші, щоб не перевантажувати головну таблицю.
- Для унікальних значень використовується вкладка Дані та функція Видалити повтори.
- Перетворення списку на розумну таблицю (Ctrl+T) дозволяє розкривному списку автоматично оновлюватися при додаванні нових значень.
- Розкривний список створюється через Перевірка даних, тип даних Список, з джерелом у вигляді INDIRECT з назвою таблиці.
- Консолідація об'єднує дані з кількох аркушів (кав'ярні 1, 2, 3) у єдиний загальний підсумок через інструмент Консолідація.
- При консолідації вказуються підписи верхнього рядка та значення лівого стовпця для коректного зведення.
- Ці інструменти знижують ризик помилок і прискорюють доступ до даних для бізнесу.
- Відео є останнім у першому модулі онлайн-курсу, який охоплює стрічку Excel, гарячі клавіші та лайфхаки.
Chapters
- 00:00Вступ: лайфхаки для ефективної роботи в Excel
- 00:51Проблема помилок у даних та переваги розкривних списків
- 01:36Підготовка даних для розкривного списку менеджерів
- 02:32Створення довідника та видалення повторів
- 03:33Розумна таблиця та сортування значень
- 04:07Створення розкривного списку у таблиці продажів
- 04:50Перевірка оновлення списку та інші застосування
- 05:33Консолідація даних трьох кав'ярень
- 07:21Підсумки модуля та анонс наступного модуля
Full Transcript — Download SRT & Markdown
Speaker A
У цьому відео зосередимося на практичних лайфхаках, які допоможуть ефективніше працювати в Excel, економити час та уникати зайвих зусиль. Розглянемо конкретні приклади, які ви можете адаптувати під ваші потреби незалежно від сфери діяльності бізнесу. Це буде створення розкривних списків та перші
Speaker A
кроки до консолідації даних. Почну з того, як ви можете оптимізувати збір даних до таблиць. Чи траплялося вам вводити дані в таблиці, а потім виявляти помилки через неправильне чи непослідовне написання? Наприклад, коли спочатку ви вказуєте менеджера як Петро Іваненко, а
Speaker A
пізніше Іваненко Петро. Далі працювати з такими списками складно, адже формально ви отримали двох різних співробітників, хоча мали на увазі одного. Розкривні списки - це простий, але ефективний інструмент в Excel, який дозволяє уникати таких ситуацій. Використання розкривних списків має три основні
Speaker A
переваги. Перша - точність даних, що запобігає помилкам у введенні даних. Друга- швидкість, адже вибір зі списку значно прискорює процес. І третя, стандартизація даних, тобто приведення до одного формату. Розкривні списки дозволяють обмежити введення даних у певній клітинці до вибору і заздалегідь
Speaker A
визначеного набору значень. Користувач обирає значення зі списку замість того, щоб вводити його вручну. Є кілька підходів до створення розкривних списків. У цьому відео розглянемо найоптимальніший через створення розумної таблиці. Перейдемо до створення розкривного списку для менеджерів. Зараз у таблиці лише чотири менеджери, і ми
Speaker A
можемо вручну створити список з унікальними значеннями. Проте так буває не завжди. На практиці різні бізнес-процеси можуть передбачати роботу зі списками, які містять багато значень, як, наприклад, у стовпці найменування.
Speaker A
Тому скопіюємо весь стовпецьменеджер. Спочатку виділимо його через уже знайому нам комбінацію гарячих клавіш. Ctrl та Shift та стрілка вниз.
Speaker A
Далі для копіювання виділеного діапазону натиснемо Ctrl та C. Таблиці для розкривних списків, їх ще можна назвати довідниками, краще розміщувати на окремому аркуші. Це практично і дозволяє уникнути перевантаження головного аркуша зайвими даними. Тому створюємо новий аркуш. Для цього треба натиснути на плюс
Speaker A
у нижньому меню робочої зони. Рекомендую новий аркуш назвати довідники. За допомогою Ctrl та V вставимо раніше скопійований вміст стовпця. Для створення розкривного списку значення в таблиці мають бути унікальними, тобто такими, що не повторюється. Щоб видалити повтори, перейдемо на вкладку Дані і в знаддях
Speaker A
даних натиснемо Видалити повтори. Тепер перетворимо список на розумну таблицю за допомогою Ctrl та T. Це потрібно для того, щоб у разі доповнення значень до таблиці розкривний список автоматично оновився.
Speaker A
Порядок даних у таблиці визначатиме вигляд розкривного списку, тому для зручності відображення посортуємо дані в порядку зростання. Для цього біля назви праворуч оберемо сортування від А до Я і задамо назву таблиці. Щоб це зробити, переконайтеся, що ви знаходитесь в
Speaker A
контекстному меню Конструктор таблиць. Назву таблиці задаємо в лівій частині меню у властивостях. Пропоную таблицю з переліком менеджерів назвати однойменно.
Speaker A
Менеджери. Перейдемо на аркуш продажі та реалізуємо розкривний список. Для цього виділимо потрібний діапазон клітинок униз, або можна виділити весь діапазон.
Speaker A
Перейдемо на вкладку Дані і натиснемо Перевірка даних. В типі даних потрібно вказати список. У полі джерело формулу дорівнює indirect. Далі відкриваємо дужку та лапки та вказуємо назву розумної таблиці менеджери. Після цього закриваємо лапки та дужку і натискаємо ОК. Перевіримо, чи створився список
Speaker A
правильно. Так, усе вірно. Тепер давайте змоделюємо ситуацію, щоб в кав'ярні з'явився новий менеджер. Перейдемо на вкладку Довідники та додамо нового менеджера в таблицю. Повернемося, щоб перевірити оновлення розкривного списку і бачимо, що новий менеджер теж з'явився у створеному нами списку.
Speaker A
За аналогічним підходом для таблиці з продажами можна створити розкривні списки для виду оплати, особової картки клієнта та найменування. Далі перейдемо до ще одного практичного інструменту для оптимізації робочих процесів, а саме консолідації даних.
Speaker A
Іншими словами, консолідацію можна назвати процесом об'єднання або зведення даних із різних джерел чи таблиць у єдиний узагальнений вид. Уявімо, що в вашому бізнесі три окремі кав'ярні в різних районах міста. В нашому робочому файлі є три аркуші: кав'ярня 1, 2 та 3.
Speaker A
На кожному з них наведені результати продажів по кожному кварталу. Щоб підвести загальний річний підсумок за всіма кав'ярнями, використаємо консолідацію. Для цього створимо аркош загальний підсумок. Далі перейдемо до вкладки Дані та натиснемо консолідація. До списку діапазонів нам потрібно почергово додати підсумкові
Speaker A
дані по всіх кав'ярнях. Спочатку перейдемо на аркуш кав'ярня один. Виділяємо всю таблицю, натиснувши C та A. Після цього натискаємо кнопку додати.
Speaker A
Повторюємо дії для вкладок кав'ярня 2 та кав'ярнят. Таким чином, у списку діапазонів має з'явитися три діапазони таблиць, які будемо консолідувати.
Speaker A
Обов'язково вказуємо, що при створенні консолідованої таблиці були використані підписи верхнього рядка та значення лівого стовпця і натискаємо ОК. Швидко отримуємо таблицю з загальним підсумком по трьох кав'ярнях. У цьому відео ми розглянули, як створювати розкривні списки та працювати з консолідацією
Speaker A
даних. Ці інструменти дозволяють ефективно структурувати дані та оптимізувати робочі процеси. Вони особливо корисні для бізнесу та підприємництва, оскільки спрощують управління даними, знижують ризик помилок і забезпечують швидкий доступ до необхідної інформації. Це відео останнє в першому модулі нашого онлайн-курсу. Підсумуємо пройдені теми.
Speaker A
Ми детально ознайомилися зі стрічкою інструментів Excel. Окрему увагу приділили використанню гарячих клавіш та швидких команд та розглянули лайфхаки для досягнення максимальних результатів. Наступний модуль нашого курсу- функції та формули для бізнес-завдань. Ми зосередимося на практичних інструментах для аналізу, структурування даних та прогнозування за
Speaker A
допомогою Excel. Ви дізнаєтесь, як автоматизувати рутинні завдання та підвищити продуктивність через шаблони та макроси. До зустрічі.
Topics:Excelрозкривний списокконсолідація данихрозумна таблицялайфхаки Excelоптимізація робочих процесівперевірка данихгарячі клавіші











