☰

Как автоматически сделать копии столбцов в Excel?

tag-icon • tag-icon microsoft-excel
Как автоматически сделать копии столбцов в Excel?

Каждый день мне приходится брать необработанные данные, которые содержат более 40 столбцов и растущее количество строк (сейчас 2,5 миллиона), и подготавливать их в соответствии с определенным форматом.


Предположим, что у меня есть 5 столбцов необработанных данных:

имя - фамилия - год - страна - пол


Мне нужно следующее:

фамилия - пол - страна

это значит, что мне нужны только некоторые столбцы и в другом порядке.

решение1

Повторите последнее действие, нажав «Повторить» на панели быстрого доступа. Сочетание клавиш Нажмите CTRL+Y или F4.

решение2

Откуда берутся 2,5 миллиона строк? Не из Excel, потому что лист Excel может содержать только 1 048 576 строк.

Если вы используете модель данных в Excel 2010 или 2013, вы можете обрабатывать больше, чем лимит Excel, но данные не будут видны на листе. Вы можете использовать Power Query для импорта больших объемов данных (больше, чем лимит листа Excel) из разных источников в модель данных. Power Query также можно использовать для преобразования исходных данных, например, для удаления столбцов и изменения порядка столбцов.

Вы можете загрузить Power Query как надстройку Excelздесь. Он работает с большинством версий Excel 2010 и 2013 и будет встроен в Excel 2016.

решение3

Предположим, что ваши иллюстративные необработанные данные из 5 столбцов находятся в Sheet1, строка 1 содержит заголовкиимя - фамилия - год - страна - пол, а данные начинаются в строке 2.

Давайте теперь поместим 3 нужных вам заголовка в строку 1 Sheet2, вставим следующую формулу в ячейку A2 и перетащим ее вниз/поперек:

=INDEX(Sheet1!$A2:$E2,MATCH(A$1,Sheet1!$A$1:$E$1,0))

Это горизонтальный поиск, который для каждой строки выполняет поиск по каждому столбцу, чтобы вернуть значение в правом столбце.

Если это занимает слишком много времени (кажется, для вас важно время расчета), вы можете просто посмотреть вверх ( MATCH) правильный столбец один раз, а затем indexвыйти из него. Добавьте дополнительную строку из 3 значений, скажем, в строку 2 Sheet2с =MATCH(A$1,Sheet1!$A$1:$E$1,0)(и предположим, что ваши данные Sheet1теперь начинаются в строке 3), теперь вставьте =INDEX(Sheet1$A3:$E3,A$2)и Sheet2!A3заполните по горизонтали + вниз.

Связанный контент