Прочитав описание снова, ища ответы на мои предыдущие вопросы, я пришел к выводу, что каждый исследуемый населенный пункт не только попадает в сетку одного населенного пункта и площади в 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 в столбце A листа 2 и вернуть значение 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 должно быть установлено как текст, иначе это будут цифры, и в следующем примере Excel отбросит ведущие нули и функции манипуляции со строками. 3 столбца потерпят неудачу, что приводит к сбоям или даже ошибкам в последних двух столбцах.
Дополнительное примечание для людей, использующих Linux ! в листах ссылки на VLOOKUP необходимо изменить на период . , чтобы заставить его работать в LibreOffice Calc, но все остальное остается как есть.
