☰

Нужно добавить недостающие номера идентификаторов итераций в ряд в тексте или Excel. Как это сделать без кодирования?

tag-icon • tag-icon microsoft-excel regex
Нужно добавить недостающие номера идентификаторов итераций в ряд в тексте или Excel. Как это сделать без кодирования?

У меня есть текстовый, CSV или Excel-файл, который выглядит так:

||--ID-----||--Name--||--Date of birth--||
    1            Jo          1/1/11
    32           Mo          2/2/12
    3382         Ro          3/3/10
    21,252       Do          4/4/09

Реальный набор содержит 280 000 из 1 000 000 строк. Мне нужно как-то добавить все недостающие номера ID (которые все последовательны +1 итерация) и просто пустые поля для имени и даты рождения. Так что я получу что-то вроде:

||--ID-----||--Name--||--Date of birth--||
    1,           "Jo",     "1/1/11"
    2,           "",         ""
    3,           "",         ""
    4,           "",         ""

до 32 и так до тех пор, пока не будет каждое целое число. Есть ли простой способ сделать это? без написания блока кода в цикле? Что-нибудь из Excel, какого-нибудь приложения или любого трюка с текстовым редактором Windows было бы оценено.

Редактировать: Пожалуйста, не обращайте внимания на запятые, кавычки и т. д. Единственное, что имеет значение, — это отсутствующие идентификационные номера.

решение1

Один из подходов может быть следующим:

  1. импортируйте CSV-файл в Excel как лист 1.
  2. На листе 2 составьте полный список идентификационных номеров с помощью формулы, например, a2: =a1+1, а затем скопируйте ее вниз.
  3. используйте формулы vlookup в двух других столбцах, ссылаясь на вашу таблицу на листе 1. например, b2: =VLOOKUP(A2,Sheet1!A2:C13,2,false)и c2:, =VLOOKUP(A2,Sheet1!A2:C13,3,false)или для точного соответствия вашему запросу давайте заключим его в оператор IFNA, чтобы вернуть "", если значение отсутствует. b2: =IFNA(VLOOKUP(A2,Sheet1!A2:C13,2,FALSE),"")и c2: =IFNA(VLOOKUP(A2,Sheet1!A2:C13,3,FALSE),"")(теперь эти формулы можно скопировать вниз по столбцу.)
  4. После заполнения сохраните лист 2 как CSV.

Примечание: для правильной работы таблицу на листе 1 необходимо отсортировать по столбцу 1. Из предоставленной информации следует, что так и есть, но если это не так, отсортируйте информацию по первому столбцу.

С миллионом строк это будет работать очень медленно на многих компьютерах, я упустил это из виду, прежде чем писать формулы. Я сделал что-то похожее однажды с большим набором данных, отключив auto relcalc, чтобы все формулы были на месте, а затем сделал ручной пересчет. Это заняло часы, но было выполнено правильно.

решение2

Я бы решил эту проблему с помощью надстройки Power Query.

Я создал прототип, который вы можете просмотреть или загрузить — это «Демонстрация Power Query — Добавление отсутствующих идентификационных номеров в series.xlsx» на моем One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

Для этого нужно сделать несколько шагов и немного кодирования на языке Power Query (M) для вызова функции List.Numbers (она не отображается в пользовательском интерфейсе Power Query). Но это всего лишь одна простая строка кода — остальное можно построить, щелкая в Power Query.

По сути, мой метод заключался в использовании List.Numbers для создания таблицы идентификационных номеров, а затем я добавил Merge для получения столбцов из входных данных (где есть идентификационный номер).

Документация по List.Numbers находится здесь:

http://office.microsoft.com/en-au/excel-help/list-numbers-HA104111648.aspx?CTT=5&origin=HA104122363

Огромная благодарность Мэтту Массону за технику «Настройки».

http://www.mattmasson.com/2014/04/defining-configurable-settings-for-your-queries/

Примечание. Power Query может считывать данные напрямую из CSV-файла, который я бы использовал в качестве источника для запроса «Входные данные». Вероятно, проще всего удалить этот запрос, а затем создать новый из CSV-файла и назвать его «Входные данные». Снимите флажок «Загрузить на лист», чтобы сэкономить ресурсы.

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