Excel: как объединить несколько строк разреженных данных в одну с помощью столбца группировки

Question

Хорошо, я хочу повернутьтекущий макетвэтот макет

Как видите, я хочу избавиться от повторяющихся заголовков и избавиться от пустых ячеек. Я думаю создать формулу, которая для каждых семи строк после первой избавлялась бы от заголовков и перемещала бы другие ячейки вверх, но я не уверен, как это написать, или вообще возможно ли сделать что-то подобное в Excel? Там будут сотни строк, поэтому я не могу сделать это вручную. Я использую Excel 2016 на Mac.

Заранее спасибо!

Answer 1

Выберите любую ячейку в ваших данных. Используйте «Данные»> «Получить и преобразовать данные»> «Из таблицы/диапазона», чтобы создать PowerQuery.

В редакторе Power Query выберите «Главная»>«Преобразование»>«Группировать по» и настройте его следующим образом:

Нажав «ОК», вы получите нужные вам результаты.

Просто нажмите «Главная»>«Закрыть и загрузить», чтобы вернуть данные в книгу.

РЕДАКТИРОВАТЬ:

Чтобы сделать это с помощью формул, вы можете использовать что-то вроде этого:

Это формула в ячейке J2 на изображении выше.

=INDEX(B$2:B$15,SUMPRODUCT(N($A$2:$A$15=$I2),N(NOT(ISBLANK(B$2:B$15))),ROW(B$2:B$15))-1,1)

эта SUMPRODUCTчасть в основном извлечет номер строки непустой ячейки в столбце B, где столбец A соответствует заголовку в столбце I. INDEXзатем извлечет значение из этой строки.

Пожалуйста, отрегулируйте вертикальный размер диапазонов в соответствии с вашими данными, но убедитесь, что $s такие же, как указано выше, прежде чем перетаскивать их по горизонтали и вертикали, чтобы заполнить консолидированную таблицу, как я сделал в J2:O3 выше.

Answer 2

Сначала удалите пустые ячейки в столбцах B:G:

1. Выберите столбцыB:G
2. На Homeвкладке выберите Find and SelectзатемGo To Special
3. Выберите Blanksи нажмите OK. Обратите внимание на выбранные пустые ячейки.
4. На Homeвкладке выберитеDelete

Далее расположите заголовки строк в столбцах A:

1. Выберите столбецA
2. Форма Dataвкладки выберитеRemove Duplicates
3. Выбирайте Continue with current selectionи нажимайтеEnter

Теперь ваши данные должны быть организованы желаемым образом.

Если вы когда-нибудь перейдете на Excel 365, это можно будет сделать очень быстро с помощью новых динамических формул Excel:

1. В ячейке I1введите=UNIQUE(FILTER(A:A,A:A<>""))
2. В ячейке J1введите=FILTER(B:B,B:B<>"")
3. Копировать ячейку J1в ячейкиK1:O1