Как добавить фамилию в эксель

Обновлено: 05.07.2024

В Excel есть несколько полезных функций, которые позволяют сэкономить время и повысить продуктивность повседневной работы. Одна из таких полезных (и менее известных) функций в настраиваемых списках в Excel.

Названия месяцев довольно общие, поэтому они доступны в Excel по умолчанию.

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

Вы тоже можете это сделать … С помощью настраиваемых списков в Excel

В этом уроке я покажу вам, как создавать свои собственные списки в Excel и как использовать их для экономии времени.

Как создавать собственные списки в Excel

По умолчанию в Excel уже есть несколько предварительно загруженных настраиваемых списков, которые можно использовать для экономии времени.

Например, если вы введете «Пн» в одну ячейку «Вт» в соседней ячейке, вы можете использовать маркер заполнения для заполнения остальных дней. Если вы расширите выделение, продолжайте перетаскивать, и он повторится и снова даст вам название дня.

Ниже приведены настраиваемые списки, которые уже встроены в Excel. Как видите, это в основном названия дней и месяцев, поскольку они фиксированы и не изменятся.

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

Ниже приведены шаги по созданию собственного настраиваемого списка в Excel:

Перейдите на вкладку Файл.
Щелкните Параметры. Откроется диалоговое окно «Параметры Excel».
Нажмите на опцию Advanced на левой панели.
В разделе «Общие» нажмите кнопку «Изменить настраиваемые списки» (возможно, вам придется прокрутить вниз, чтобы перейти к этому параметру).
В диалоговом окне «Настраиваемые списки» импортируйте список, выбрав диапазон ячеек, содержащих список. Кроме того, вы также можете ввести имя вручную в поле List Entries (через запятую или каждое имя в новой строке)
Нажмите на Добавить

Как только вы нажмете «Добавить», вы заметите, что ваш список теперь становится частью настраиваемых списков.

Если у вас есть большой список, который вы хотите добавить в Excel, вы также можете использовать параметр «Импорт» в диалоговом окне. (см. как добавить месяцы к дате в Excel)

Совет от профессионала : вы также можете создать именованный диапазон и использовать этот диапазон для создания настраиваемого списка. Для этого введите имя именованного диапазона в поле «Импортировать список из ячеек» и нажмите «ОК». Преимущество этого заключается в том, что вы можете изменить или расширить именованный диапазон, и он будет автоматически настроен как настраиваемый список.

Теперь, когда у вас есть список в бэкэнде Excel, вы можете использовать его так же, как вы используете числа или названия месяцев с автозаполнением (как показано ниже).

Хотя здорово иметь возможность быстро получить эти пользовательские имена lits в Excel простым перетаскиванием, есть кое-что еще более интересное, что вы можете сделать с настраиваемыми списками (об этом следующий раздел).

Создайте свои собственные критерии сортировки с помощью настраиваемых списков

В настраиваемых списках замечательно то, что вы можете использовать их для создания собственных критериев сортировки. Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите отсортировать его по параметрам «Высокий», «Средний» и «Низкий».

Вы не можете этого сделать! Если вы отсортируете в алфавитном порядке, он изменит алфавитный порядок (он даст вам высокий, низкий и средний, а не высокий, средний и низкий). Вот где действительно сияют индивидуальные списки.

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

Первым шагом является создание настраиваемого списка (высокий, средний, низкий), используя шаги, показанные в предыдущем разделе («Как создавать настраиваемые списки в Excel»).

После того, как вы создали настраиваемый список, вы можете использовать следующие шаги для сортировки на его основе:

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

Обратите внимание, что вам не обязательно сначала создавать настраиваемый список, чтобы использовать его при сортировке. Вы можете использовать описанные выше шаги, а на шаге 4, когда откроется диалоговое окно, вы можете создать список прямо в этом диалоговом окне.

Некоторые примеры использования настраиваемых списков

Ниже приведены некоторые из случаев, когда создание и использование настраиваемых списков может сэкономить ваше время:

Если у вас есть список, который вам нужно ввести вручную (или скопировать и вставить из другого источника), вы можете создать собственный список и использовать его вместо этого. Например, это могут быть названия отделов в вашей организации, названия продуктов или регионов / стран.
Если вы учитель, вы можете составить список имен своих учеников. Таким образом, когда вы будете ставить им оценку в следующий раз, вам не нужно будет беспокоиться о том, чтобы ввести имена учеников вручную или скопировать их с другого листа. Это также снижает вероятность ошибок.
Когда вам нужно отсортировать данные на основе критериев, которые не встроены в Excel. Как описано в предыдущем разделе, вы можете использовать свои собственные критерии сортировки, создав собственный список в Excel.

Это все, что вам нужно знать о создании настраиваемых списков в Excel .

После того, как вы создали раскрывающийся список, вам может понадобиться добавлять в него дополнительные элементы или удалять имеющиеся. В этой статье мы покажем, как изменять списки, созданные разными способами.

Изменение раскрывающегося списка, основанного на таблице Excel

Если источником вашего списка является таблица Excel, достаточно просто добавить элементы в список или удалить их из него, а Excel автоматически обновит все связанные раскрывающиеся списки.

Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

Чтобы удалить элемент, нажмите кнопку Удалить.

Совет: Если удаляемый элемент находится в середине списка, щелкните его правой кнопкой мыши, выберите пункт Удалить, а затем нажмите кнопку ОК, чтобы сдвинуть ячейки вверх.

Изменение раскрывающегося списка, основанного на именованном диапазоне

Откройте лист, содержащий именованный диапазон для раскрывающегося списка.

Выполните одно из указанных ниже действий.

Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

Чтобы удалить элемент, нажмите кнопку Удалить.

На вкладке Формулы нажмите кнопку Диспетчер имен.

В поле Диспетчер имен выберите именованный диапазон, который требуется обновить.

Щелкните поле Диапазон, а затем на листе выберите все ячейки, содержащие записи для раскрывающегося списка.

Совет: Чтобы определить именованный диапазон, выделите его и найдите его имя в поле Имя. Сведения о поиске именованных диапазонов см. в статье Поиск именованных диапазонов.

Изменение раскрывающегося списка, основанного на диапазоне ячеек

Откройте лист, содержащий данные для раскрывающегося списка.

Выполните одно из указанных ниже действий.

Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

Чтобы удалить элемент, нажмите кнопку Удалить.

На листе с раскрывающимся списком выделите содержащую список ячейку.

На вкладке Данные нажмите кнопку Проверка данных.

В диалоговом окне на вкладке Параметры щелкните поле Источник, а затем на листе с записями для раскрывающегося списка выберите все ячейки, содержащие эти записи. После выделения ячеек вы увидите, как изменится диапазон списка в поле "Источник".

Чтобы обновить все ячейки, к которым применен один и тот же раскрывающийся список, установите флажок Распространить изменения на другие ячейки с тем же условием.

Изменение раскрывающегося списка с введенными вручную элементами

На листе с раскрывающимся списком выделите содержащую список ячейку.

На вкладке Данные нажмите кнопку Проверка данных.

На вкладке Параметры щелкните поле Источник и измените нужные элементы списка. Элементы должны быть разделены точкой с запятой, без пробелов между ними следующим образом: Да;Нет;Возможно

Если список записей для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Подробнее о защите листов читайте в статье Блокировка ячеек.

Если вам нужно удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.

Чтобы просмотреть видео о том, как работать с раскрывающимися списками, см. статью Создание раскрывающихся списков и управление ими.

Изменение раскрывающегося списка, основанного на таблице Excel

Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

Чтобы удалить элемент, нажмите кнопку Удалить.

Изменение раскрывающегося списка, основанного на именованном диапазоне

Откройте лист, содержащий именованный диапазон для раскрывающегося списка.

Выполните одно из указанных ниже действий.

Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

Чтобы удалить элемент, нажмите кнопку Удалить.

На вкладке Формулы нажмите кнопку Диспетчер имен.

В поле Диспетчер имен выберите именованный диапазон, который требуется обновить.

Щелкните поле Диапазон, а затем на листе выберите все ячейки, содержащие записи для раскрывающегося списка.

Изменение раскрывающегося списка, основанного на диапазоне ячеек

Откройте лист, содержащий данные для раскрывающегося списка.

Выполните одно из указанных ниже действий.

Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

Чтобы удалить элемент, нажмите кнопку Удалить.

На листе с раскрывающимся списком выделите содержащую список ячейку.

На вкладке Данные нажмите кнопку Проверка данных.

В диалоговом окне на вкладке Параметры щелкните поле Источник, а затем на листе с записями для раскрывающегося списка выделите содержимое ячеек в Excel, в которых находятся эти записи. После выделения ячеек вы увидите, как изменится диапазон списка в поле "Источник".

Изменение раскрывающегося списка с введенными вручную элементами

На листе с раскрывающимся списком выделите содержащую список ячейку.

На вкладке Данные нажмите кнопку Проверка данных.

После обновления раскрывающегося списка убедитесь, что он работает так, как нужно. Например, проверьте, как изменяется ширина столбцов и высота строк для отображения ваших обновленных записей.

Если вам нужно удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.

В Excel в Интернете можно изменять только тот раскрывающийся список, в котором исходные данные введены вручную.

Выделите ячейки, в которых расположен раскрывающийся список.

Выберите пункт Данные > Проверка данных.

На вкладке Параметры щелкните в поле Источник. Затем выполните одно из указанных ниже действий.

Если поле "Источник" содержит записи раскрывающегося списка, разделенные запятыми, введите новые записи или удалите ненужные. После завершения записи должны быть разделены запятыми без пробелов. Например: Фрукты,Овощи,Мясо,Закуски.

Если поле "Источник" содержит ссылку на диапазон ячеек (например, =$A$2:$A$5), нажмите кнопку Отмена, а затем добавьте или удалите записи из этих ячеек. В этом примере можно добавить или удалить записи в ячейках А2–А5. Если окончательный список записей оказался больше или меньше исходного диапазона, вернитесь на вкладку Параметры и удалите содержимое поля Источник. Затем щелкните и перетащите указатель, чтобы выделить новый диапазон, содержащий записи.

Если поле "Источник" содержит именованный диапазон, например, "Отделы", необходимо изменить сам диапазон с помощью классической версии Excel.

После обновления раскрывающегося списка убедитесь, что он работает так, как нужно. Например, проверьте, достаточно ли ширины ячеек для отображения ваших обновленных записей. Если вам нужно удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Если фамилия, имя и отчество записываются в разные ячейки, работать с ними легче, чем при размещении их в одной ячейке. Перечислим преимущества:

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

Итак, объединяем фамилию, имя и отчество полностью из разных ячеек в одну:

Здесь можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(A1;" ";B1;" ";C1) , или просто соединить строки с помощью & (амперсандов) =A1&" "&B1&" "&C1 , не забыв добавить между словами пробелы.

В следующем примере мы также объединим фамилию, имя и отчество из разных ячеек в одну, при этом имя и отчество заменив на инициалы:

Здесь также можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(A1;" ";ЛЕВСИМВ(B1;1);".";ЛЕВСИМВ(C1;1);".") или & (амперсанды) =A1&" "&ЛЕВСИМВ(B1;1)&"."&ЛЕВСИМВ(C1;1)&"." , не забыв добавить между фамилией и инициалами пробел, а к инициалам точки. В этом примере мы извлекаем левые символы из имени и отчества для присоединения их к фамилии в виде инициалов.

Фамилия, имя и отчество в одной ячейке

Использование фамилии, имени и отчества в одной ячейке имеет тоже свои преимущества:

уменьшается количество колонок в таблице;
в большинство документов* требуется внесение фамилии, имени и отчества в полном написании.

*Если документы генерируются на основе этой таблицы, то вставка ФИО не потребует дополнительных преобразований.

Лично мне в работе не приходилось преобразовывать фамилию, имя и отчество из одной ячейки в разные, но могу предположить, что для кого-то это бывает необходимо:

Для этого преобразования используются следующие формулы в соответствующих ячейках:

ячейка B1 — =ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)-1)
ячейка C1 — =ПСТР(A1;НАЙТИ(" ";A1;1)+1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)-НАЙТИ(" ";A1;1)-1)
ячейка D1 — =ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1))

Чтобы определить начало имени и начало отчества используются позиции первого и второго пробелов, найденных с помощью функции «НАЙТИ».

Необходимость следующего преобразования возникает чаще предыдущего, используется для заполнения различных документов наряду с полным именем:

Для этого преобразования используется следующая формула в ячейке B1 — =СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1));" ";ПСТР(A1;НАЙТИ(" ";A1;1)+1;1);".";ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1);".")

Здесь тоже функцию «СЦЕПИТЬ» можно заменить & (амперсандами) — =ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1))&" "&ПСТР(A1;НАЙТИ(" ";A1;1)+1;1)&"."&ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1)&"."

Вы можете копировать эти формулы в свои файлы, не забывая изменять адреса ячеек на фактические. Часто инициалы пишут перед фамилией, изменить формулы для такого отображения ФИО несложно.

В этом руководстве по Excel я покажу вам несколько различных способов сочетания имени и фамилии в Excel.

Вы можете легко сделать это, используя простые формулы (такие как Concatenate или TextJoin) и такие функции, как Flash Fill и Power Query.

4 способа сочетания имени и фамилии в Excel

Предположим, у вас есть набор данных, как показано ниже, и вы хотите объединить имя в столбце A и фамилию в столбце B.

Давайте посмотрим на некоторые способы сделать это.

Использование функции CONCATENATE

Комбинировать разные текстовые строки из разных ячеек в Excel довольно просто. В Excel есть встроенная формула Concatenate, предназначенная только для этой цели.

Ниже приведена формула, которая объединит имя и фамилию (разделенные пробелом):

Ниже приведена формула CONCATENATE, если вы хотите ее использовать:

Приведенная выше формула просто берет имя и фамилию и объединяет их. И поскольку я хочу, чтобы они были разделены пробелом, я использовал «» (пробел в двойных кавычках) в качестве второго аргумента.

Вы также можете использовать оператор амперсанда для конкатенации.

Предполагая, что у вас один и тот же набор данных, вы можете использовать приведенную ниже формулу для объединения имени и фамилии:

Теперь, когда вы понимаете концепцию, вы можете комбинировать имена в разных форматах, если хотите. Например, вы можете указать фамилию, а затем имя или запятую вместо пробела между именами.

Если вам нужно только комбинированное имя и вы хотите избавиться от имени и фамилии, вам следует сначала преобразовать значения формулы в статические значения. После этого вы можете удалить / удалить имя и фамилию.

Использование функции TEXTJOIN

Если у вас есть доступ к этой функции, лучше всего использовать ее для объединения ячеек и столбцов (так как это намного лучше, чем приведенные выше методы CONCATENATE и амперсанда).

Предположим, у вас есть набор данных, показанный ниже, и вы хотите объединить имя и фамилию.

Ниже приведена формула для этого:

Вышеупомянутая функция TEXTJOIN принимает три аргумента:

Разделитель, который в этом примере представляет собой пробел в двойных кавычках (поскольку мы хотим, чтобы имя и фамилия разделялись пробелом)
Логическое значение, где TRUE означает, что в случае наличия пустых ячеек формула игнорирует их.
Диапазон, содержащий ячейки, которые вы хотите объединить.

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

Использование Flash Fill

Позвольте мне объяснить, как это работает.

Предположим, у вас есть набор данных, как показано ниже, и вы хотите объединить имя и фамилию.

Ниже приведены шаги, которые можно использовать для этого с помощью Flash Fill.

В ячейке C2 введите желаемый результат. В нашем примере это будет «Бобби Бейкер».
В ячейке C3 начните вводить ожидаемый результат. Вы заметите, что Excel показывает ожидаемый результат во всех ячейках (в светло-сером тексте). Это Flash Fill в действии

Вышеупомянутые шаги мгновенно заполнят все ячейки объединенным именем.

В некоторых случаях возможно, что вы не увидите заливку флэш-памяти при вводе текста во второй ячейке.

Не волнуйтесь, иногда такое бывает.

В таком сценарии ниже другие шаги, которые вы можете использовать, чтобы заставить работать Flash Fill:

В группе «Редактирование» щелкните значок «Заливка».
Нажмите на Flash Fill.

Вышеупомянутые шаги мгновенно выберут узор из ячейки выше и заполнят весь столбец комбинированным именем.

Если Flash-заливка не может подобрать правильный узор и дает неправильный результат, заполните две ячейки вручную, а затем выполните указанные выше действия.

Вы также можете использовать сочетание клавиш Control + E для заливки с помощью Flash-заливки.

Имейте в виду, что Flash Fill не идеален. Он работает, определяя шаблоны и используя один и тот же шаблон для заполнения всех ячеек в столбце. Хотя это, скорее всего, сработает, как ожидалось, рекомендуется дважды проверить результат Flash Fill.

Использование Power Query

Для работы Power Query ваши данные должны находиться в таблице Excel (или хотя бы в именованном диапазоне).

Для этого урока я конвертирую набор данных с первым и последним именем в таблицу Excel.

Предположим, у вас есть набор данных, показанный ниже, и вы хотите объединить два столбца, чтобы получить полное имя.

Ниже приведены шаги по преобразованию данных в таблицу Excel:

В диалоговом окне «Создание таблицы» убедитесь, что диапазон указан правильно.

Вышеупомянутые шаги преобразуют диапазон данных в таблицу Excel.

Теперь давайте посмотрим, как объединить имя и фамилию с помощью Power Query:

В группе «Получить и преобразовать данные» выберите параметр «Из листа». Откроется редактор Power Query.

В редакторе Power Query убедитесь, что на левой панели выбрана правая таблица. Если у вас всего одна таблица, вы увидите только одну опцию на левой панели.
Выберите столбцы, которые необходимо объединить (удерживайте клавишу Control, а затем щелкните заголовок столбца, чтобы выбрать столбец)

Щелкните правой кнопкой мыши, а затем выберите параметр «Объединить столбцы».

В открывшемся диалоговом окне «Объединить столбцы» выберите «Пробел» в качестве разделителя (в раскрывающемся списке).

Щелкните ОК. Это удалит два столбца, которые у нас уже есть, и заменит их новым столбцом с полным именем.

Перейдите на вкладку «Файл», а затем нажмите «Закрыть и загрузить».

Вышеупомянутые шаги позволят вставить новый лист в книгу с таблицей, в которой есть один столбец с полным именем.

Если вам нужны исходные столбцы, а также новый столбец, содержащий полное имя, вместо того, чтобы выбирать столбцы и объединять их, сначала выберите вкладку «Добавить столбец» в редакторе Power Query, затем выберите столбцы, а затем нажмите «Объединить». Колонны. Это добавит новый столбец с полным именем.

По сравнению с методами формул и Flash Fill, Power Query определенно немного длиннее.

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

Кроме того, Power Query широко используется для объединения различных таблиц и данных из нескольких листов и книг. Итак, если у вас есть данные об именах, которые вы хотите объединить, это один шаг в уже существующем рабочем процессе запроса мощности.

В этом уроке я рассказал, как объединить имя и фамилию в Excel . Но если у вас есть имя, отчество и фамилия, вы можете использовать для этого те же методы.

Читайте также: