Дані — золото для будь-якого бізнесу. Але, як правило, вони організовані у файли, в яких потрібно ще розібратися. 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 є зведені таблиці.
- Є графіки.
#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.
Бажаєте отримувати дайджест статей?
