Если в таблице excel написать фамилии сотрудников в столбце а
В прошлом выпуске мы рассмотрели задачу, в которой нужно было определить сотрудника с максимальным возрастом и вывести его имя в отдельную ячейку.
Предложенное решение этой задачи имеет один существенный недостаток. Дело в том, что функция МАКС возвращает первое максимальное значение из диапазона и на этом весь расчет прекращается. Это означает, что если в списке будет два или больше сотрудников с одинаковым возрастом, то в результатах мы увидим лишь первого по списку.
Давайте решим задачу таким образом, чтобы получился список всех сотрудников, имеющих максимальный возраст.
Итак, первым делом мы по-прежнему должны определить максимальный возраст. Для этого вполне подойдет функция МАКС , которую мы уже использовали ранее.
Ну а далее нам нужно сравнивать возраст каждого сотрудника с максимальным и в случае совпадения выводить имя сотрудника в столбце F ниже.
На первый взгляд ничего сложного, но не так все просто, как кажется и сейчас вы в этом убедитесь.
Дело в том, что все стандартные функции Экселя, которые сразу приходят на ум, выдадут лишь первое значение из диапазона, удовлетворяющее условию. Поэтому нам придется воспользоваться формулой массива .
Формула массива является частью стандартного функционала Экселя и многие даже не догадываются об ее существовании.
По сути, формула массива позволяет выполнять несколько вычислений с одним или несколькими элементами диапазона ячеек, то есть элементами массива. В итоге формула массива возвращает либо один результат вычислений, либо несколько результатов, то есть массив значений, что нам и нужно.
Не буду сейчас вдаваться в подробности, так как эта заметка и так будет довольно длинной. Если вы хотите узнать больше о формуле массива, то напишите об этом в комментариях и я раскрою тему подробнее в одной из следующих заметок.
Итак, давайте решим задачу.
Как и в прошлый раз , воспользуемся функцией ИНДЕКС , которая возвращает значение ячейки, заданного номером строки и номером столбца.
Первый ее аргумент - массив значений. Выбираем все ячейки с именами сотрудников.
Если фамилия, имя и отчество записываются в разные ячейки, работать с ними легче, чем при размещении их в одной ячейке. Перечислим преимущества:
- при заполнении таких ячеек в таблицах срабатывает автоподбор значений, так как имена и отчества часто повторяются;
- для заполнения таких ячеек можно использовать раскрывающиеся списки наиболее распространенных имен и отчеств;
- фамилия, имя и отчество, записанные в разные ячейки, легко объединить в одну, а имя и отчество заменить инициалами.
Итак, объединяем фамилию, имя и отчество полностью из разных ячеек в одну:
Здесь можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(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);".")
Вы можете копировать эти формулы в свои файлы, не забывая изменять адреса ячеек на фактические. Часто инициалы пишут перед фамилией, изменить формулы для такого отображения ФИО несложно.
Добрый день! В приложении образец. Там есть список фамилий и их бейджы, мне нужно чтобы в основную таблицу, в столбце "бейдж сотрудника" вставляем его бейдж и рядом в столбце "фамилия сотрудника" появлялась его фамилия. Заранее спасибо.
Добрый день! В приложении образец. Там есть список фамилий и их бейджы, мне нужно чтобы в основную таблицу, в столбце "бейдж сотрудника" вставляем его бейдж и рядом в столбце "фамилия сотрудника" появлялась его фамилия. Заранее спасибо. Shibal
Сделал Вам выпадающий список в столбце С, чтобы не было ошибок ввода номеров бейджей, добавил 2 именованных динамических диапазона (нажмите Контрл F3 - увидите их), чтобы можно было добавлять значения в таблицу фамилий и формула в столбце В
Сделал Вам выпадающий список в столбце С, чтобы не было ошибок ввода номеров бейджей, добавил 2 именованных динамических диапазона (нажмите Контрл F3 - увидите их), чтобы можно было добавлять значения в таблицу фамилий и формула в столбце В
в формуле =ЕСЛИ(C10="";"";ИНДЕКС(Фамилия;ПОИСКПОЗ(C10;Бейдж;))) не могу указать другую ячейку фамилия она у меня в столбце K
в формуле =ЕСЛИ(C10="";"";ИНДЕКС(Фамилия;ПОИСКПОЗ(C10;Бейдж;))) не могу указать другую ячейку фамилия она у меня в столбце K Shibal
Для этого Вам нужно пойти в диспетчер имен (назовем его ДИ) (Контрл F3) и поменять столбцы там.
Пошагово - лезете в ДИ, прописываете там формулы для двух имен. Какие - посмотрите у меня в файле. Затем прописываете формулу в В6 и тянете ее вниз строк на 50-100 (можно больше, но без фанатизма). Затем выделяете столько же строк столбца С, Данные - Проверка данных; Тип данных - Список; Источник - пишете формулу =Бейдж. ОК
Попробуйте, если что-то не совсем ясно будет - пишите.
Для этого Вам нужно пойти в диспетчер имен (назовем его ДИ) (Контрл F3) и поменять столбцы там.
Пошагово - лезете в ДИ, прописываете там формулы для двух имен. Какие - посмотрите у меня в файле. Затем прописываете формулу в В6 и тянете ее вниз строк на 50-100 (можно больше, но без фанатизма). Затем выделяете столько же строк столбца С, Данные - Проверка данных; Тип данных - Список; Источник - пишете формулу =Бейдж. ОК
Попробуйте, если что-то не совсем ясно будет - пишите. _Boroda_
Для этого Вам нужно пойти в диспетчер имен (назовем его ДИ) (Контрл F3) и поменять столбцы там.
Пошагово - лезете в ДИ, прописываете там формулы для двух имен. Какие - посмотрите у меня в файле. Затем прописываете формулу в В6 и тянете ее вниз строк на 50-100 (можно больше, но без фанатизма). Затем выделяете столько же строк столбца С, Данные - Проверка данных; Тип данных - Список; Источник - пишете формулу =Бейдж. ОК
Попробуйте, если что-то не совсем ясно будет - пишите. Автор - _Boroda_
Дата добавления - 09.07.2015 в 17:32
по поводу формулы в ДИ. название листа в экселе "июль" столбец с фамилией с К2 по К9, а бейджи L2 по L9.
по поводу формулы в ДИ. название листа в экселе "июль" столбец с фамилией с К2 по К9, а бейджи L2 по L9. Shibal
Спасибо вам огромное, чтобы я без вас делал, это лучшее вообще в мире))) буду пробовать самостоятельно делать, ну думаю что не получиться, а можете сказать как эта функция вообще называется чтобы я мог найти подробную инструкцию.
Спасибо вам огромное, чтобы я без вас делал, это лучшее вообще в мире))) буду пробовать самостоятельно делать, ну думаю что не получиться, а можете сказать как эта функция вообще называется чтобы я мог найти подробную инструкцию. Shibal
Как быстро совместить имя и фамилию в одной ячейке в Excel?
Говорит, что у вас есть список имен с перечислением имен и фамилий отдельно в двух столбцах, теперь вы хотите создать полный список имен, объединив два столбца. Эта статья покажет вам несколько быстрых приемов объединения имени и фамилии в Excel.
Предположим, у вас есть список имен, как показано на скриншоте слева, и вам нужно получить список с полным именем, как показано на правом снимке экрана:
Разделите имя и фамилию на две ячейки с помощью Kutools for Excel
Объедините имя и фамилию с функциями
Есть две функции, которые могут быстро объединить имя и фамилию в одну ячейку.
1. = Объединить (A2, "", B2)
Шаг 1. В пустой ячейке, например C2, введите формулы = Объединить (A2; ""; B2) , см. снимок экрана:
Шаг 2: нажмите Enter и щелкните ячейку C2, затем перетащите маркер заполнения, чтобы скопировать формулы в диапазоне, который вы хотите заполнить. Тогда вы получите один столбец полного имени с объединением столбца имени и столбца фамилии.
2. = A2 & "" & B2
Вы можете использовать формулы = A2 & "" & B2 выполните те же действия, что и выше, чтобы получить список полного имени, объединив столбец имени и столбца фамилии.
Объедините имя и фамилию с помощью Kutools for Excel
Для Kutools for Excel's Комбинируйте строки, столбцы или ячейки без потери данных инструмент, вы можете быстро объединить содержимое из нескольких столбцов (или строк) в один столбец (или строку) без потери исходных данных.
После бесплатная установка Kutools for Excel, сделайте следующее:
Шаг 1: Выберите диапазон, с которым вы будете работать.
Шаг 2: нажмите Кутулс > Слияние и разделение > Комбинируйте строки, столбцы или ячейки без потери данных, см. снимок экрана:
Шаг 3: В Объединить столбцы и строки диалоговом окне укажите следующие параметры:
- Проверить Объединить столбцы вариант в Чтобы объединить выбранные ячейки в соответствии со следующими параметрами раздел;
- Проверить Space вариант в Укажите разделитель раздел;
- Укажите Доступные опции нужного вам комбинированного результата вы можете сохранить содержимое, удалить содержимое или объединить содержимое объединенных ячеек.
Шаг 4: Затем нажмите OK or Применить, вы получите полный список имен в столбце исходного имени, как показано на следующем снимке экрана:
Объединить имя и фамилию в одну ячейку
Разделите имя и фамилию на две ячейки с помощью Kutools for Excel
Но в некоторых случаях, когда у вас есть один столбец с именем и фамилией в каждой ячейке, возможно, теперь вы захотите разделить их на две ячейки, одна включает имя, а другая включает фамилию, как показано ниже:
В этом случае вы можете использовать Kutools for ExcelАвтора Разделить имена утилита для быстрого разделения имен на ячейки.
После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите столбец с именами и щелкните Кутулс > Слияние и разделение > Разделить имена. Смотрите скриншот:
2. В появившемся диалоговом окне отметьте нужные вам параметры. Если в вашем списке есть отчество, вы можете проверить Отчество вариант тоже. Смотрите скриншот:
3. Нажмите Ok и выберите одну ячейку, чтобы поместить результат.
4. Нажмите OK. Теперь выбранные имена разделены на две колонки: одна колонка с именем, а другая - с фамилией отдельно.
Этот метод представит Найти и заменить функция для извлечения фамилий из всех полных имен, а затем вы можете легко сортировать полные имена по фамилии. Пожалуйста, сделайте следующее:
1. Скопируйте столбец полного имени и вставьте его рядом со столбцом полного имени, говорится в столбце B.
2. Продолжайте выбирать вставленный столбец и нажмите Ctrl + H одновременно клавиши, чтобы открыть диалоговое окно «Найти и заменить».
Внимание: Вы также можете открыть это диалоговое окно, нажав Главная > Найти и выбрать > Замените.
3. В открывшемся диалоговом окне «Найти и заменить» введите пробел в поле Найти то, что: поле, введите @ в Заменить на: поле, а затем щелкните Заменить все кнопка. Смотрите скриншот:
Затем появится диалоговое окно, в котором будет показано, сколько замен было произведено, просто нажмите кнопку OK чтобы закрыть его.
4. Тем не менее выберите новый столбец и в диалоговом окне «Найти и заменить» введите *@ в Найти то, что: поле, ничего не вводите в Заменить на: и нажмите Заменить все кнопку.
Затем появится диалоговое окно, в котором будет показано, сколько замен было произведено, просто нажмите кнопку OK чтобы закрыть его.
5. Продолжайте выбирать новый столбец и щелкните Данные > Сортировка от А до Я or Сортировка Z по A, а затем в диалоговом окне предупреждения о сортировке установите флажок Расширить выбор и нажмите Сортировать кнопка. Смотрите скриншот:
Пока что столбец с полным именем отсортирован по извлеченным фамилиям. При необходимости вы можете удалить новый извлеченный столбец с фамилией.
С легкостью сортируйте по фамилии / слову в ячейках Excel
Kutools для Excel Расширенная сортировка Утилита поддерживает сортировку данных многими гибкими сценариями / методами, такими как сортировка по длине текста, сортировка по частоте, сортировка по фамилии и т. д. Полнофункциональная бесплатная 30-дневная пробная версия!
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Сортировать по фамилии с извлечением фамилии в первую очередь
Если мы можем извлечь фамилию из каждого полного имени, а затем легко отсортировать список полных имен по фамилиям. Этот метод представит Kutools for Excel's Разделить имена Утилита для извлечения сначала фамилий, а затем сортировки по фамилии.
Kutools for Excel- Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Выберите столбец с полным именем без заголовка столбца и нажмите Кутулс > Текст > Разделить имена. Затем в открывшемся диалоговом окне Split Names установите флажок Фамилия только вариант и щелкните Ok кнопка. Смотрите скриншот:
2. Во втором диалоговом окне Разделить имена укажите первую ячейку столбца назначения рядом с исходным столбцом полного имени и нажмите кнопку OK кнопку.
Затем все фамилии извлекаются из полных имен и помещаются в столбец исходного полного имени. Смотрите скриншот:
3. Выберите столбец с новой фамилией и нажмите Данные > Сортировка от А до Я or Сортировка Z по A, а затем в диалоговом окне предупреждения о сортировке установите флажок Расширить выбор и нажмите Сортировать кнопка. Смотрите скриншот:
И теперь столбец с полным именем отсортирован по фамилиям. При необходимости удалите созданный столбец с фамилией по мере необходимости.
Сортировка по фамилии с перестановкой имени и фамилии
Фактически, мы можем перевернуть ячейку с полным именем и поместить фамилию в начало ячейки с помощью Обратный порядок текста утилита Kutools for Excel, поэтому ее будет довольно легко отсортировать по фамилии.
Kutools for Excel- Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Выберите столбец с полным именем без заголовка столбца и нажмите Кутулс > Текст > Обратный порядок текста. Смотрите левый снимок экрана:
2. В открывшемся диалоговом окне «Обратный текст» установите флажок Space и нажмите Ok кнопка. Смотрите скриншот:
3. Выберите столбец полного имени и нажмите Данные > Сортировка от А до Я or Сортировка Z по A. Пока что столбец с полным именем уже отсортирован по фамилии. Но нам нужно восстановить нормальный порядок полных имен.
4. Повторите шаги 1 и 2, чтобы восстановить нормальный порядок полных имен.
Выберите столбец с полным именем без заголовка столбца и нажмите Кутулс > Текст > Обратный порядок текста. А затем проверьте Space и нажмите Ok в диалоговом окне Обратный текст. Смотрите скриншот:
Сортировка столбца полного имени по фамилии с помощью Kutools for Excel
Использование функции «Текст в столбец» или «Найти и заменить» для сортировки имени по фамилии не проще, чем применить Расширенная сортировка особенность Kutools for Excel.
Kutools for Excel- Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Выберите столбец полного имени, который вы хотите отсортировать по фамилии, и нажмите Кутулс Плюс > Расширенная сортировка для включения этой функции.
2. Во всплывающем окне Расширенная сортировка диалоговом окне выберите столбец, который вы хотите отсортировать, выберите Фамилия из Сортировать по в раскрывающемся списке указан порядок сортировки из заказ раскрывающийся список и щелкните значок Ok кнопка. Смотрите скриншот:
Пока что столбец полного имени отсортирован сразу по фамилии.
Демо: сортировка полных имен по фамилии в Excel
Читайте также: