Каждый день мне приходится брать необработанные данные, которые имеют более 40 столбцов и увеличивают количество строк (сейчас 2,5 миллиона), и готовить их в соответствии с определенным форматом.
Предположим, у меня есть 5 столбцов в необработанных данных:
имя - фамилия - год - страна - пол
что мне нужно это:
фамилия - пол - страна
а это значит, что мне нужны только некоторые столбцы в другом порядке.
Предположим, что ваши иллюстративные необработанные данные из 5 столбцов находятся на Sheet1 1, строка 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 и заполните через + вниз.
Повторите последнее действие, нажав кнопку «Повторить» на панели быстрого доступа. Сочетание клавиш Нажмите CTRL+Y или F4.
Откуда берутся 2,5 миллиона строк? Не Excel, потому что лист Excel может иметь только 1 048 576 строк.
Если вы используете модель данных в Excel 2010 или 2013, вы можете обрабатывать больше, чем предел Excel, но данные не будут видны на листе. Вы можете использовать Power Query для импорта больших объемов данных (превышающих лимит листа Excel) из разных источников в модель данных. Power Query также можно использовать для преобразования исходных данных, например, для удаления столбцов и изменения порядка столбцов.
Вы можете скачать Power Query в виде надстройки для Excel здесь. Он работает с большинством выпусков Excel 2010 и 2013 и будет встроен в Excel 2016.