Прочитав описание снова, ища ответы на мои предыдущие вопросы, я пришел к выводу, что каждый исследуемый населенный пункт не только попадает в сетку одного населенного пункта и площади в PLSS, но и соответствует ей, за исключением номенклатуры. Таким образом, все 9 листов обзорного городка 02 соответствуют старым 9 листам Township 04S Range 06W (или 06E). Поскольку вы сказали, что в текущем округе насчитывается 20 населенных пунктов, я создал гипотетическую «карту» страны, на которой показано наложение 20 населенных пунктов на сетку PLSS. (Так как у вас есть карты и вы знаете область, вы можете настроить мой пример в соответствии с вашими требованиями.) Я сделал эту гипотетическую карту пересеченной как по базовой линии, так и по меридиану, чтобы вы могли увидеть ее применение в этой ситуации.
Это «карта», которую я использовал для примера.
Преобразование этой карты в данные для вашего Sheet2 создает следующий лист:
Используя ваши образцы данных для PID 0241800000001000, я создал это как Лист1:
Формула для ячейки B2- =LEFT(A2,2)извлечь номер опроса из PID.
Формула для ячейки C2является =MID(A2,3,1), чтобы извлечь карту лист из PID.
Формула для ячейки D2является =MID(A2,4,2), чтобы извлечь номер секции из PID.
Используя последнюю формулу в качестве руководства, вы сможете извлечь любую другую часть PID, которая является предсказуемой с позиции. Я предполагаю, что оставшиеся 10 цифр находятся в двух группах по 5 для секционных подразделений к востоку и северу от углов секций. (Полагаю, 8 000 ссылок Гюнтера сработают, если вы находитесь в месте, где все еще используется эта система, или даже в 5 цифрах умещаются дюймы).
Формула для ячейки E2состоит в том =VLOOKUP(VALUE(B2),Sheet2!$A$2:$C$21,2), чтобы найти значение (числовое) ячейки B2в столбце ASheet2 и вернуть значение 2-го столбца в той же строке. Обратите внимание, что это один пункт, чтобы быть осторожным! Значения в столбце индексации Sheet2 должны быть числами, а не текстом. Так что вместо «02» это только 2. Это, вероятно, лучше, поскольку при вводе чисел Excel хочет сохранить их как числа, а не как текст, если только вы не решите проблему с кавычками или явным форматированием ячейки.
Формула для ячейки F2является =VLOOKUP(VALUE(B2),Sheet2!$A$2:$C$21,3), за исключением того, что это третья колонка, это работает так же, как формула в ячейке E2.
Изменение данных в Sheet2 и ссылки на них в столбцах E и F Sheet1 должны позволить вам повторно использовать их для любого округа в вашем штате, если они используют одну и ту же систему с PID. Надеюсь, что по пунктам, которые я неправильно понял, вы сможете скорректировать приведенные здесь концепции, чтобы исправить их.
РЕДАКТИРОВАТЬ
Чтобы использовать одну и ту же систему для нескольких округов или наборов Survey Township, измените Sheet2, вставив два столбца в начале. Это для списка округов или других названий, которые вы хотите использовать для наборов Survey Township, и их соответствующего «диапазона» для поиска данных PLSS. Этот список должен быть в алфавитном порядке, так как другие наборы добавляются. В каждом округе будет свой набор из трех столбцов. Эти таблицы не должны быть сложены слева направо. Они могут быть организованы любым способом, который лучше всего подходит для вашего рабочего процесса. Их также не нужно хранить в каком-либо порядке, только имена в столбце Aдолжны быть в порядке. Это образец с 3 сетами, которые я раскрасил только для того, чтобы было понятно, как они ломаются.
Это образец «карты» наборов. Я включил несколько перекрывающихся областей, поскольку знаю, что не все графства будут следовать удобно нарисованным границам PLSS. Итак, я предполагаю, что два разных поселка могут соответствовать одному и тому же PLSS Township / Range.
На первом листе Sheet1вставьте новый столбец для названия округа в качестве столбца A. Образец показан ниже.
Имена, используемые в этом столбце, должны точно соответствовать именам, используемым в первом столбце Sheet2, но могут использоваться в любом порядке. Формулы Sheet1должны быть скорректированы с учетом изменений на обоих листах, а также ссылки на названия округов.
Формула для ячейки C2 есть =LEFT(B2,2).
Формула для ячейки D2 есть =MID(B2,3,1).
Формула для ячейки E2 есть =MID(B2,4,2).
Формула для ячейки F2 имеет вид
`=VLOOKUP(VALUE(C2),INDIRECT("Sheet2!"&VLOOKUP(A2,Sheet2!$A$2:$B$100,2)),2)`.
Формула для ячейки G2 имеет вид
`=VLOOKUP(VALUE(C2),INDIRECT("Sheet2!"&VLOOKUP(A2,Sheet2!$A$2:$B$100,2)),3)`.
В клетках F2и G2формулы включает в себя диапазон, Sheet2!$A$2:$B$100где 100это просто число, выбранное, чтобы обеспечить много графств (99) на самом деле. Если вы государство имеет 120 уездов, и вам необходимо, чтобы охватить все из них, изменить, 100чтобы 121, к примеру.
«Волшебство» происходит в двух последних ячейках с использованием двух функций VLOOKUPи INDIRECT. Внутренний VLOOKUPиспользует название округа в Sheet1столбце, Aчтобы найти диапазон для использования в Sheet2. Это объединено с другими частями строки, чтобы создать полную ссылку на таблицу в Sheet2. INDIRECTФункция принимает эту строку и превращает его в ссылку, что внешние VLOOKUPмогут использовать.
Вы можете использовать отдельные листы для покрытия разных состояний, если хотите. Чтобы заставить это работать, измените Sheet2ссылки в ячейке F2и G2на Sheet3или как там будет новое имя листа. Вы также можете переименовать листы, даже если их всего 2, во что-то, что работает лучше. Опять же, вам нужно изменить Sheet2ссылки на новое имя. (Всего 4 замены на строку, по две в каждой Fи G.) Если два штата имеют одинаковое название округа, это не имеет значения, так как имя будет найдено в списке на листе этого штата и будет ссылаться на свою собственную таблицу, а не на одноименный округ в другом штате.
Первоначально я забыл упомянуть, что форматирование ячеек Sheet1для PID должно быть установлено как текст, иначе это будут цифры, и в следующих 3-х Excel будут отбрасываться начальные нули и функции манипуляции со строками. столбцы потерпят неудачу, что приводит к сбоям или даже ошибкам в последних двух столбцах.
Дополнительное примечание для людей, использующих Linux, !в листе «Ссылки» VLOOKUPнеобходимо изменить на точку ., чтобы она работала в LibreOffice Calc, но остальная часть остается неизменной .