Надбудова для Excel заощадить час відділу контекстної реклами
Я вирішував більшість завдань шляхом розробки власних функцій та макросів, тому що завжди знаходилося щось, що не влаштовувало в готових інструментах, або просто нема чого шукати, коли воно є під рукою.
Так у мене накопичилася велика бібліотека функцій та макросів, які я публікував зазвичай окремо, але деякі збирав у єдиний інструмент. Таким став файл із жартівливою назвою «Робот-розпізнавач», останню версію якого завжди можна знайти як у мене на сторінці у Facebook, так і звичайним пошуком в інтернеті.
Однак зберігати на комп'ютері окремий файл і відкривати його щоразу, коли потрібно виконати якесь завдання не дуже зручно, тому я вирішив зробити надбудову для Excel, яка швидко завантажуватиметься разом з Excel і розширюватиме його функціонал «з коробки».
Перед створенням публічно запитав у колег, які функції могли б бути корисними, зворотний зв'язок дав життя багатьом фічам, про які сам не думав.
1. Завантажте файл надбудови і збережіть його в директорії, яку не плануєте переміщати і точно не видаліть (наприклад, окремій папці прямо в корені диска). Не потрібно відкривати файл.
2. Перейдіть в "Параметри Excel" і знайдіть пункт "Надбудови", натисніть кнопку "Перейти".
У вікні через «Огляд» файлів знайдіть на комп'ютері завантажений файл надбудови, виберіть його та натисніть «ОК». Він повинен автоматично з'явитися у списку та бути з активним чекбоксом:
Натисніть "ОК". Готово! Тепер все має працювати, при кожному запуску Excel завантажуватиметься і ця надбудова, і всі функції та макроси в ній. Кнопки макросів з'являться в окремій панелі «Надбудови» і виглядатимуть приблизно так:
Якщо після перезапуску програми панель"Надбудови" не відкривається, можливо, її блокує захист операційної системи. Щоб виправити, натисніть файл правою кнопкою миші, відкрийте властивості і клацніть на кнопку «Розблокувати»:
Функціонал
Насамперед пробіжимося помакросам.
Більшість макросів працюють з виділеним діапазоном з 1 стовпця, перетворюючи його вміст на місці, для багатьох є еквівалентні функції, що працюють з осередком. Функції можуть знадобитися у випадках, коли необхідно зберегти вихідні дані. Важливою умовою коректної роботи макросів є відсутність порожніх рядків у виділеному діапазоні.
Для виклику макросів зайдіть у панель Надбудови, виберіть потрібний і натисніть кнопку.
Видалити пунктуацію // видаляє всі можливі символи, крім прогалин, цифр та букв;
Проставити всі модифікатори // проставляє модифікатори усі слова в діапазоні;
Проставити модифікатори крім стоп-слів // проставляє символ «+» (модифікатор широкої відповідності AdWords) перед кожним словом у кожному рядку виділеного діапазону, крім стоп-слів (поточний список, як тут). Інші варіанти типів відповідності зобразив схематично, має бути так зрозуміло;
Виправити великі // перетворює рядки діапазону, роблячи перші літери речень заголовними;
Видалити UTM // видаляє всі UTM-мітки в діапазоні, якщо вони є. При цьому залишає всі інші параметри недоторканими. Також видаляє зайві прогалини;
Видалити слова із цифр // іноді буває потрібно, наприклад, перед складанням частотного словника;
Видалити стоп-слова // видаляє всі українські стоп-слова з масиву (список той самий, що вище);
Частотний словник // цей макрос проводить аналіз виділеного діапазону щодо зустрічальності у ньому унікальних слівта виводить результат на окремий лист;
Скомбінувати списки // Це генератор лінійних комбінацій. Дозволяє скласти лінійні комбінації на основі кількох списків. Списків і елементів у них може бути будь-яка кількість, але є ряд обмежень:
- Списки не повинні містити порожніх осередків,
- Не повинно бути порожніх стовпців,
- Перший рядок першого списку – Осередок «A1».
Коротко зафункціями :
= слів (A1) // кількість слів у комірці. Функція по суті «ховає під капот» громіздкий синтаксис =ДЛСТР(СЖПРОБІЛИ(A1)) – ДЛСТР(ПІДСТАВИТИ(СЖПРОБІЛИ(A1);” “;”)) + 1, який обчислює кількість слів як різницю довжини рядка з пробілами і без (слів на 1 більше, ніж прогалин у рядку);
=ТочнеДиректАбоФразовеAdWords(A1) // аналогічно попередньої розставляє лапки;
=ПроставитиМодифікатори(A1) // проставляє знак «+» перед кожним словом;
= Закріпити Словоформи (A1) // проставляє знак «!» перед кожним словом;
= БезПунктуації (A1) // видаляє всі зайві пунктуаційні символи (корисно для роботи з ключовими словами);
=БезUTMметок(A1) // видаляє UTM-мітки з посилань, не торкаючись жодних інших параметрів ні перед, ні після них;
=Є Український(A1) // корисно для швидкої фільтрації українських запитів, розпізнає за
30 різних факторів (наявність у рядку певних символів або поєднань символів);
=Є Латиниця(A1) // перевіряє, чи є в комірці будь-які символи латиниці;
=ЄЦифри(A1) // аналогічно до попередньої, але з цифрами;
=ВсеСВеликий(A1) // робить великими всі перші літери слів;
= ЛатиницяСВеликий(A1) // робить заголовними всі перші літери слів, що містять латиницю;
=СортуватиАЯВнутрішньоосередки(A1) // сортує слова всерединіосередки за абеткою від А до Я;
= Знайти Слова зі списку (A1; $ J $ 1: $ K $ 2000) // дозволяє прямо в Excel проводити кластеризацію за маркерами. Аналогічно попередньої функції шукає в масиві з двох стовпців, проте, на відміну від неї, повертає не всі, а лише знайдені слова. Є регістрозалежною, тому для досягнення потрібного результату шукані фрази і слова в стовпцях, що переглядаються, повинні бути в одному регістрі (або ПРОПИС або СТРОЧН).