5

У меня есть несколько электронных таблиц с информацией, которая мне нужна для консолидации, каждая с разными (и иногда частично совпадающими) частями окончательного набора данных, которые я надеюсь создать. Как правило, объединение электронных таблиц с похожими данными легко сделать, сбросив все данные на один лист и используя команду «Удалить дубликаты». Однако я хочу убедиться, что оставленные позиции представляют собой последнюю версию дубликатов.

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

  • Поле уникального идентификатора (UID), которое идентифицирует тему позиции и будет использоваться для определения дубликатов между входными листами.
  • Поле состояния (STATUS), которое будет содержать информацию об элементе, вызываемом в поле уникального идентификатора.
  • Поле даты (DATE), которое будет содержать дату, в которую импортированные данные были изначально записаны.

Что мне нужно, чтобы мой вывод содержал только одну строчку для каждого UID и имел данные из поля STATUS, которые соответствуют самой последней DATE для этого UID из входных листов.

Какой самый простой способ сделать это в Excel?

|источник

6 ответов6

5

Я не знаю, гарантированно ли это работает, но мне кажется, что это работает для меня (в очень мелкомасштабных тестах в Excel 2007): возьмите объединенную таблицу данных и отсортируйте ее в обратном порядке по ДАТЕ, чтобы получить самые новые строки выше старых.  Затем удалите дубликаты.

Этот сайт подтверждает следующее: «Когда Excel сканирует таблицу, он удаляет любую последующую запись с тем же идентификатором продукта, что и более ранняя запись, даже если остальные данные отличаются».

|источник
3

Вот решение, состоящее из нескольких шагов, при условии, что вы можете сделать это вручную, и вам не нужно ни одного полностью автоматизированного решения: (и если вы это сделаете, я уверен, что вы можете взять его отсюда ...)

  1. Excel не является базой данных.
  2. Сбросить все данные на один лист. (Для примера, я предполагаю, что у вас есть UID в столбце A, DATE в столбце B и STATUS в C).
  3. На втором листе выполните команду « Удалить дубликаты» только для столбца UID. (например, копировать только отфильтрованные уникальные файлы или скопировать весь столбец, а затем выполнить стандартное удаление дубликатов).

  4. В столбце DATE добавьте следующую формулу Array *:

    {= MAX (IF (Технический паспорт!A: A = A1, DataSheet!В: В))}

    Это в основном выбирает самую последнюю дату для каждого UID. (Это для первой строки, конечно, убедитесь, что все остальные строки заполнены A1, A2, ... )

  5. В столбце СОСТОЯНИЕ добавьте следующую формулу массива:

    {= INDEX (IF (Технический паспорт!A: A = A1, IF (Технический паспорт!B: B = B1, информационные листки!C: C)), MATCH (TRUE, IF (Технический паспорт!A: A = A1, IF (Технический паспорт!В: В = В1, TRUE)), 0))}

(Снова обратите внимание на первый ряд, заполните остальные).

Этот более сложный, давайте разберем его:

IF(Технический паспорт!A:A = A1, IF(Технический паспорт!B:B = B1, информационные листки!С: С))

Эта формула массива просто выполняет эквивалент предложения SQL WHERE с двумя условиями: для всех строк, которые соответствуют как UID (столбец A), так и DATE (столбец B), вернуть значение строки в столбце C (STATUS).

MATCH(TRUE, IF(Технический паспорт!A:A = A1, IF(Технический паспорт!В: В = В1, TRUE)), 0)

Первая формула должна была быть достаточно хорошей, но, поскольку у нас нет способа извлечь только ненулевое (или ненулевое) значение, а в Excel нет формулы COALESCE, нам нужно немного прибегнуть к Косвенная.
Формула MATCH ищет массив, возвращаемый IF (те же условия, что и выше, но просто возвращает TRUE, если это совпадение), для первого значения TRUE. 3 параметр, 0, требует точного соответствия.
Эта формула просто возвращает индекс первой и единственной строки, которая соответствует предыдущим условиям (соответствует UID и DATE (которая была максимальной датой, соответствующей UID)).

{= ИНДЕКС (ЕСЛИ (см. Выше), МАТЧ (см. Выше))}

Теперь достаточно просто взять индекс совпадающей строки из MATCH и извлечь соответствующее значение STATUS из массива IF. Это возвращает единственное значение, ваш новый STATUS, который гарантированно (если вы сделали все эти шаги правильно) будет иметь самую последнюю дату для каждого UID.

6 Excel не является базой данных.

* FOOTNOTE: если вы не знакомы с формулами Array (хотя я думаю, что вы есть), посмотрите на это: в основном вы вводите исходную формулу, которая должна привести к массиву значений (без волнистости {}), затем нажмите CTRL+SHIFT+ENTER. Excel добавляет за вас волнистый {} и вычисляет все значения в виде массива.

* СНОСКА № 2: Серьезно, EXCEL НЕ БАЗА ДАННЫХ. ;-)

|источник
1

@AviD верен, поскольку Excel не является базой данных, но вы можете импортировать данные в другую электронную таблицу через источник данных Microsoft Query. Это немного уродливо, но даст вам доступ к SQL-выражению, которое должно позволить вам получить то, что вы хотите.

  1. В новой электронной таблице перейдите на вкладку Данные и в группе Получить внешние данные выберите Из других источников ... и Из Microsoft Query.

  1. Выберите файлы Excel и выберите сохраненные данные.
  2. Если вы получаете сообщение об ошибке, в котором говорится, что не удается найти видимые таблицы, просто нажмите кнопку «ОК» и в диалоговом окне «Параметры» выберите «Системные таблицы» из списка отображения. Это должно дать вам доступ к листам на вашем листе

  1. Добавьте в запрос столбцы UID, Status и Date

  1. Следующий... Следующий... Далее и выберите Просмотр данных или измените запрос в Microsoft Query и выберите Готово

  1. Теперь вы получаете рабочий лист, который выглядит как ранняя версия Access.

  1. Нажмите кнопку SQL, и вы получите доступ к самому запросу, который, я думаю, вам нужно изменить на что-то вроде следующего (используя GROUP BY и MAX, чтобы получить самую последнюю дату):

    ВЫБЕРИТЕ Sheet1$ .UID, Sheet1$ .Состояние, Макс (Sheet1$ .Последнее) ОТ C:\Users\rgibson\Desktop\Book8.xlsx .Sheet1$ Sheet1$ GROUP BY Sheet1$ .UID, Sheet1$ .Статус

    1. Вы можете закрыть запрос и выбрать, куда импортировать данные:

|источник
0

Если все остальное не удается, объедините все данные в один лист по дате (убедитесь, что они были отформатированы одинаково), затем скопируйте самую последнюю дату в новую электронную таблицу, а затем следующую самую последнюю дату и так далее. Перейдите на вкладку «Данные» и удалите дубликаты. Так как Excel сохраняет первую запись, введенную в электронную таблицу, и удаляет следующий дубликат, это должно работать.

|источник
0

Поверните таблицу данных. Поместите Уникальный идентификатор в строку и поле даты в значение и установите значение Макс. Это создаст таблицу из двух столбцов с UID и самой последней датой, связанной с этим UID. Отформатируйте столбец значений в сводной таблице так, чтобы он точно совпадал со значением поля даты на вкладке данных.

Затем я скопировал сводные данные в новую вкладку - я назвал ее "преобразователь даты" - и создал именованный диапазон для двух столбцов A и B, а также назвал именованный диапазон "date_selector", который можно называть как угодно. Я фактически установил диапазон в $ A:$ B, чтобы он был готов, если я вернусь и добавлю дополнительные строки позже.

Затем я вернулся к таблице данных и добавил два столбца. AV-поиск, который вводил UID и возвращал максимальную дату из диапазона "date_selector", а затем простой оператор if, чтобы сохранить строки, в которых дата из диапазона "date_selector" соответствует дате в записи.

Мой уникальный идентификатор в столбце M Дата в столбце H Значение даты VLOOKUP находится в столбце A

VLOOKUP = VLOOKUP(M2, date_resolver, 2, FALSE)

ЕСЛИ ЗАЯВЛЕНИЕ = ЕСЛИ (A2 = H2, "СОХРАНИТЬ", "УДАЛИТЬ")

В будущем все, что мне нужно сделать, это обновить сводку, вставить результаты сводки на вкладку date_resolver, вставить строки моей формулы и удалить строки удаления.

(глубоко задумавшись)

|источник
0

Если вы готовы рассмотреть возможность использования стороннего инструмента, я бы порекомендовал BeyondCompare. Он прост в использовании, имеет очень щедрую пробную версию перед покупкой, довольно низкую цену и удобен как для сравнения, так и для объединения различных типов файлов и каталогов, включая Excel. Вы можете скопировать отдельные строки из одного файла в другой.

(Я - пользователь BC, и не имею ничего общего с компанией.)

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .