У меня есть база данных нескольких компаний. Существует одна запись на компанию. В каждой записи компании есть несколько контактов.
Как я могу создать запись для каждого КОНТАКТА (сохраняя одну и ту же информацию о компании для каждого)?
Вот образец:
Этого будет достаточно для вашего примера. Вам нужно будет немного изменить, чтобы соответствовать реальной базе данных.
Example Data
Company | Address | City | State | ZIP | Phone | GM | EmailGM | PD | EmailPD | SM | EmailSM | PRM | EmailPRM
ABC | 1234 M | Saint | MN | zip | phone | gm | gm1@gm | pd | pd1@pd | sm | sm1@sm | prm | prm@prm
Result
Company | Address | City | State | ZIP | Phone | Name | Email
ABC | 1234 M | Saint | MN | zip | phone | gm | gm1@gm
ABC | 1234 M | Saint | MN | zip | phone | pd | pd1@pd
ABC | 1234 M | Saint | MN | zip | phone | sm | sm1@sm
ABC | 1234 M | Saint | MN | zip | phone | prm | prm1@prm
Company column (and Address, City, State, ZIP, Phone)
=INDIRECT("A"&(CEILING((ROW()+1)/4-1, 1)))
Name column (and Email)
=INDIRECT(CHAR(CODE("G")+MOD((ROW()-2), 4)*2)&CEILING((ROW()-1)/4+1, 1))
В столбце Компания формула ссылается на столбец "A" а номер строки рассчитывается из текущей строки.
ROW()-1 корректирует номер строки назад, потому что у нас есть заголовок в таблице данных. Мы используем номер 1, потому что данные таблицы данных начинаются со строки 2, а не со строки 1.
/4 + 1 основном копирует строку результата 4 раза, затем корректирует номер строки результата на единицу, потому что у нас есть заголовок в таблице данных. Мы используем номер 4, потому что у нас есть 4 имени и адреса электронной почты. Мы используем номер 1, потому что данные таблицы данных начинаются со строки 2, а не со строки 1.
CEILING( ... , 1 ) округляет номер строки до целого числа.
В столбце "Имя" формула ссылается на столбец "G" а номер строки рассчитывается из текущей строки.
(ROW()-2) сдвигает результат MOD обратно на 0 . Мы используем номер 2, потому что данные таблицы результатов начинаются со строки 2 .
MOD( ... , 4) вычисляет, какой столбец получить. Результат 0 означает столбец G , 1 означает столбец H и т.д. Мы используем номер 4, потому что у нас есть 4 имени и адреса электронной почты.
+ ... * 2 смещает столбец, выбранный справа от столбца G по the result of modulo, multiplied by 2 . Мы используем номер 2, потому что у нас есть 2 столбца, имя и адрес электронной почты, чтобы получить.
CODE("G") преобразует символ "G" в его код ASCII.
CHAR( ... ) преобразует значение смещенного столбца (от 7 до 11, то есть "G", например, в столбец "K") обратно в строку.
CEILING( ... ) дает номер строки данных для выборки.
Просто измените букву столбца на соответствующий столбец.
Пример:
в столбце "Адрес" измените букву "A" на "B"
в столбце электронной почты измените букву "G" на "H"
Вы упомянули, что у вас будет 7 имен и адресов электронной почты. Вам нужно будет изменить формулу, чтобы использовать 7 вместо 4.
Эта формула чувствительна к тому, где вы положили эту формулу. Эта формула предполагает, что вы поместите ее в строку 2 (строка 1 для заголовка). Вам нужно будет отрегулировать, если вы поместите его в строку 1 (см. Объяснение)
Эта формула не пропускает пустое имя и адрес электронной почты. Все компании будут иметь ровно 4 строки, независимо от количества доступных имени и адреса электронной почты.
Это не предназначено для замены базы данных, но вы можете использовать данные, сгенерированные по этой формуле, для создания базы данных.
Другой способ сделать это в Excel - использовать Power Query или Data ► Get and Transform ► From Table (зависит от версии Excel)
После входа в редактор запросов выберите повторяющиеся столбцы и ОТКЛЮЧИТЕ другие столбцы. Это даст вам такую таблицу:
Обратите внимание, что я переименовал новые столбцы, созданные с помощью операции UNPIVOT.
Теперь добавьте третий столбец, назовите его "Email" и введите эту формулу в I2
I2: =IF(ISERR(SEARCH("email",[@Position])),H3,"")
Тогда это всего лишь вопрос фильтрации бланков из столбца электронной почты. Вы можете сделать это с помощью простого фильтра; затем скопируйте / вставьте видимые ячейки в область результатов; или вы можете использовать расширенный фильтр, который имеет механизм для отправки результатов фильтра в указанную область:
