Содержание
- Как в экселе найти фамилию в списке?
- Видео
- Поиск данных в таблице или диапазоне ячеек с помощью встроенных функций Excel
- Описание
- Создание образца листа
- Определения терминов
- Функции
- LOOKUP ()
- INDEX () и MATCH ()
- СМЕЩ () и MATCH ()
- Поиск в программе Microsoft Excel
- Поисковая функция в Excel
- Способ 1: простой поиск
- Способ 2: поиск по указанному интервалу ячеек
- Способ 3: Расширенный поиск
Как в экселе найти фамилию в списке?
Давайте разберемся, как можно найти фамилию в списке в программе эксель. Сделаем это на конкретном примере, перед нами таблица, состоящая из десяти ФИО, нам нужно найти людей с фамилией Сидоренко.
Первый способ.
Воспользуемся фильтром, чтобы найти нужную фамилию. Для этого выделим ячейку «А1», а на верхней панели настроек перейдем во вкладку «Данные», где нажмем на иконку в виде воронки и имеющую подпись «Филтьр».
В ячейке «А1» появится слева стрелочка, нужно нажать и перед вами откроется меню. Нажмем на строчку «Текстовые фильтры», в появившемся списке выберем строку «Содержит».
В появившемся меню, наберем фамилию Сидоренко, после нажмем на кнопку «ОК».
В итоге таблица отформатируется, и останутся только люди с фамилией Сидоренко.
Второй способ.
Снова перед нами та же табличка, только теперь нажмем на клавиатуре сочетание клавиш «Ctrl+F», а в строке «Найти» наберем слово «Сидоренко». После нажимаем на кнопку «Найти все». В нижней части меню, отразиться адреса ячеек, в которых содержится фамилия Сидоренко.
Видео
Источник
Поиск данных в таблице или диапазоне ячеек с помощью встроенных функций Excel
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Описание
В этой статье приведены пошаговые инструкции по поиску данных в таблице (или диапазоне ячеек) с помощью различных встроенных функций Microsoft Excel. Для получения одного и того же результата можно использовать разные формулы.
Создание образца листа
В этой статье используется образец листа для иллюстрации встроенных функций Excel. Рассматривайте пример ссылки на имя из столбца A и возвращает возраст этого человека из столбца C. Чтобы создать этот лист, введите указанные ниже данные в пустой лист Excel.
Введите значение, которое вы хотите найти, в ячейку E2. Вы можете ввести формулу в любую пустую ячейку на том же листе.
Определения терминов
В этой статье для описания встроенных функций Excel используются указанные ниже условия.
Вся таблица подстановки
Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».
Просматриваемый_массив
-или-
Лукуп_вектор
Диапазон ячеек, которые содержат возможные значения подстановки.
Номер столбца в аргументе инфо_таблица, для которого должно быть возвращено совпадающее значение.
3 (третий столбец в инфо_таблица)
Ресулт_аррай
-или-
Ресулт_вектор
Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.
Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.
Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?.
Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение «5» в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).
Функции
LOOKUP ()
Функция Просмотр находит значение в одной строке или столбце и сопоставляет его со значением в той же позицией в другой строке или столбце.
Ниже приведен пример синтаксиса формулы подСТАНОВКи.
= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)
Следующая формула находит возраст Марии на листе «образец».
= ПРОСМОТР (E2; A2: A5; C2: C5)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как «Мария» находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).
Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.
Чтобы получить дополнительные сведения о функции Просмотр , щелкните следующий номер статьи базы знаний Майкрософт:
Функция ВПР или вертикальный просмотр используется, если данные указаны в столбцах. Эта функция выполняет поиск значения в левом столбце и сопоставляет его с данными в указанном столбце в той же строке. Функцию ВПР можно использовать для поиска данных в отсортированных или несортированных таблицах. В следующем примере используется таблица с несортированными данными.
Ниже приведен пример синтаксиса формулы ВПР :
= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)
Следующая формула находит возраст Марии на листе «образец».
= ВПР (E2; A2: C5; 3; ЛОЖЬ)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется «3» в качестве Колумн_индекс (столбец C). Так как «Мария» находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).
Чтобы получить дополнительные сведения о функции ВПР , щелкните следующий номер статьи базы знаний Майкрософт:
INDEX () и MATCH ()
Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.
Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:
= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)
Следующая формула находит возраст Марии на листе «образец».
= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как «Мария» находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).
Обратите внимание Если ни одна из ячеек в аргументе «число» не соответствует искомому значению («Мария»), эта формула будет возвращать #N/А.
Чтобы получить дополнительные сведения о функции индекс , щелкните следующий номер статьи базы знаний Майкрософт:
СМЕЩ () и MATCH ()
Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.
Ниже приведен пример синтаксиса, объединяющего смещение и сопоставление для достижения того же результата, что и функция Просмотр и ВПР.
= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)
Эта формула находит возраст Марии на листе «образец».
= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как «Мария» находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).
Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:
Источник
Поиск в программе Microsoft Excel
В документах Microsoft Excel, которые состоят из большого количества полей, часто требуется найти определенные данные, наименование строки, и т.д. Очень неудобно, когда приходится просматривать огромное количество строк, чтобы найти нужное слово или выражение. Сэкономить время и нервы поможет встроенный поиск Microsoft Excel. Давайте разберемся, как он работает, и как им пользоваться.
Поисковая функция в Excel
Поисковая функция в программе Microsoft Excel предлагает возможность найти нужные текстовые или числовые значения через окно «Найти и заменить». Кроме того, в приложении имеется возможность расширенного поиска данных.
Способ 1: простой поиск
Простой поиск данных в программе Excel позволяет найти все ячейки, в которых содержится введенный в поисковое окно набор символов (буквы, цифры, слова, и т.д.) без учета регистра.
- Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.
Поиск и выдача результатов производится построчно. Сначала обрабатываются все ячейки первой строки. Если данные отвечающие условию найдены не были, программа начинает искать во второй строке, и так далее, пока не отыщет удовлетворительный результат.
Поисковые символы не обязательно должны быть самостоятельными элементами. Так, если в качестве запроса будет задано выражение «прав», то в выдаче будут представлены все ячейки, которые содержат данный последовательный набор символов даже внутри слова. Например, релевантным запросу в этом случае будет считаться слово «Направо». Если вы зададите в поисковике цифру «1», то в ответ попадут ячейки, которые содержат, например, число «516».
Для того, чтобы перейти к следующему результату, опять нажмите кнопку «Найти далее».
Так можно продолжать до тех, пор, пока отображение результатов не начнется по новому кругу.
Способ 2: поиск по указанному интервалу ячеек
Если у вас довольно масштабная таблица, то в таком случае не всегда удобно производить поиск по всему листу, ведь в поисковой выдаче может оказаться огромное количество результатов, которые в конкретном случае не нужны. Существует способ ограничить поисковое пространство только определенным диапазоном ячеек.
- Выделяем область ячеек, в которой хотим произвести поиск.
- Набираем на клавиатуре комбинацию клавиш Ctrl+F, после чего запуститься знакомое нам уже окно «Найти и заменить». Дальнейшие действия точно такие же, что и при предыдущем способе. Единственное отличие будет состоять в том, что поиск выполняется только в указанном интервале ячеек.
Способ 3: Расширенный поиск
Как уже говорилось выше, при обычном поиске в результаты выдачи попадают абсолютно все ячейки, содержащие последовательный набор поисковых символов в любом виде не зависимо от регистра.
К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2. Как такое могло получиться? Просто в ячейке E2 в качестве формулы содержится адрес на ячейку A4, который как раз включает в себя искомую цифру 4.
Но, как отсечь такие, и другие заведомо неприемлемые результаты выдачи поиска? Именно для этих целей существует расширенный поиск Excel.
- После открытия окна «Найти и заменить» любым вышеописанным способом, жмем на кнопку «Параметры».
- В окне появляется целый ряд дополнительных инструментов для управления поиском. По умолчанию все эти инструменты находятся в состоянии, как при обычном поиске, но при необходимости можно выполнить корректировку.
По умолчанию, функции «Учитывать регистр» и «Ячейки целиком» отключены, но, если мы поставим галочки около соответствующих пунктов, то в таком случае, при формировании результата будет учитываться введенный регистр, и точное совпадение. Если вы введете слово с маленькой буквы, то в поисковую выдачу, ячейки содержащие написание этого слова с большой буквы, как это было бы по умолчанию, уже не попадут. Кроме того, если включена функция «Ячейки целиком», то в выдачу будут добавляться только элементы, содержащие точное наименование. Например, если вы зададите поисковый запрос «Николаев», то ячейки, содержащие текст «Николаев А. Д.», в выдачу уже добавлены не будут.
По умолчанию, поиск производится только на активном листе Excel. Но, если параметр «Искать» вы переведете в позицию «В книге», то поиск будет производиться по всем листам открытого файла.
В параметре «Просматривать» можно изменить направление поиска. По умолчанию, как уже говорилось выше, поиск ведется по порядку построчно. Переставив переключатель в позицию «По столбцам», можно задать порядок формирования результатов выдачи, начиная с первого столбца.
В графе «Область поиска» определяется, среди каких конкретно элементов производится поиск. По умолчанию, это формулы, то есть те данные, которые при клике по ячейке отображаются в строке формул. Это может быть слово, число или ссылка на ячейку. При этом, программа, выполняя поиск, видит только ссылку, а не результат. Об этом эффекте велась речь выше. Для того, чтобы производить поиск именно по результатам, по тем данным, которые отображаются в ячейке, а не в строке формул, нужно переставить переключатель из позиции «Формулы» в позицию «Значения». Кроме того, существует возможность поиска по примечаниям. В этом случае, переключатель переставляем в позицию «Примечания».
Ещё более точно поиск можно задать, нажав на кнопку «Формат».
При этом открывается окно формата ячеек. Тут можно установить формат ячеек, которые будут участвовать в поиске. Можно устанавливать ограничения по числовому формату, по выравниванию, шрифту, границе, заливке и защите, по одному из этих параметров, или комбинируя их вместе.
Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».
После этого, появляется инструмент в виде пипетки. С помощью него можно выделить ту ячейку, формат которой вы собираетесь использовать.
После того, как формат поиска настроен, жмем на кнопку «OK».
Бывают случаи, когда нужно произвести поиск не по конкретному словосочетанию, а найти ячейки, в которых находятся поисковые слова в любом порядке, даже, если их разделяют другие слова и символы. Тогда данные слова нужно выделить с обеих сторон знаком «*». Теперь в поисковой выдаче будут отображены все ячейки, в которых находятся данные слова в любом порядке. 
Как видим, программа Excel представляет собой довольно простой, но вместе с тем очень функциональный набор инструментов поиска. Для того, чтобы произвести простейший писк, достаточно вызвать поисковое окно, ввести в него запрос, и нажать на кнопку. Но, в то же время, существует возможность настройки индивидуального поиска с большим количеством различных параметров и дополнительных настроек.
Источник
Поиск значений в списке данных
Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
Предположим, что вы хотите найти расширение телефона сотрудника, используя его номер эмблемы или правильную ставку комиссионных за объем продаж. Вы можете искать данные для быстрого и эффективного поиска определенных данных в списке, а также для автоматической проверки правильности данных. После поиска данных можно выполнить вычисления или отобразить результаты с возвращаемой величиной. Существует несколько способов поиска значений в списке данных и отображения результатов.
Что необходимо сделать
-
Точное совпадение значений по вертикали в списке
-
Подыыывка значений по вертикали в списке с помощью приблизительного совпадения
-
Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения
-
Точное совпадение значений по горизонтали в списке
-
Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения
-
Создание формулы подступа с помощью мастера подметок (только в Excel 2007)
Точное совпадение значений по вертикали в списке
Для этого можно использовать функцию ВLOOKUP или сочетание функций ИНДЕКС и НАЙТИПОЗ.
Примеры ВРОТ
Дополнительные сведения см. в этой информации.
Примеры индексов и совпадений
Что означает:
=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение “Капуста” в массиве B2:B10))
Формула ищет в C2:C10 первое значение, соответствующее значению “Ольга” (в B7), и возвращает значение в C7(100),которое является первым значением, которое соответствует значению “Ольга”.
Дополнительные сведения см. в функциях ИНДЕКС иФУНКЦИЯ MATCH.
К началу страницы
Подыыывка значений по вертикали в списке с помощью приблизительного совпадения
Для этого используйте функцию ВЛВП.
Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.
В примере выше ВРОТ ищет имя учащегося, у которого 6 просмотров в диапазоне A2:B7. В таблице нет записи для 6 просмотров, поэтому ВРОТ ищет следующее самое высокое совпадение меньше 6 и находит значение 5, связанное с именем Виктор,и таким образом возвращает Его.
Дополнительные сведения см. в этой информации.
К началу страницы
Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения
Для этого используйте функции СМЕЩЕНИЕ и НАЙТИВМЕСЯК.
Примечание: Используйте этот подход, если данные в диапазоне внешних данных обновляются каждый день. Вы знаете, что цена находится в столбце B, но вы не знаете, сколько строк данных возвращает сервер, а первый столбец не отсортировали по алфавиту.
C1 — это левые верхние ячейки диапазона (также называемые начальной).
MATCH(“Оранжевая”;C2:C7;0) ищет “Оранжевые” в диапазоне C2:C7. В диапазон не следует включать запускаемую ячейку.
1 — количество столбцов справа от начальной ячейки, из которых должно быть возвращено значение. В нашем примере возвращается значение из столбца D, Sales.
К началу страницы
Точное совпадение значений по горизонтали в списке
Для этого используйте функцию ГГПУ. См. пример ниже.
Г ПРОСМОТР ищет столбец “Продажи” и возвращает значение из строки 5 в указанном диапазоне.
Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.
К началу страницы
Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения
Для этого используйте функцию ГГПУ.
Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.
В примере выше ГЛЕБ ищет значение 11000 в строке 3 указанного диапазона. Она не находит 11000, поэтому ищет следующее наибольшее значение меньше 1100 и возвращает значение 10543.
Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.
К началу страницы
Создание формулы подступа с помощью мастера подметок (толькоExcel 2007 )
Примечание: В Excel 2010 больше не будет надстройки #x0. Эта функция была заменена мастером функций и доступными функциями подменю и справки (справка).
В Excel 2007 создается формула подытов на основе данных на основе данных на основе строк и столбцов. Если вы знаете значение в одном столбце и наоборот, мастер под поисков помогает находить другие значения в строке. В формулах, которые он создает, используются индекс и MATCH.
-
Щелкните ячейку в диапазоне.
-
На вкладке Формулы в группе Решения нажмите кнопку Под поиск.
-
Если команда Подытов недоступна, вам необходимо загрузить мастер под надстройка подытогов.
Загрузка надстройки “Мастер подстройок”
-
Нажмите кнопку Microsoft Office
, выберите Параметры Excel и щелкните категорию Надстройки. -
В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.
-
В диалоговом окне Доступные надстройки щелкните рядом с полем Мастер подстрок инажмите кнопку ОК.
-
Следуйте инструкциям мастера.
, выберите Параметры Excel и щелкните категорию Надстройки.К началу страницы
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Содержание:
- Извлечь и отсортировать по фамилии с помощью функции поиска и замены
- Извлечь и расположить в алфавитном порядке по фамилии с помощью формулы
- Использование текста в столбцы
- Использование Flash Fill
Если вы работаете с наборами данных имен, сортировка — одна из распространенных задач, которые вам придется часто выполнять.
Сортировать данные в алфавитном порядке по полному имени довольно просто, а Excel использует первый символ имени для сортировки.
Но что, если ты хочешь сортировать данные по фамилии в Excel?
Хотя это не так просто, но все же можно сделать (многое также зависит от того, как структурированы данные об именах).
Независимо от того, какой метод вы используете, вам придется каким-то образом извлечь фамилию из полного имени и поместить ее в отдельный столбец. Затем вы можете использовать этот столбец для сортировки данных по фамилии в алфавитном порядке.
В этом руководстве по Excel я покажу вам, как отсортировать столбец с именами на основе фамилии.
Итак, приступим!
Извлечь и отсортировать по фамилии с помощью функции поиска и замены
Первый шаг к сортировке по фамилии — занести фамилию в отдельный столбец.
Вы можете сделать это, заменив все перед фамилией пробелом, чтобы у вас осталась только фамилия.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите отсортировать эти данные в алфавитном порядке по фамилии.
Ниже приведены шаги для сортировки по фамилии:
- Выберите набор данных, включая заголовок (в этом примере это будет A1: A10)
- Скопируйте его в соседний столбец (если соседний столбец не пустой, вставьте новый столбец, а затем скопируйте эти имена)
- Переименуйте скопированный заголовок столбца. В этом примере я назову «Фамилия».
- Выделите все скопированные имена (не выбирайте заголовок)
- Удерживая клавишу Ctrl, нажмите клавишу H. Откроется диалоговое окно «Найти и заменить».
- В поле «Найти» введите * (символ звездочки, за которым следует пробел).
- Оставьте поле «Заменить на» пустым.
- Нажмите «Заменить все». Это мгновенно заменит все имя, и у вас останутся только фамилии.
Вышеупомянутые шаги сохранят фамилию и удалят все, что перед ней. Это хорошо работает, даже если у вас есть отчество или префиксы (например, Mr. или Ms).
Если у вас есть фамилии в соседнем столбце, вы можете легко отсортировать набор данных (включая полные имена) в алфавитном порядке по фамилии.
Ниже приведены шаги для сортировки по фамилии:
- Выберите весь набор данных с заголовками (включая полные имена и извлеченные фамилии). Вы также можете включить другие столбцы, которые хотите отсортировать, вместе с именами
- Перейдите на вкладку «Данные».
- Нажмите на Сортировать
- Убедитесь, что в диалоговом окне «Сортировка» выбрано «Мои данные имеют заголовки».
- В параметре «Сортировать по» выберите имя столбца, в котором есть только фамилия.
- В поле «Сортировка по» выберите «Значения ячеек».
- В разделе «Порядок» выберите «От А до Я».
- Нажмите ОК.
Вышеупомянутые шаги сортируют весь выбранный набор данных по фамилии.
После этого вы можете удалить столбец с фамилией.
Совет профессионала: В любой момент времени, если вы думаете, что вам может понадобиться вернуть исходные данные, вам нужно иметь способ распаковать этот набор данных. Для этого в соседнем столбце (левом или правом) укажите порядковые номера перед сортировкой. Теперь, если вам нужно вернуть исходные данные, вы получите их путем сортировки по числам.
Извлечь и расположить в алфавитном порядке по фамилии с помощью формулы
Хотя я предпочитаю получать все фамилии и сортировать на их основе метод, показанный выше (с использованием поиска и замены), одним из его ограничений является то, что результирующие данные статичны.
Это означает, что если я добавлю больше имен в свой список, мне придется повторить тот же процесс еще раз, чтобы получить фамилии.
Если вам это не нужно, вы можете использовать метод формулы для сортировки данных по фамилиям.
Предположим, у вас есть набор данных, как показано ниже.
Ниже приведена формула, которая извлечет фамилию из полного имени:= ВПРАВО (A2; LEN (A2) -FIND (""; A2))
Вышеупомянутая формула основана на шаблоне с полным именем (который в этом примере содержит только имя и фамилию). Шаблон состоит в том, что между именем и фамилией будет пробел.
Функция НАЙТИ используется для получения позиции символа пробела. Затем это значение вычитается из общей длины имени, чтобы получить общее количество символов в фамилии.
Это значение затем используется в функции ВПРАВО для получения фамилии.
Когда у вас есть столбец с фамилией, вы можете отсортировать эти данные (это подробно рассматривается в первом методе).
Приведенная выше формула будет работать, если у вас есть только имя и фамилия.
Но что, если у вас есть отчество? Или перед именем может стоять приветствие (например, г-н или г-жа).
В таком случае вам нужно использовать следующую формулу:= ПРАВО (A2; LEN (A2) -НАЙТИ ("@", ПОДСТАВИТЬ (A2, "", "@", LEN (A2) -LEN (ПОДСТАВИТЬ (A2, "", ""))))))
Приведенная выше формула находит позицию последнего символа пробела и затем использует ее для извлечения фамилии.
Я рекомендую вам использовать вторую формулу во всех случаях, она более надежна и может обрабатывать все случаи (если фамилия стоит в конце имени).
Примечание. Эти две формулы основаны на условии, что между каждым элементом имени есть только один пробел. Если есть двойные пробелы или начальные / конечные пробелы, эта формула даст неверные результаты. В таком случае лучше всего использовать функцию TRIM, чтобы сначала избавиться от любых начальных, конечных и двойных пробелов, а затем использовать приведенную выше формулу.
Хотя это может показаться сложным методом, преимущество использования формулы состоит в том, что она делает результаты динамичными. Если вы добавите больше имен в свой список, все, что вам нужно сделать, это скопировать формулу, и она даст вам фамилию.
Использование текста в столбцы
Текст в столбцы — это снова простой и легкий способ разбить ячейки в Excel.
Вы можете указать разделитель (например, запятую или пробел) и использовать его для разделения содержимого ячейки. После того, как у вас есть разделенные элементы в отдельных столбцах, вы можете использовать столбец с фамилией, чтобы расположить данные в алфавитном порядке.
Предположим, у вас есть набор данных, как показано ниже:
Ниже приведены шаги по использованию текста в столбец для сортировки по фамилии:
- Выберите столбец с именем (без заголовка)
- Перейдите на вкладку «Данные».
- В группе «Инструменты для работы с данными» выберите параметр «Преобразование текста в столбцы». Откроется мастер текста в столбцы.
- На шаге 1 «Мастера преобразования текста в столбцы» выберите «С разделителями» и нажмите «Далее».
- На шаге 2 выберите «Пробел» в качестве разделителя (и снимите все флажки, если они выбраны), а затем нажмите кнопку «Далее».
- На шаге 3 выберите первый столбец имени в окне предварительного просмотра данных, а затем выберите параметр «Не импортировать столбцы (пропустить)». Это гарантирует, что имя не будет частью результата, а вы получите только фамилию.
- Также на шаге 3 измените целевую ячейку на ту, которая находится рядом с исходными данными. Это гарантирует, что вы получите фамилию отдельно, а исходные данные об именах останутся нетронутыми.
- Нажмите Готово
Получив результат, вы можете отсортировать его по фамилии.
Вы также можете преобразовать текст в столбцы, чтобы разделить имя и фамилию, если у вас есть запятая в качестве разделителя.
Использование Flash Fill
Еще один быстрый способ получить фамилии — использовать функцию Flash Fill.
Flash Fill был представлен в Excel 2013 и помогает манипулировать данными путем выявления шаблонов. Чтобы это сработало, вам нужно пару раз показать ожидаемый результат Flash Fill.
Как только он определит шаблон, он быстро сделает остальную работу за вас.
Предположим, у вас есть набор данных с именами ниже.
Ниже приведены шаги по использованию Flash Fill для получения фамилии и последующей сортировки по ней:
- В ячейке B2 введите текст «Мори». Это результат, который вы ожидаете в ячейке.
- Перейдите в следующую ячейку и введите фамилию для имени в соседней ячейке (Эллиот в этом примере).
- Выберите обе ячейки
- Наведите курсор на правую нижнюю часть выделения. Вы заметите, что курсор изменится на значок плюса.
- Дважды щелкните по нему (или щелкните и перетащите его вниз). Это даст вам какой-то результат в ячейках (вряд ли это будет тот результат, который вам нужен)
- Щелкните значок «Параметры автозаполнения».
- Нажмите на Flash Fill.
Это даст вам результат, который, скорее всего, будет фамилиями во всех ячейках.
Я говорю «вероятно», поскольку в некоторых случаях Flash Fill может не работать. Поскольку это зависит от определения шаблона, это не всегда удается. Или иногда, шаблон, который он расшифровывает, может быть неправильным.
В таких случаях следует ввести ожидаемый результат еще в одну или две ячейки, а затем выполнить шаги 4-7.
Когда у вас есть все фамилии в столбце, вы можете отсортировать данные на основе этих фамилий.
Итак, это четыре разных способа сортировки данных по фамилии. Лучшим способом было бы использовать технику «Найти и заменить», но если вы хотите сделать свои результаты динамическими, лучше всего подойдет метод формул.
Надеюсь, вы нашли этот урок полезным.
Ольга Г.
15 октября 2021 ·
Род деятельности – аналитик (экономика).
Увлечения: духовное развитие человека… · 17 окт 2021
Добрый день.
Как вариант, применить связку формул Excel: ИНДЕКС() + ПОИСКПОЗ(), в частности:
=ИНДЕКС(A:C;ПОИСКПОЗ(E3;B:B;0);3)
Таблица 1 – столбцы А:С
ПОИСКПОЗ(E3;B:B;0;0) – поиск номера строки (5) по заданной ФИО (E3 = ФИО_2) в столбце B Таблицы 1;
ИНДЕКС(A:C;ПОИСКПОЗ(E3;B:B;0);3) – отображение найденного номера телефона из столбцов A:C по номеру найденной строки и 3-му столбцу (С).
Подробнее :
Комментировать ответ…Комментировать…
Клиенты для офлайн-бизнеса в срок от 3 дней · 15 окт 2021
Я использовал два варианта в таких случаях:
1) с помощью формулы в эксель, которая ищет по совпадению в ячейке (но нужно, чтобы формат данных был одинаковый)
2) через гугл контакты (импортировать + объединить похожие)
Комментировать ответ…Комментировать…
|
cobraN Пользователь Сообщений: 10 |
Привет. В экселе почти не разбираюсь. Как сделать, чтобы на листе “Прайс” в графе “Фамилия” я ввожу “Фамилию Имя” и должен появляться “Номер ID” из листа “База”, присвоенный к этой фамилии. И как быть с повторами? Допустим, уже есть такая “Фамилия Имя”, но номера у людей разные. Спасибо. Файл-пример прикрепил. Админы, не закрывайте тему, а лучше помогите! |
|
cobraN Пользователь Сообщений: 10 |
Хочу добавить, что некоторые люди не знают свой ИД, поэтому я хочу сделать такую базу по фамилии. Их номера записаны у меня в отдельном файле, но каждый раз просматривать их и искать очень долго. Там больше 1500 человек |
|
Alsu Пользователь Сообщений: 31 |
Можно сделать через ВПР, но для этого нужно, чтобы присутсвовал ещё один признак. Или связные выпадающие списки. |
|
cobraN Пользователь Сообщений: 10 |
{quote}{login=Мяу™}{date=06.08.2010 10:19}{thema=Автоматический поиск номера по фамилии в др. ячейке}{post}Можно сделать через ВПР, но для этого нужно, чтобы присутсвовал ещё один признак. Или связные выпадающие списки.{/post}{/quote} Не могли бы написать точную формулу? Я в них ничего не понимаю( |
|
VistaS Пользователь Сообщений: 33 |
{quote}{login=cobraN}{date=06.08.2010 10:30}{thema=Re: Автоматический поиск номера по фамилии в др. ячейке}{post}{quote}{login=Мяу™}{date=06.08.2010 10:19}{thema=Автоматический поиск номера по фамилии в др. ячейке}{post}Можно сделать через ВПР, но для этого нужно, чтобы присутсвовал ещё один признак. Или связные выпадающие списки.{/post}{/quote} Не могли бы написать точную формулу? Я в них ничего не понимаю({/post}{/quote} |
|
На листе База – скрытый дополнительный столбец С. |
|
|
cobraN Пользователь Сообщений: 10 |
{quote}{login=VistaS}{date=06.08.2010 10:54}{thema=Re: Re: Автоматический поиск номера по фамилии в др. ячейке}{post}{quote}{login=cobraN}{date=06.08.2010 10:30}{thema=Re: Автоматический поиск номера по фамилии в др. ячейке}{post}{quote}{login=Мяу™}{date=06.08.2010 10:19}{thema=Автоматический поиск номера по фамилии в др. ячейке}{post}Можно сделать через ВПР, но для этого нужно, чтобы присутсвовал ещё один признак. Или связные выпадающие списки.{/post}{/quote} Не могли бы написать точную формулу? Я в них ничего не понимаю({/post}{/quote} У нас американская компания и отчеств нет). Первая формула у меня не работает, а если ввожу вторую, то при пустом значении пишется Н.Д с ошибкой. Как устранить? П.С. кто-нибудь может доработать эту форуму до совершенства? |
|
cobraN Пользователь Сообщений: 10 |
|
|
VistaS Пользователь Сообщений: 33 |
=ЕСЛИ(C4>0;ИНДЕКС(База!A:B;ПОИСКПОЗ(Прайс!C4;База!B:B;0);1);””)чтобы не было Н/Д |
|
{quote}{login=VistaS}{date=06.08.2010 11:17}{thema=}{post}=ЕСЛИ(C4>0;ИНДЕКС(База!A:B;ПОИСКПОЗ(Прайс!C4;База!B:B;0);1);””)чтобы не было Н/Д Нет)) Если только попробовать сделать раскрывающийся список, когда будут вводить фамилию, на которой несколько номеров, где в списке будут появляться только те номера, которые присвоины той фамилии |
|
|
А в таком виде формула не будет выдавать Н/Д и когда введённой фамилии нет в базе |
|
|
vikttur Пользователь Сообщений: 47199 |
#12 09.08.2010 13:35:30 Вариант. Прикрепленные файлы
|
