У меня есть набор данных, загруженный из веб-базы данных, которая имеет переменное форматирование. Цель состоит в том, чтобы получить все данные в строках, чтобы их можно было отсортировать и потенциально превратить в файл CSV-KMZ(KML). После разъединения ячеек данные форматируются следующим образом:этот.
Решение методом грубой силы, которое я нашел, включает в себя использование='cell number'в ячейках справа (Так.) и затем удалить все пустые ячейки, чтобы объединить все данные в одной строке (пример здесь). Отсюда я могу просто выбрать и перетащить вниз, чтобы скопировать этот массив ячеек в нижние ячейки. Проблема возникает, когда натыкаешься на записи Responsible Party с несколькими сайтами, как на первой картинке, так как они сбрасывают интервал, что требует большого количества копирования и вставки, чтобы обойти это.
Поскольку этот набор данных спускается до строки 10 000+, и, вероятно, будут еще наборы, подобные этому, я надеюсь, что некоторые из великих талантов здесь смогут придумать другое решение. Спасибо всем, кто думает об этой проблеме!
решение1
Изображенный пример макета данных — ключ к вашему успеху. Этохорошо охарактеризованныйданные. Это значит, что они следуют заданному шаблону, а не разбросаны по всему. И это значит, что вы можете написать простые, понятные формулы, чтобы собрать каждую их часть.
Когда я прочитал вводную часть перед тем, как нажать eclick, я думал, что увижу данные повсюду. Например, {City} может быть в ячейке D3, E3 или F3, возможно, и другие подобным образом повсюду, или, возможно, объединены с {State}, и каждая запись отличается: не объединены в некоторых, шесть подряд с {City} в E3, затем пара в F3, и тому подобное.
У вас НЕТ НИ ОДНОГО подобного недостатка!
Ваша единственная трудность заключается в информации о сайте, состоящей, возможно, из нескольких сайтов, а не только из одного на запись, период. Но это небольшое осложнение решается двумя вещами:
1) Суждение о том, сколько сайтов возможно для одной записи. Вы, должно быть, уже думаете об этом, поскольку планируете расположить результаты в одной строке для каждой RP, поэтому используйте это. 2) Использование функции IF() для проверки определенного фрагмента данных, который скажет вам, следует ли извлекать информацию о сайте или начинать новую запись.
Второй вариант покажется немного странным, поскольку вам придется принимать решение в двух местах, так сказать, а не только в одном.
После принятия решения ячейки в выходной строке могут иметь более простые формулы, которые проверяют содержимое принимающей решение ячейки и распространяют его на всю строку.
Что вы делаете? Во-первых, я предполагаю, что выделенные жирным шрифтом элементы были добавлены для ясности и не нуждаются в извлечении. (Если они есть, сделайте это таким же образом.) Я также планирую простой вывод, который будет иметь строку данных, затем ряд «пустых» строк, затем еще одну строку данных и так далее. Идея в конце будет заключаться в том, чтобы Копировать и Вставить|Специальные|Значения, затем отсортировать и удалить огромную кучу «пустых» строк в конце. Можно сделать и поинтереснее, но это не в планах на 11:30 вечера...
Чтобы извлечь основную информацию, данные «RP» (я буду использовать идею, что они начинаются в T2, и предположу, что «Party ID — это «RP#»), вам нужно зафиксировать немного данных, а затем связать с ними позиции, рассматриваемые для других частей. «RP#» выглядит идеальным для этого. Таким образом, ячейка T2 будет иметь:
= C1
Теперь используйте OFFSET(), чтобы найти все остальные данные RP. Но помните, что каждая строка в выводе должна проверить, должна ли она вообще собирать данные. Поэтому вам нужно обернуть это в IF(), чтобы проверить, получает ли строка данные или нет:
= IF( C1 = "", "", C1)
Это заполнит T2 некоторым RP#. В T3 через T7 будет показано "". Перейдите к U2. Введите следующую формулу:
= IF( C1="", "", OFFSET( C1, 0, 2 ) )
Если C1 имел RP#, то вы находите значение ячейки на 0 строк ниже и на 2 столбца правее. Если нет, вы получаете "" в U2 (и вы будете продолжать получать это для всех столбцов, ищущих данные не-Site).
Продолжайте. Просто измените два значения (строки — первое, столбцы — второе) по мере необходимости, чтобы определить местоположение каждой части относительно C1. Thant обрабатывает все запрошенные данные, не относящиеся к сайту. (Интересно запомнить (и это будет использоваться в дальнейшем), что смещения могут быть отрицательными значениями, поэтому вы можете использовать OFFSET() для просмотра влево и вверх, а также вправо и вниз.)
Для ячейки AB2 введите смещение +4 строки (т.е.вниз) и -1 столбцов (так чтолевый). Итак, простой тест IF(), затем смещение. Кажется невозможным, учитывая данные, что не должно быть минимум одного Сайта для каждой RP, но если их может не быть, добавьте к тесту IF():
= IF( OR( C1="", OFFSET( C1, 4, -1 ) = "" ), "", OFFSET( C1, 4, -1 ) )
Двигаясь вправо, собирая данные о сайте, вернитесь к простому тесту IF(), но используйте AB2 вместо C1. (Если C1 не был RP#, у вас есть «пустой» AB2, поэтому «пустой» AB2 означает, что C1 также был «пустым», поэтому нет необходимости проводить проверку каждый раз.) Соберите все данные этого сайта так же, как вы это делали с данными RP.
Теперь суть вопроса: есть ли второй сайт или начало новой записи? Ячейка, тот же столбец, но на 7 строк ниже RP# этой записи, является либо новым RP#, либо пустой. «Пустота» может быть проверена, как это делалось ранее. Допустим, AK2 — это то место, где должны начинаться данные второго сайта. Просто проверьте эту ячейку на пустоту или нет. Если пусто, то есть второй сайт, и вы находите его данные так же, как указано выше. Используйте:
= IF( OR( C1="", OFFSET( C1, 7, 0 ) = "" ), "", OFFSET( C1, 7, -1 ) )
который получает Site #, если таковой существует, или "". Те же формулы, что и для первого сайта, только с изменением смещения строки (смещения столбцов будут такими же). Если информации о сайте может вообще не быть, измените это, чтобы также проверить AB2 на "", так что если он "пустой", результаты "" будут рябить вправо отсюда.
Сделайте это для стольких наборов данных Сайта, сколько вы посчитали возможным. Плюс, может быть, еще один или два, а?
Теперь "другая половина" существования данных сайта: мы переходим к строке 3 и ячейке T3. Копируем все ячейки строки 2 вниз, скажем, до строки 15. Будет ряд пустых строк, C2, c3 и т. д. не имеют RP#, пока не будет достигнут C8. Затем данные появляются снова.
А поскольку T8 не будет "", строка справа от него заполнится данными. Ура!
Вы можете подумать, что ячейки Site справа от реальных коллекций Site могут получить странные или запутанные записи, потому что они считывают данные в следующую запись, или две, или четыре. Но ПЕРВАЯ проверка, есть ли для них вообще причина, и приводит к "" для (беспокоюсь, что это поддельный) Site #, а затем это рябит вправо, приводя к записям "", а не к считыванию того, что было бы данными какой-то другой записи. Не беспокойтесь.
Скопируйте и вставьте либо все строки данных, которые у вас есть, либо столько, с которыми вы можете работать (помня об этом, вы не только заставите свою машину отслеживать все эти формулы в этих 10 000 строках, но и одновременно копировать и вставлять их значения). Допустим, здесь нет никаких проблем, но если они есть, вам придется управлять ими наборами по 1000 строк или как вам будет удобно.
После завершения всех вычислений скопируйте выходные ячейки и выберите Вставить|Специальные|Значения в... куда-нибудь еще, например, на второй лист. (На этом этапе, если мощность компьютера имеет значение, удалите все строки формул, кроме первых двух.)
Находясь на совершенно другом листе, вы можете делать с выходными данными все, что угодно, не затрагивая ячейки извлечения формул или исходные данные.
На втором листе у вас есть только одна забота: вы будете сортировать вывод. Иногда нужен исходный порядок данных, а это НЕ порядок сортировки, который создаст Excel. Если это так, вставьте столбец слева и заполните его по своему усмотрению списком последовательных номеров. Это должны быть просто «константы», а не формулы, чтобы ничего не изменилось из-за того, что формулы пересчитывают себя после сортировки...
Хорошо, пришло время избавиться от всех этих "пустых" строк и оставить только плотный набор строк данных. Отсортируйте данные (И нумерованный столбец тоже, если он вам нужен или вы хотите его). Сделайте это снизу вверх, как обычно, чтобы "пустые" строки оказались внизу. Найдите первую из этих "пустых" строк. Есть много способов сделать это... прокрутить, скажем... или, возможно, сначала перейти к ЛЮБОЙ пустой строке и ввести "zzzzzzzzzzzzzz" в ее первую (крайнюю левую) ячейку, чтобы она отсортировала данные до конца, что сделает ее первой строкой перед всеми "пустыми" строками. Как только вы дойдете до первой ячейки первой строки "пустых" ячеек, нажмите Ctrl-Shift-End, чтобы выделить ВЕСЬ мусор, который вам нужно вынести. Очистите содержимое с помощью клавиши Delete.
Теперь все ваши строки данных вместе, нет никаких ложных не-на-самом-деле-пустых строк, которые мешают вам использовать данные, и вы готовы к работе. Работайте над этим или скопируйте и вставьте это в место последнего упокоения, возможно, в какую-то другую электронную таблицу, и вперед!
Кстати, конструкция очень проста и не требует много времени. Однажды сделанная, она сделана навсегда, пока данные не перемещаются. Вы знаете, сайты меняются, программное обеспечение обновляется и совершенно новые столбцы меняют вещи, но есть много простых способов обойти эти проблемы, пока вы медленно обновляете свою первоначальную работу.
После создания данные считываются в тот момент, когда они загружены, затем вы просто делаете одно копирование и вставку, сортировку, удаление (все это просто нажатия клавиш, буквально минута, а не часы), и все готово. Я упоминаю это, потому что, будучи на вашем месте и решая проблемы с помощью грубой силы, как вы поднимаете в вопросе, я думаю, вам будет трудно поверить, но ЧАСЫ отвратительного, жестокого продирания через это с ошибками, которые приходится исправлять тоннами, промахами мыши и так далее, теперь будут буквально одной минутой. Наслаждайтесь жизнью, которая больше не крадет у вас!