В цій статті розглянемо наступну задачу. Візьмемо Excel файл, у якого в рядках в першій комірці перелічені країни, а в другій – провайдери стільникового зв’язку, доступні в кожній з цих країн. Необхідно таким чином виконати об’єднання рядків, щоб кожній країні було відведено тільки один рядок, і в другій комірці кожного рядка через кому були наведені доступні в цій країні провайдери.
Перед початком роботи впевніться, що в першому рядку таблиці ви маєте заголовки стовпчиків – це полегшить подальшу роботу з таблицею.
1. Сортування
Першим кроком відсортуємо файл по обом колонкам:
2. Підготовка до об’єднання рядків
Створимо нову колонку “Providers”, яка буде містити об’єднані дані:
Для об’єднання даних по кожній країні будемо використовувати просту функцію IF.
В першій після заголовку комірці нової колонки (C2) пишемо:
=IF(A2=A1;C1&", "&B2;B2)Цей вираз перевіряє, чи співпадає значення в першій комірці поточного рядка з першою коміркою попереднього рядка (тобто чи відносяться обидва ці рядки до однієї країни).
- Якщо вираз повертає значення true, тобто країна одна й та сама, функція приєднує інформацію про провайдера з поточного рядка до об’єднаної інформації з попереднього рядка.
- Якщо вираз повертає значення false, тобто країна в поточному рядку відмінна від переднього, функція повертає інформацію тільки про поточного, тобто першого в списку, провайдера.
Тиснемо Enter і получаємо в цій комірці копію колонки з першим провайдером першої країни.
Виділіть комірку, яку ви тільки що відредагували, наведіть курсор на нижній правий кут, поки не побачите курсор в формі чорного хрестика, і двічі клацніть. Це розповсюдить функцію на всі заповнені рядки.
Як бачите, в межах однієї країни кожна наступна комірка містить перелік всіх попередніх провайдерів і поточного, розділені комами.
3. Видалення зайвих даних
Тепер нам потрібно зберегти останню комірку кожної країни, оскільки вона містить повний список даних.
Створимо ще одну “службову” колонку, в якій будемо визначати, яка комірка є останньою.
В першій комірці нової колонки пропишемо функцію:
=IF(A2<>A3;"LAST";"")Розповсюдимо формулу на всі рядки. Як бачимо, в деяких рядках з’явилось слово LAST.
Тепер нам потрібно відфільтрувати тільки рядки, в яких є слово LAST. Виділяємо заповнені колонки і вмикаємо фільтр.
Знімаємо галочку з порожніх клітинок, залишаючи відміченими тільки LAST.
На цьому етапі доцільно приховати колонки Provider та Check , оскільки вони нам більше не потрібні.
Поточний документ вже візуально відображає бажаний результат – перелік країн та доступних провайдерів, перелічених через кому, однак він також містить приховані непотрібні дані. На цьому етапі можна перенести дані, які наразі відображаються на сторінці в колонках Country та Providers, на окрему сторінку чи в новий документ.
Виділіть колонки, натисніть Ctrl + C, створіть новий аркуш в вашій книзі та вставте скопійоване туди, використовуючи “Вставити значення”, щоб не прихопити з собою формули.
Висновок
Ми отримали скорочений файл, в якому комірки з ключовими значеннями не дублюються. При бажанні формули можна модифікувати, замінивши роздільник на пробіл чи крапку з комою.