Як витиснути з Excel максимум | Бізнес-школа Laba (Лаба)
Для відстеження статусу замовлення - авторизуйтесь
Введіть код, який був надісланий на пошту Введіть код із SMS, який був надісланий на номер
anastasiiasytar@gmail.com
Код дійсний протягом 2 хвилин Код з SMS дійсний протягом 2 хвилин
Ви впевнені, що хочете вийти?
Сеанс завершено
На головну

Пошук

Зміст

Як витиснути з Excel максимум

10 лайфхаків від лектора курсу «Advanced Excel».

cover-65b1038688e17983917727.jpg

Дані — золото для будь-якого бізнесу. Але, як правило, вони організовані у файли, в яких потрібно ще розібратися. Microsoft Excel — один із тих інструментів, який допомагає це зробити.

Операційний менеджер Delivery Unit в Uklon Микита Свідло розповідає про десять простих, але дуже корисних лайфхаків Excel, які заощадять час та гроші.

#1. Розбити на колонки

Допустимо, ви збираєте дані з Google Форм. Вони організовуються у форматі CSV-файлу та не розбиваються на колонки автоматично. Тобто клітинка виглядає як довгий рядок, де текст розділений комами, косою лінією або крапкою з комою.

Працювати з даними у такому форматі незручно. Щоб проводити обчислення, я розбиваю ці дані на стовпчики.

Як це зробити

Натискаємо на вкладку «Дані» → «Занаряддя даних» та вибираємо «Текст за стовпцями». З'являється віконце, в якому потрібно виконати три дії поетапно:

1. Вибрати формат даних. У вікні першого кроку поставити галочку на опції «З роздільниками» (іноді система за замовчуванням вибирає правильний роздільник). Натиснути «Далі».

2. Вибрати формат роздільника. Із запропонованих варіантів (знак табуляції, крапка з комою, кома, пробіл або інший) вибрати один — наприклад, кому. Натиснути на «Далі».

3. Вибрати формат колонки — загальний, текстовий або з датою. Вигляд нової колонки відображається відразу ж у спливному вікні. Натиснути «Готово». Система попередить, що інформація перебудується — і через декілька секунд отримуємо таблицю з колонками.

#2. Об'єднати колонки

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

Як це зробити

Формула CONCATENATE дозволяє об'єднати декілька значень в одному рядку. Наприклад, потрібно об'єднати клітинки В2 та C2. Для цього:

1. Вписати в клітинку формулу — наприклад, =CONCATENATE(В2;C2).

2. Додати «;" "» між значеннями. Після цього формула виглядатиме як =CONCATENATE(В2;" ";C2).

Альтернатива. Символ & (амперсанд) також допомагає зв'язувати колонки. Щоб об'єднати значення А3, В3 та С3, потрібно прописати код — наприклад, "=А3&" "В3&" "&C3". Замість пробілу можна ставити дефіс, тире або будь-який інший знак пунктуації.

#3. Посилання на клітинки та діапазони всередині однієї книги Excel

Я завжди раджу не використовувати вставку як значення під час роботи в Excel. Адже вже за 10 хвилин буде складно згадати, звідки взято ту чи іншу цифру чи значення. Куди зручніше і практичніше використовувати лінк на інший аркуш Excel. Так завжди можна знайти вихідне значення та легко перевірити весь зміст.

Як це зробити

1. Вписати «=» у клітинку, куди хочете додати посилання.

2. Перейти на інший лист, клацнути по другій клітинці. Так ви поставите посилання будь-яке значення в іншому аркуші Excel-книги. Натиснути клавішу Enter.

3. Значення переноситься в цю клітинку. У рядку вгорі виглядатиме приблизно так: «='Акруш1'!М7».

Якщо у першій клітинці натиснути комбінацію «Ctrl + [», відкриється лист, звідки взяті ці дані. Зручно — завжди знаєш, звідки беруться значення, при цьому комбінація працює і відкриває посилання на інші книги Excel.

#4. Зв'язок з іншими книгами Excel

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

Як це зробити

1. Вписати «=» у клітинку, куди хочете додати посилання.

2. Перейти в іншу книгу, клацнути по потрібній клітинці. Так ви поставите посилання на будь-яке значення в іншій книзі Excel. Натиснути клавішу Enter.

3. Після чого посилання виглядатиме так: «='[Книга1.xls]Аркуш1'!М7».

При цьому можна керувати зовнішніми посиланнями за допомогою спеціального меню на панелі інструментів «Дані» → «Запити та звʼязки» → «Редагувати звʼязки».

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

Excel і сам нагадує, коли значення можна оновлювати.

Якщо дані у другому файлі змінилися, у верхній частині сторінки з'явиться попередження у форматі «У вас є лінки на зовнішні джерела, які можуть бути оновлені. Оновити?». Натискаєте так, і всі лінки у вашій табличці автоматично оновляться. Але це можна зробити і вручну лише для окремих лінків.

#5. Зробити запит до Google Таблиці

Стартапи та невеликі компанії часто працюють у Google Таблицях. У ці файли збираються дані з Google Аналітики або інших сервісів у реальному часі, до них є онлайн-доступ одразу в кількох людей команди.

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

Як це зробити

Щоб отримати дані з онлайн-файлу, потрібно:

1. Налаштувати доступ до файлу Google Таблиці «Для всіх, хто має посилання».

2. Скопіювати посилання на Google Таблицю та клацнути на вкладку «Дані».

3. Натисніть кнопку «Отримання зовнішніх даних» зліва на верхній панелі Excel-файлу.

4. Відобразиться список можливих джерел, до яких можна підключитися через Excel. Вибрати опцію «З робочої книги». З'явиться діалогове вікно.

5. Вставити посилання на таблицю в рядок «Ім'я файлу» у нижній частині діалогового вікна.
Важливо! Наприкінці посилання замість «edit» написати «export». Натиснути «Відкрити». Декілька секунд чекаємо. Відкриється нове діалогове вікно.

6. Вибрати у цьому вікні файл-таблицю та натиснути кнопку «Завантажити». Вказати, де з'явиться ця нова інформація: наприклад, «Таблиці» → «A1».

Тепер ці дані мають прямий зв'язок із вибраною Google Таблицею. Якщо захочеться оновити дані в Excel, потрібно клацнути по таблиці правою кнопкою та натиснути опцію «Оновити». Підтягнуться нові дані.

Обмеження. Неможливо підключитися, якщо:

  • Доступ до Google Таблиці обмежений — за посиланням її не відкрити.
  • У Таблиці Google є об'єднані колонки.
  • У Таблиці Google є зведені таблиці.
  • Є графіки.

Рекомендуємо прочитати:

preview-65a6b682e7035788333261.jpg

Як працювати в Google Таблицях

Читати

#6. Зробити одну таблицю з кількох

Припустимо, в одній папці я маю дані за кілька місяців — і вони розділені за файлами (один місяць — один файл). Мені потрібно поєднати їх в один файл. Наприклад, дані з таблиці «Січень» та «Лютий» зібрати до однієї.

Як це зробити

1. Переходимо у вкладку «Дані» → «Новий звпит» → «З файлу» → «З папки». У вікні вибрати шлях до потрібної папки.

2. Вибрати файли з папки, яка відображається у вікні. Натиснути кнопку «Об'єднати та завантажити в». Відобразиться вікно з налаштуваннями об'єднаного файлу.

3. Вибрати файл-приклад, за яким організовуватиметься інформація. Задаємо приклад — вибираємо його в цьому ж вікні зліва. Чекаємо.

4. Вибираємо із запропонованих опцій, в якому вигляді буде вивантажено інформацію. Наприклад, у вигляді таблиці. Натискаємо «Ок».

Отримуємо одну таблицю, де дані «Січень» та «Лютий» — разом. Якщо у спільну папку покласти ще й «Березень», то просто натискаємо «Оновити», і в таблиці з'являються дані за березень.

#7. Зробити «розумну» таблицю

Простий діапазон, що має табличний формат, — це ще не таблиця. На клітинки такої «таблиці», наприклад, не можна посилатися. Щоб діапазон став справжньою «розумною» таблицею, потрібно виконати одну маленьку дію.

Як це зробити

Натискаємо Ctrl+T всередині діапазону. З'являється діалогове вікно — у ньому підтверджуємо, що хочемо створити таблицю.

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

Один із плюсів «розумних» таблиць у тому, що вони можуть розширюватися: якщо додається нове поле внизу, воно автоматично підтягується до таблиці. Зі звичайними таблицями такого не відбувається.

#8. Зробити залежну зведену таблицю

З «розумної» таблиці можна зробити зведену таблицю — щоб швидше та зручніше відображати дані та проводити обчислення.

Як це зробити

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

2. Вказати, куди помістити звіт зведеної таблиці: «Новий аркуш» або «Наявний аркуш». Якщо вибрати «Наявний аркуш», потрібно буде вказати клітинку — там буде зведена таблиця.

3. Набрати поля у зведену таблицю. Праворуч з'являється панель, там — список полів, значення яких заберемо для зведеної таблиці. Наприклад, «магазини», «продукт», «обсяг продукту».

Якщо в «розумній» таблиці-джерелі з'явиться новий продукт, він автоматично додасться і до зведеної таблиці. Для цього потрібно буде просто оновити другу таблицю: клацнути правою кнопкою мишки і вибрати «Оновити».

Якщо робити те саме в звичайній таблиці, зведена автоматично оновлюватися не буде.

#9. Додати «розумний» зріз

В Excel багато хто користується фільтрами, але зрізи чи шкали вміють робити не все. Я використовую їх, щоб оформити потрібну інформацію максимально наочно. Зрізи можна робити до «розумної» та зведеної таблиць.

Як це зробити

1. Перейти до вкладки «Вставлення». Клацаємо по кнопці «Роздільник» у правій частині верхньої панелі.

2. Вибрати колонки для зрізу. Колонки у списку ті ж, що й у таблиці — потрібно відзначити галочками потрібні. З'являється інтерактивний зріз.

Зріз допомагає тримати необхідні дані під рукою. Натискаєш на значення в зрізі — і за ним фільтрується головна таблиця. Це швидше та зручніше, ніж звичайні фільтри.

#10. SUMIF та її старша сестра SUMIFS

Наприклад, є обсяг реалізації товару в деталізації по Даті, Магазину та Продукту — і потрібно отримати дані про продаж тільки по одному Магазину або по всіх Магазинах, але тільки за певним Продуктом. Це не складний кейс — тут нам допоможе SUMIF.

Є й складніший кейс. Наприклад, знайти реалізацію на певну Дату, за певним Магазином та Продуктом. У такому кейсі проста формула SUMIF вже не підійде — потрібно звертатися за допомогою до її старшої сестри — SUMIFS.

Як це зробити

1. Натиснути на порожню клітинку — наприклад, H5. У ній відображатиметься результат.

2. Вказати потрібний вам період. Наприклад, ввести його в клітинку H2.

3. Вказати необхідний код товару. Наприклад, ввести його в клітинку H3.

4. Ввести формулу в клітинку Н5 «=SUMIFS(E2:E48;A2:A48;H2;C2:C48;H3)».

У підсумку виходить сума реалізації на визначену Дату і певний Продукт. Умов може бути більше, ніж у прикладі, — максимум 255.

Бажаєте отримувати дайджест статей?

Один лист з найкращими матеріалами за місяць. Підписуйтесь, аби нічого не проґавити.
Дякуємо за вашу підписку!
Курс з теми:
«Advanced Excel»
Програми
Веде Микита Свідло
25 березня 22 квітня
Микита Свідло