Як об'єднати осередки в Google Таблиці без втрати даних: Повний посібник

Об'єднання осередків - це процес, який дозволяє з'єднувати кілька осередків в одну, що може значно покращити форматування таблиць і зробити дані зручніше читати. У Google Таблицях ця функція використовується для створення більш організованого та структурованого представлення інформації. Користувачі можуть об'єднувати комірки як по горизонталі, так і по вертикалі, залежно від своїх потреб та переваг.

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

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

Стандартне об'єднання осередків у Google Таблицях: плюси та мінуси

Перед тим як розпочати процес об'єднання осередків у Google Таблиці, дуже важливо провести попередню підготовку даних.

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

Коли вам потрібно об'єднати осередки в Google Таблицях, стандартний інструмент Об'єднати осередки часто є першим вибором. Тим не менш, хоча він швидкий і може бути достатнім для базових потреб в об'єднанні, він має одне помітне обмеження, яке змусить вас двічі подумати в залежності від вашого варіанту використання. Дозвольте мені показати, що я маю на увазі.

Щоб об'єднати осередки в Google Таблицях за допомогою цього інструменту, виконайте такі дії:

Або клацніть піктограму «Об'єднати комірки» на панелі інструментів, щоб об'єднати все в одну комірку.
...або натисніть стрілку вниз і вкажіть тип злиття там:

Об'єднати все , щоб об'єднати всі вибрані осередки в одну:

Об'єднати по вертикалі, щоб об'єднати вибрані осередки в кожному стовпці:

Об'єднати по горизонталі, щоб об'єднати вибрані осередки в кожному рядку:

Ну, найістотніший із них — втрата даних.

Як ви бачите, стандартний інструмент Google Об'єднати осередки зберігає вміст тільки верхньої лівої комірки. Всі інші значення губляться.

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

Отже, його єдина стандартна можливість — об'єднувати осередки без збереження всіх даних, що робить його менш придатним для складніших потреб.

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

Отже, якщо ви бажаєте зберегти всі дані, давайте розглянемо інші варіанти об'єднання осередків у Google Таблицях без втрати даних.

Об'єднайте осередки в Гугл Таблицях за допомогою ЗЧЕПИТИ

Наступна стандартна опція, яку пропонують електронні таблиці, – це функція ЗЧЕПИТИ. Вона розроблена спеціально для об'єднання тексту з двох або більше осередків в одну:

І оскільки вона об'єднує всі текстові рядки, які ви вказуєте, жодних даних не буде втрачено. Погляньмо, як це працює.

Я буду використовувати СЧЕПИТИ для об'єднання імені та прізвища в моїй Google таблиці. Кожне повне ім'я буде поміщене у свій власний осередок з пробілами між ними:

Виберіть комірку, в якій потрібно розмістити об'єднаний текст.
Введіть цю формулу:

і скопіюйте її вниз по стовпцю:

Ця формула поєднує вміст осередків A2, B2, C2 і додає пробіл між ними.

Очевидно, на відміну від стандартних методів об'єднання, ЗЧЕПИТИ не втрачає жодних даних. І ви можете додавати будь-які потрібні вам роздільники між значеннями, що об'єднуються.

Альтернативний варіант: функція CONCAT

Функція CONCAT є спрощеною версією функції ЗЧЕПИТИ в Google Таблицях:

Щоб об'єднати осередки Google таблиці за допомогою цієї функції, необхідно перерахувати необхідні значення:

Значення_1 — запис, до якого потрібно додати значення_2.
Значення_2 - значення, що приєднується.

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

Як бачите, функція дуже проста, але в неї є суттєві недоліки:

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

Результат цієї формули такий самий, як ви бачили на скріншоті трохи вище.

Оператор об'єднання амперсанд (&)

Використання символу амперсанда (&) у формулах замість функції CONCAT дасть вам той самий результат:

Однак, цей оператор об'єднання більш гнучкий. Він може поєднати більше двох осередків одночасно і при цьому розділити їх пробілами або іншими символами.

Можна використовувати як роздільник різні символи. Наприклад, за допомогою функції СИМВОЛ можна вставити перенесення рядка після прізвища:

Ось як це виглядатиме:

Якщо ви ще не отримали бажаного результату, є ще одна функція, яку варто спробувати.

Функція JOIN

Ось ще одна функція, яка допоможе вам об'єднати осередки в Google таблиці без втрати даних.

JOIN(розділювач; значення_або_масив1; [значення_або_масив2; . ])

На відміну від СЧЕПИТИ, функція JOIN поєднує всі значення з діапазону осередків в один текстовий рядок, використовуючи роздільник, який ви вказуєте лише один раз:

При використанні ЗЧЕПИТИ, щоб об'єднати кілька значень з одним і тим самим роздільником після кожного запису, вам доведеться чергувати кожне значення з роздільником, як це робили ми в нашому першому прикладі.

Однак, JOIN поєднує всі комірки Google таблиці, у тому числі і порожні. В результаті у вас можуть з'явитися зайві прогалини в будь-якому місці отриманого текстового рядка.

На скріншоті вище ви бачите зайві прогалини на початку рядка в осередку D7.Це може створити вам додаткові проблеми при сортуванні чи пошуку потрібного значення за допомогою ВПР.

Функція TEXTJOIN для об'єднання осередків

Якщо в деяких стовпцях є порожні комірки, є ще одна функція, яка може вас зацікавити.

TEXTJOIN не тільки об'єднує осередки в Google Таблицях, але й ігнорує порожні:

TEXTJOIN(розділювач; виключити_порожні; текст1; [текст2; …])

Стосовно нашого прикладу:

Ось як це працює:

Вкажіть бажаний роздільник як перший аргумент - у моєму випадку це пробіл (" ").
Введіть ІСТИНА як другий аргумент, щоб пропустити порожні осередки, або БРЕХНЯ, щоб включити їх у отриманий текстовий рядок.
Вкажіть діапазон комірок для об'єднання.

Як бачите, тепер в осередку D7 немає зайвих прогалин, оскільки порожній осередок при об'єднанні був проігнорований.

Оскільки методи об'єднання дуже схожі, вважаю, що вам буде корисна ця стаття: Об'єднання осередків без втрати даних в Excel

Що важливо пам'ятати при об'єднанні осередків

Об'єднання осередків у Google Таблицях може значно покращити візуальне сприйняття ваших даних. Однак неправильне використання цієї функції може призвести до плутанини та фрагментації інформації. Перше правило – уникайте об'єднання осередків без потреби. Переконайтеся, що об'єднані осередки служать чіткої мети, як-от створення заголовків чи угруповання даних, щоб читачі могли швидко і легко сприймати інформацію.

Об'єднання осередків у Google Таблиці може значно вплинути на роботу формул і посилань, що є основною проблемою для користувачів.

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

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

Якщо ви використовуєте стандартний інструмент об'єднання комірок, які містять формули, необхідно змінити їх так, щоб вони посилалися на потрібні дані після об'єднання.

Наприклад, ви можете використовувати функцію СЧЕПИТИ для об'єднання текстових значень в одному осередку, зберігаючи при цьому доступ до вихідних даних.

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

Поширені помилки при об'єднанні осередків та як їх уникнути

Об'єднання осередків у Google Таблицях може суттєво спростити візуалізацію даних, проте багато користувачів стикаються з певними проблемами в процесі.

Одна з найбільш поширених помилок полягає в втраті даних, що відбувається, якщо в комірках, що об'єднуються, міститься інформація. При виконанні операції об'єднання Google Таблиці зберігають лише дані з комірки, що знаходиться у верхньому лівому куті діапазону. Всі інші дані губляться. Щоб уникнути цієї проблеми, варто звернути увагу на попередню перевірку і необхідно перемістити всі важливі дані в один осередок перед об'єднанням.

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

Також серед частих помилок можна виділити недостатнє оформлення об'єднаних осередків. Особливо це стосується великих таблиць, де об'єднання осередків без чіткої структури може призвести до плутанини. Рекомендується використовувати об'єднання осередків тільки в тих випадках, коли це дійсно необхідно для покращення читання таблиці. Таким чином, використання об'єднання має бути свідомим, щоб не виникали труднощі при навігації та роботі з даними.

Таким чином, можна ефективно об'єднувати осередки в Google Таблиці, зберігаючи при цьому всі важливі дані.

Функція CONCATENATE (ЗЧЕПИТИ) у Google Таблиці

Функція CONCATENATE (ЗЧЕПИТИ) в Google Таблиці використовується для об'єднання текстових рядків з різних осередків в одну. Вона приймає як аргументи комірки або текстові значення та об'єднує їх в один рядок.

Функція полегшує роботу з даними, коли необхідно об'єднати текстові значення з різних джерел в одну комірку. Це може бути корисним при створенні формул, складанні звітів або форматуванні тексту в осередках.

Приклад використання функція CONCATENATE (ЗЧЕПИТИ) у Google Таблиці

В якості аналога функцію CONCATENATE (СЧЕПИТИ) можна використовувати оператор конкатенації (об'єднання) "&", який надає більш компактний синтаксис, що читається.

Синтаксис

=CONCATENATE(рядок_1; [рядок_2; …])

=CONCATENATE(string1; [string2; …])

=ЗЧЕПИТИ(рядок_1; [рядок_2; …])

Список аргументів

Рядок_1 (string1) - Текстовий рядок, число або посилання на комірку.

[Рядок_2; …] ([string2; …]) - Текстовий рядок, число або посилання на комірку.

Значення, що повертається

Повертає текстовий рядок, що є об'єднанням всіх переданих їй аргументів.

приклад

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

Пишемо нашу формулу: =CONCATENATE(A2;" ";B2) і простягаємо вниз.

Використання функція CONCATENATE (ЗЧЕПИТИ) у Google Таблиці на реальному прикладі з життя

Загалом, функція CONCATENATE (ЗЧЕПИТИ) корисна для простих завдань щодо об'єднання текстових значень, але також не варто забувати про оператора конкатенації (об'єднання) "&". Функціонал у них однаковий.

25+ функцій Google Таблиць для роботи з текстом

Google Таблиці (Google Spreadsheets) - табличний, а не текстовий редактор, проте там часто доводиться мати справу саме з текстом. Розповідаємо про відповідні функції.

ДЛСТР (LEN)

ДЛСТР (LEN) – функція для обчислення довжини рядка.Стане в нагоді, якщо працюєте в Google Sheets з контент-планами, текстами оголошень, метатегами - відразу можна перевірити, відповідає текст потрібним критеріям чи ні.

Синтаксис простий, аргумент один - текстовий рядок (можна вказати прямо у формулі або взяти з комірки). Наприклад, =ДЛСТР("Блог CyberMarketing про інтернет-маркетинг") поверне значення 42. Враховуються всі символи, включаючи прогалини та переклади рядка.

Якщо потрібно перевірити тайтли сторінок, а самі вони поки невідомі, можна вкласти в LEN функцію IMPORTXML: =ДЛСТР(IMPORTXML(B2;"//title")) В даному випадку результатом стане довжина метатегу TITLE, отриманого для URL, що лежить в осередку B2.

Так працює IMPORTXML, вкладений у LEN

Але взагалі вирішувати такі завдання за допомогою парсера Click.ru набагато простіше та зручніше.

РЯДКОВИЙ (LOWER), ПРОПИСН (UPPER)

РЯДКОВИЙ (LOWER) наводить всі символи до нижнього регістру, а ПРОПИСН (UPPER) - навпаки, до верхнього. Обидві текстові функції приймають один аргумент - рядок у лапках або комірку з текстом. Виходить, конструкція =ПРОПИСН(СТРОЧН("CYBERMARKETING")) виведе той самий рядок - "CYBERMARKETING".

Але практично часто можна обійтися без цих функцій. Для пошуку без урахування регістру є ПОШУК (SEARCH) - про неї трохи пізніше. А, наприклад, QUERY, яку ми розбирали в попередній статті, має свої вбудовані функції — upper() і lower() — для приведення рядків до єдиного регістру.

ЗЧЕПИТИ (CONCATENATE), JOIN, TEXTJOIN

Усі три функції об'єднують кілька рядків на одну. Працюють схожим способом, але є деякі відмінності.

CONCATENATE (СЧЕПИТИ) дослівно зчіплюють кілька рядків між собою по черзі. Синтаксис: =ЗЧЕПИТИ("Hello";", ";"World";"!")

Найпростіший приклад використання CONCATENATE.Але такий спосіб підійде, мабуть, тільки для німецької мови.

Щоб вони не злипалися між собою, доведеться щоразу додавати між ними прогалину (або інший символ). Наприклад: =ЗЧЕПИТИ(A15;" ";B15;" ";C15;" ";D15) Або створювати таблицю, де в одному стовпці потрібні слова, в іншому сполучники. (Якщо в параметрі вказаний діапазон – мінімум по 2 осередки у висоту та ширину – функція буде об'єднувати значення по рядках.)

У такому разі тільки треба залишити останній осередок порожнім — інакше наприкінці створеного рядка буде зайва прогалина.

Як ви розумієте, ЗЧЕПИТИ далеко не найзручніша функція. JOIN дозволяє відразу задати єдиний роздільник для всіх рядків в масиві/діапазоні: =JOIN(" ";A16:A19) дасть такий же результат, як на попередньому скріншоті – при цьому не треба турбуватися за можливий зайвий символ наприкінці рядка.

У JOIN можна спокійно поєднувати відразу кілька масивів даних, наприклад: =JOIN("&";;) А якщо не вказати роздільник, то все буде працювати так само, як у CONCATENATE.

Наступна функція підійде краще, якщо в масиві/діапазоні трапляються порожні рядки - адже в JOIN вони створюють зайві прогалини. А TEXTJOIN робить все те саме, тільки є додатковий аргумент, який вказує: включати порожні рядки чи ні. Синтаксис: =TEXTJOIN(" ";ІСТИНА;A16:B20), де перший параметр передає пропуск як роздільника, другий говорить, що потрібно ігнорувати порожні рядки, а третій - дає діапазон значень для з'єднання.

Тут все правильно з'єднується - нічого зайвого

До речі, крім функцій можна використовувати такий оператор як '&'. Наприклад, ="Cyber"&"Marketing" спокійно спрацює та видасть "CyberMarketing".Дуже зручна фішка для створення будь-яких динамічних таблиць (дашбордів/конструкторів), де потрібно кастомізувати запит для QUERY.

А докладніше про те, що робити з ключовими словами, ви зможете дізнатися за допомогою статей, вебінарів, курсів з SEO від CyberMarketing.

СЖПРОБІЛИ (TRIM)

Якщо проблема з пробілами все ж таки є, її допоможе вирішити спеціальна функція - СЖПРОБІЛИ (TRIM). Вона чистить їх на початку і в кінці тексту, а також прибирає символи, що повторюються, наприклад, подвійні пробіли.

Синтаксис простий і зрозумілий, аргумент один - рядок цілком або посилання на комірку: =СЖПРОБІЛИ(" Hello, World! ")

Так працює СЖПРОБІЛИ

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

ЛЕВСІМВ (LEFT), ПРАВСІМВ (RIGHT), ПСТР (MID)

Усі три функції витягують із рядка один або кілька символів: перша бере їх зліва, друга - праворуч, третя - прямо з середини. Приклади:

=ЛЕВСИМВ("Привіт, мир!";6) - Тут вийде просто "Привіт".
=ПРАВСИМВ("Привіт, мир!"; 4) - Тут від рядка залишиться тільки "світ!".
=ПСТР("Привіт, мир!"; 4; 3) - Виведе лише "віт".

З ЛЕВСІМВ і ПРАВСИМУ все має бути і так зрозуміло (до речі, останнім параметром може бути нуль, тоді функція просто поверне порожній рядок). А ось ПСТР працює так: другим аргументом приймає номер символу, з якого потрібно почати вилучення підрядка, а третім — довжину підрядки, що витягується. (Важливо: відлік починається з 1, а не з нуля.)

Звичайно, в роботі рідко буває заздалегідь відомо, де в якому рядку і скільки символів треба відступити. Як правило, такі функції використовують у поєднанні з іншими, наприклад, ДЛСТР та ПОШУК/ЗНАЙТИ.

SPLIT

SPLIT поділяє текст — за одним або декількома символами-розділювачами — і виводить отримані фрагменти до різних осередків. Самі символи у своїй не повертає. Простий приклад: =SPLIT(A10;"/")

1. Текст, який потрібно поділити на частини. Рядком або посиланням на комірку.

2. Символ(и) для поділу - теж рядком, у лапках.

3. Тип роздільника (необов'язково). За промовчанням ІСТИНА — функція розділяє текст за кожним символом, зазначеним у другому параметрі. Якщо поставити брехню, то поділ відбуватиметься лише по всій послідовності символів.

Різниця між двома способами поділу тими самими роздільниками. У другому випадку взагалі немає ніякого поділу, тому що в URL слеш і точка не зустрічаються поспіль.

4. Також необов'язковий параметр, який визначає, чи потрібно видаляти порожні комірки після поділу. За умовчанням ІСТИНА — два послідовні роздільники вважатимуться одним. Якщо брехня, то між цими роздільниками буде створено порожній осередок.

Мабуть, це не стане в нагоді, SPLIT і так займає занадто багато осередків

Головний недолік - безліч зайвих «технічних» осередків, які можуть заважати, може знадобитися приховувати їх. Видобувати окремі фрагменти тексту можна іншими способами.

REGEXMATCH, REGEXEXTRACT, REGEXREPLACE

Всі ці три функції працюють із регулярними висловлюваннями — спеціальною мовою для гнучких маніпуляцій із текстами. REGEXMATCH шукає збіги, REGEXEXTRACT витягує за шаблоном потрібний фрагмент, а REGEXREPLACE замінює одну частину тексту іншу. Синтаксис схожий: перший аргумент - текст, а другий - саме регулярне вираження; у REGEXREPLACE є ще третій текст, який потрібно вставити.

=REGEXEXTRACT("https://www.cybermarketing.ru/video-courses/promo/poiskovoe-prodvizhenie.html";"[^/]+$") - результатом буде "poiskovoe-prodvizhenie.html".
=REGEXMATCH("[email protected]";"^([a-z0-9_-]+\.)*[a-z0-9_-]+@[a-z0-9_-]+(\ .[a-z0-9_-]+)*\.[a-z]$") — ІСТИНА, оскільки у рядку коректна адреса електронної пошти.
=REGEXREPLACE("Google — це один з найвідвідуваніших сайтів у світі, другий за популярністю пошуковик Рунету та лідер мобільного трафіку. Тому немає жодних сумнівів, що контекстна реклама Google Adwords — ефективний канал для просування бізнесу. Google Адвордс дозволить охопити велику аудиторію пошуку Google та користувачів мобільних пристроїв на базі Android.";"(Adwords)|(Адвордс)|(Едвордс)";"Ads") — тут у тексті скрізь правильно написано назву сервісу.

Взагалі регулярні вирази – тема для окремого матеріалу. Спочатку здається, що дуже складно, але коли починаєш розбиратися, стає набагато легше. Практикуватися можна не лише в Google Docs, а й на спеціальних тренажерах, наприклад regex101.com. Але майте на увазі, що таблиці підтримують не всі можливості, які є в PCRE2 (PHP) або ECMAScript (JavaScript). Вся інформація про синтаксис RE2 (Google).

UNIQUE, COUNTUNIQUE

Функція UNIQUE бере лише ті рядки, які ніколи не повторюються, тобто дозволяє почистити набір даних від дублікатів. Дуже корисна штука у SEO, PPC, веб-аналітиці. Унікальні рядки повертаються у тому порядку, в якому розташовуються в діапазоні/масиві.

Синтаксис: =UNIQUE(A2:A100) або =UNIQUE() - Тут у стовпець вийдуть по порядку 5 елементів цього масиву, а не 6, тому що два з них неунікальні.

Якщо потрібно не виводити, а лише підрахувати кількість унік, є функція COUNTUNIQUE. До неї можна передавати набір із довільного числа аргументів: значень та діапазонів. Синтаксис: =COUNTUNIQUE(1; 1; 2; 3; 5; 8; 13; A2; B6:B9)

ПОШУК (SEARCH), ЗНАЙТИ (FIND)

Обидві функції роблять те саме: повертають порядковий номер символу, у якому запит вперше зустрічається у тексті. Першим аргументом передаємо, що шукати, другим – де шукати, третім – з якого символу шукати (пошук йде з початку, враховуються всі символи, у тому числі прогалини). (На відміну від ЛЕВСІМВ та ПРАВСІМВ, не можна передати нуль як останній аргумент.)

Різниця лише в тому, що ЗНАЙТИ (FIND) враховує регістр, а пошук (SEARCH) - ні. Приклади:

=ЗНАЙТИ("!";"Привіт, мир!";1) - результатом буде 12.
=ЗНАЙТИ("Привіт";"Привіт, мир!";1) - Тут вийде помилка #ЗНАЧ, оскільки функція чутлива до регістру.
=ПОШУК("привіт";"Привіт, мир!";1) - Тут функція спрацює нормально, повернеться.
=ПОШУК(",";"Привіт, мир!";4) — тут на виході буде 7. (Важливо: хоча пошук починається з 4 символів, функція поверне позицію, де відбувається збіг, саме з початку рядка — а не з того символу, з якого починається пошук.)
=ПОШУК("р";"Привіт, мир!";1) - Відповідь 2, тому що враховується тільки перший збіг, який ближче до лівої частини, початку рядка. Якщо трапляється далі повторно, вже ніяк не впливає.

Результати виконання функції — позиції символів/підрядків — стануть у нагоді, наприклад, для подальшої роботи з ЛЕВСІМВ, ПРАВСІМВ або ПСТР. Але самі по собі вони не знадобляться, якщо потрібно просто перевірити: чи є в тексті потрібне слово чи ні. TRUE або FALSE замість марних цифр допоможе отримати ПОМИЛКА (ISERROR) (або схожа функція ОСЛИПОМИЛКА) у поєднанні з функціями IF або IFS (іноді ще потрібен NOT). Наприклад:

=IF(ISERROR(SEARCH("paid";"https://www.cybermarketing.ru/video-courses/paid/prodvinutyj-kurs-seo.html";1));"Курс безкоштовний";"Курс платний" ) - тут функція поверне саме "Курс платний".
=IFS(НЕ(ПОМИЛКА(ЗНАЙТИ("paid";"https://www.cybermarketing.ru/video-courses/promo/kontekstnaya-reklama.html"))));"Курс платний";НЕ(ЄПОМИЛКА(ЗНАЙТИ) ("promo";"https://www.cybermarketing.ru/video-courses/promo/kontekstnaya-reklama.html"))));"Курс безкоштовний") - а тут вийде "Курс безкоштовний" (для двох варіантів, звичайно, занадто складно, але для великої кількості роздоріжжя - цілком).

ПІДСТАВИТИ (SUBSTITUTE)

ПІДСТАВИТИ (SUBSTITUTE) — текстова функція Google Таблиць, яка спочатку знаходить у тексті потрібний фрагмент, потім замінює його на інший (як зазвичай можна відправити рядки як є або комірки з відповідними даними). Аргументи:

Текст, для якого потрібні пошук та заміна.
Рядок, який потрібно знайти та видалити.
Рядок, який потрібно підставити замість.
Порядковий номер входження рядка, який потрібно замінити (необов'язково). (За замовчуванням усі збіги замінятимуться.)

На злобу дня: =ПІДСТАВИТИ("Привіт, мир!";"світ";"карантин") - "Привіт, карантин!". Ще приклад: =ПІДСТАВИТИ("Привіт, світ!";"р";"г";2) - "Привіт, мить!" ('р' зустрічається раніше, але спеціально зазначено, що слід брати другий символ по рахунку).

Важливо: функція SUBSTITUTE не розрізняє, чи є рядок окремим словом або частиною іншого. Заміна буде проводитись у будь-якому випадку. У складніших кейсах краще використовувати функції REGEX - для роботи з мовою регулярних виразів.

DETECTLANGUAGE та GOOGLETRANSLATE

DETECTLANGUAGE вміє визначати мову тексту, а GOOGLETRANSLATE - перекладати текст з однієї мови іншою.

DETECTLANGUAGE приймає один аргумент - текст чи діапазон.(Працює тільки з одновимірним стовпцем, при передачі діапазону виду A2:B8 видасть помилку — обійти можна тільки через масив.) Якщо в діапазоні будуть тексти кількома мовами, функція визначить мову лише першого фрагмента, що попався.

GOOGLETRANSLATE працює з трьома параметрами (два останні необов'язкові):

Текст, який потрібно перекласти.
Мова оригіналу в такому ж форматі, яким оперує DETECTLANGUAGE.
Мова перекладу – аналогічно – двома літерами.

З діапазонами не вийде, тільки з окремими осередками/рядками.

ТЕКСТ (TEXT) І ЗНАЧЕНИЙ (VALUE)

Функція ТЕКСТ (TEXT) перетворює числовий формат на текстовий — це може знадобитися, наприклад, для динамічних таблиць з використанням QUERY, красивого представлення даних у звітах. ', '*', а також дробові формати не підтримуються.) Приклади:

=ТЕКСТ("21/12/2012";"mmm d yyyy") - Тут вийде "груд. 21 2012".
=ТЕКСТ("199,99"; "#,###") - Тут результатом буде 200.
=ТЕКСТ(100,789; "$0,00") — округлення та подання у доларах — $101.
=ТЕКСТ("10000001"; "#,##0") - "10 000 001".

(Всі варіанти можна переглянути у довідці, а також у розділі «Формат» → «Числа» → «Інші формати».)

ЗНАЧЕНИЙ (VALUE) робить зовсім протилежне - перетворює текст чи дату на число. На вхід приймає єдиний аргумент - рядок.Зміна формату може знадобитися, наприклад, коли ми витягуємо дані з тексту за допомогою REGEXEXTRACT, а потім їх потрібно використовувати для функцій підсумовування, підрахунку середнього арифметичного і т.п.

ЕТЕКСТ (ISTEXT)

ЕТЕКСТ (ISTEXT) перевіряє, чи є в осередку текст чи ні. Якщо там текстове значення, повертає ІСТИНА, якщо знайде число чи порожню комірку — БРЕХНЯ. Так:

=ЕТЕКСТ("123") - TRUE, тому що дані, укладені в лапки, передаються як рядок.
=ЕТЕКСТ(123) — FALSE, тому що цифри передаються без лапок.
=ЕТЕКСТ("") - теж TRUE, рядок порожній, але все-таки це рядок.

Ця функція найчастіше використовується в умовних конструкціях (IF, IFS).

Збіг (EXACT)

СПІД (EXACT) - спеціальна функція Таблиць для порівняння двох рядків: якщо вони повністю збігаються, повернеться TRUE, якщо ні - FALSE. Важливі і прогалини, і регістри. Наприклад, =СПІД("Cybermarketing";"CyberMarketing") видасть брехня, тому що по одному символу вони відрізняються.

Якщо регістр не важливий, набагато простіше використовувати звичайні оператори, наприклад: =ЯКЩО("CyberMarketing"="Cybermarketing";"Збігаються";"Не збігаються") - Тут буде "Збігаються".

Гіперпосилання (HYPERLINK)

Гіперпосилання (HYPERLINK) створює в осередок гіперпосилання. Перший аргумент – повний URL, другий (необов'язковий) – текст посилання.

Якщо протокол не вказано, за промовчанням використовується "http://". Якщо текстом посилання вказано порожній рядок "", осередок відображається порожнім, але посилання все одно працюватиме.

Насправді ця функція потрібна рідко, тільки якщо важливо приховати цілий URL за анкором. В інших випадках Google Таблиці автоматично роблять адреси сторінок/сайтів клікабельними.

А про те, як правильно працювати з посиланнями на сайті, ви зможете дізнатися за допомогою статей, вебінарів, курсів з SEO від CyberMarketing.

Як зчепити в гугл таблиці