Как заполнять трудовую книжку образец 2019: образец заполнения в 2023 году. Как заполнить трудовую книжку

  • Главная   /  Разное   /  
  • Как заполнять трудовую книжку образец 2019: образец заполнения в 2023 году. Как заполнить трудовую книжку

Как заполнять трудовую книжку образец 2019: образец заполнения в 2023 году. Как заполнить трудовую книжку

Содержание

Трудовые книжки 2019 \ Акты, образцы, формы, договоры \ КонсультантПлюс

  • Главная
  • Правовые ресурсы
  • Подборки материалов
  • Трудовые книжки 2019

Подборка наиболее важных документов по запросу Трудовые книжки 2019 (нормативно–правовые акты, формы, статьи, консультации экспертов и многое другое).

  • Трудовая книжка:
  • Акт об отказе в получении трудовой книжки
  • Акт об уничтожении трудовой книжки
  • Акт приема передачи трудовых книжек
  • Аннулирование записи в трудовой книжке о приеме на работу
  • Благодарность в трудовую книжку
  • Показать все
Еще
  • Трудовая книжка:
  • Акт об отказе в получении трудовой книжки
  • Акт об уничтожении трудовой книжки
  • Акт приема передачи трудовых книжек
  • Аннулирование записи в трудовой книжке о приеме на работу
  • Благодарность в трудовую книжку
  • Показать все

Формы документов

Судебная практика

Зарегистрируйтесь и получите пробный доступ к системе КонсультантПлюс бесплатно на 2 дня

Подборка судебных решений за 2020 год: Статья 22 «Сроки назначения страховой пенсии» Федерального закона «О страховых пенсиях»»Так, суд первой инстанции, сославшись на положения ч. 1 ст. 22 ФЗ «О страховых пенсиях», в соответствии с которыми страховая пенсия назначается со дня обращения за указанной пенсией, за исключением случаев, предусмотренных частями 5 и 6 настоящей статьи, но во всех случаях не ранее чем со дня возникновения права на указанную пенсию, обоснованно учел, что заявление Ж. по вопросу назначения пенсии подано им в пенсионный орган 03 июня 2019 г. При этом трудовая книжка, содержащая записи обо всех периодах работы истца, в том числе спорном периоде, была предъявлена ответчику при обращении. Установив на основании пенсионного дела, поступившего из Украины, что пенсия истцу выплачивалась на территории данного государства до 30 июня 2019 г., суд первой инстанции обоснованно возложил на ответчика обязанность назначить Ж. пенсию с 01 июля 2019 г.»

Зарегистрируйтесь и получите пробный доступ к системе КонсультантПлюс бесплатно на 2 дня

Подборка судебных решений за 2021 год: Статья 32 «Сохранение права на досрочное назначение страховой пенсии отдельным категориям граждан» Федерального закона «О страховых пенсиях»»Отказывая истцу в удовлетворении исковых требований об установлении факта работы в районах Крайнего Севера в период его работы автослесарем в ПСТО-86 поселок Советский, город Тюмень в период с 15 августа 1981 года по 03 декабря 1982 года и назначении досрочной страховой пенсии по старости на основании пункта 6 части 1 статьи 32 Федерального закона от 28 декабря 2013 года N 400-ФЗ «О страховых пенсиях» с 17 июля 2019 года, суд первой инстанции исходил из того, что трудовая книжка истцом утеряна, из архивных справок N 285 от 11 ноября 2019 года архивного отдела администрации Советского района ХМАО — Югры, N 32 от 06 февраля 2020 года архивного отдела администрации Советского района ХМАО — Югры, N Т-177 от 16 марта 2020 года ГБУТО «Государственный архив Тюменской области» следует, что сведений о таком работодателе, как «ПСТО-86», в архивах нет и документы по такому работодателю как «ПСТО-86» в архивы Ханты-Мансийского автономного округа — Югры и Тюменской области не поступали. «

Статьи, комментарии, ответы на вопросы

Правила заполнения трудовых книжек при приеме на работу

Лара Бодрова Статьи

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

Общие правила записи в трудовых книжках

Трудовые книжки должны заполняться по правилам, установленным постановлением Правительства РФ от 16 апреля 2003 года № 225 и инструкцией, утвержденной постановлением Минтруда № 69 от 10 октября 2003 года. Общие правила таковы:

  1. На титульном листе недопустимо производить каких-либо исправлений. Если допущена ошибка, то вносить правки в запись нельзя. Такой бланк трудовой книжки считается испорченным и подлежит уничтожению.
  2. Даты следует указывать в цифровом формате, например,
    01.03.2019.     Применение словесно-цифрового формата является не совсем корректным способом заполнения, но на практике работодателей за это не наказывают.
  3. Запись должна производиться аккуратно, шариковый, перьевой либо гелевой ручкой.
  4. В трудовой книжке нельзя указывать каких-либо сокращений. Например, нельзя писать «пр.» вместо «приказ» и так далее.
  5. Все записи в ТК должны нумероваться в пределах соответствующего раздела.

Возникает вопрос: нужно ли нумеровать записи, сделанные в связи с переименованием работодателя? Здесь есть небольшое противоречие: инструкция № 69 требует, чтобы такие записи не нумеровались, а в постановлении № 225 сказано, что должны быть пронумерованы все записи. В юридической практике в таком случае применяется правило из того нормативного акта,

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

Кадровиков часто интересует и другой вопрос — может ли организация ставить штамп со своим наименованием в трудовой книжке вместо того, чтобы писать его от руки? Если исходить четко из требований нормативных актов, то применение штампы не совсем корректно. Но на практике это допускается, то есть ПФР принимает такие трудовые книжки у работников без каких-либо вопросов. Важный момент — если вы используете штамп, то ставьте его

аккуратно и строго в пределах отведенной графы и строки.

Заполнение титульного листа

Если вы первый работодатель нанимаемого сотрудника, то должны оформить ему трудовую книжку «с нуля». Это придется сделать и в том случае, если новый работник утратил ТК и не имеет желания ее восстанавливать у бывшего работодателя.

На титульном листе указывается:

  1. ФИО и дата рождения. Указывается в полном соответствии с документом, удостоверяющим его личность. Ошибка хотя бы на одну букву или цифру создаст большие проблемы для работника в будущем. Не исключено, что ему впоследствии придется обращать в суд для признания того факта, что трудовая книжка действительно принадлежит именно ему.
  2. Образование работника. Указывать его нужно строго на основании документов об образовании. Важно учесть, что в 2015 году классификация образования изменилась. Например, сейчас нет такого вида, как среднее профессиональное или высшее профессиональное образование. Для сотрудников с высшим образованием можно указать, какую ступень они окончили, то есть прописать образование
    «высшее магистратура»     или «высшее бакалавриат». Но допустима и формулировка «высшее».
  3. Профессия, специальность работника. Эти сведения также нужно указать из документа об образовании. Бывает, что работник получил образование по направлению подготовки, то есть специальность, профессия либо квалификация ему не присвоена. В таком случае эту строку можно оставлять пустой.
  4. Подпись работника.
  5. Дата заполнения. Бланк ТК предполагает запись этой даты в словесно-цифровом формате, хотя в инструкции написано иное. На практике это проблем не вызывает, поэтому допустимы оба варианта.
  6. Подпись лица, ответственного за ведение ТК. В бланке указано, что подпись должна быть разборчивой, поэтому часто вместо подписи ответственные сотрудники прописывают свою фамилию. Это неверная практика, хотя она и очень распространена. Фамилия сотрудника кадровой службы может быть указана — это не является нарушение, но личная подпись при этом также должна быть.
  7. Если работодатель использует печать, то ее нужно поставить на титульном листе трудовой книжки.

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

Мнение о недействительности ТК без печати есть, и его источником является Пенсионный фонд. Органы ПФР зачастую раньше отказывали пенсионерам в принятии книжек без печатей, но суды всегда в таких спорах поддерживают граждан. Судьи говорят о том, что работники не обязаны нести ответственность за ошибки своих работодателей.

Если изменилась фамилия

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

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

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

Но это можно сделать в случае, если кончилось место на титульном листе.

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

Запись о приеме на работу

При найме на работу порядок заполнения трудовой книжке такой:

  1. В графе 3 указывается наименование работодателя в соответствии с учредительными документами — полное и сокращенное, если оно предусмотрено. Часто кадровики указывают только сокращенное наименование, например, ООО «Ромашка», однако это неверно.
  2. В графе 1 строчкой ниже наименования работодателя ставится порядковый номер записи.
  3. В графе 2 в той же строке прописывается дата приема на работу — она берется из приказа о найме.
  4. В графе 3 в той же строке делается запись о приеме. Тут правила такие:
    • отдел (структурное подразделение) работодателя указывается в том случае, если это прописано в трудовом договоре;
    • делается запись о должности, профессии с указанием квалификации.

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

  5. В графе 4 в той же строке делается ссылка на приказ, на основании которого принят сотрудник. Запись должна иметь такой вид: «Приказ от (дата) № (номер)». Допустимо в целях экономии места поменять дату и номер местами, но это уже отступление от точного следования правилам.

Печатью запись о приеме на работу не заверяется.

Запись о переводе

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

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

Порядок записи при переводе такой:

  1. В графе 1 указывается номер записи.
  2. В графе 2 — число, месяц и год перевода.
  3. В графе 3 указывается, куда и кем сотрудник переведен.
  4. В графе 4 — ссылка на приказ также, как при приеме на работу.

Временный перевод может стать постоянным — условия этого прописаны в статье 72.2 Трудового кодекса. Это бывает, когда срок временного перевода окончился, но прежняя работа сотруднику не предоставлена, а он этого не потребовал и продолжает работать дальше. В этом случаем считается, что условие о временном характере перевода больше не действует. Перевод считается постоянным с даты перевода (то есть задним числом), а значит, его нужно отразить в трудовой книжке.

Как правильно оформить такой перевод? Опять же, правила не регулируют этот вопрос. Но Роструд в частных разъяснения предлагает такую схему:

  1. С работником заключается дополнительное соглашение, в котором указывается, что ранее осуществленный времени перевод является постоянным.
  2. Издается приказ о том, что временный характер перевода отменяется.
  3. В трудовой книжке делается запись о переводе с указанием в качестве основания обоих приказов: первичного, которым сотрудник был временно переведен, а также приказа из пункта 2.

Остается невыясненным один момент, пояснить который проще на примере. Допустим, работник с 1 марта 2018 года получил временный перевод, а 1 марта 2019 года он был признан постоянным. Какой датой делать запись в трудовой книжке? Тут возможны два варианта:

  1. Сделать запись датой признания перевода постоянным, то есть от 01.03.2019. Тогда в графе 3 нужно будет указать: «01.03.2018 переведен…» далее куда и кем.
  2. Сделать запись датой перевода, то есть от 01.03.2018. В этом случае нужно просто указывать, куда и кем сотрудник переведен.

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

Пример записи при временном переводе, ставшим постоянным

В раздел «Сведения о работе» также вносятся записи:

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

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

Трудовая книжка

Владлена Владлена

Арбитражный суд подтвердил: при отсутствии выплат физическим лицам необходимо заполнять и сдавать нулевой расчёт по страховым взносам.

Подробнее

Владлена Владлена

Минфин выпустил письмо о сдаче персонифицированных сведений на руководителей предприятий, в том числе на тех руководителей, которым не выплачивается зарплата

Подробнее

Владлена Владлена

ФНС и Минфин разрешили работодателям не сдавать персонифицированные сведения о физических лицах одновременно с расчётом по страховым взносам, в том числе за март 2023 года.

Подробнее

Видео: печать листов и книг

Печать рабочих листов и рабочих книг

Обучение Эксель 2013.

Печать рабочих листов и рабочих книг

Печать рабочих листов и рабочих книг

Печать рабочих листов и рабочих книг

  • Печать рабочих листов и рабочих книг
    видео
  • Дополнительные параметры печати
    видео
  • Распечатать определенные страницы
    видео
  • Печать заголовков, линий сетки и формул
    видео

Следующий: Работа с макросами

Печатайте рабочие листы и книги Excel по одному или несколько одновременно. Вы также можете распечатать частичный лист, например таблицу Excel.

Печать одного или нескольких рабочих листов

  1. Выберите рабочие листы, которые вы хотите напечатать.

  2. Щелкните ФАЙЛ > Распечатать . Вы также можете нажать Ctrl+P.

  3. Нажмите кнопку Печать или настройте Настройки перед нажатием кнопки Печать .

Печать одной или нескольких книг

Все файлы рабочей книги, которые вы хотите напечатать, должны находиться в одной папке.

  1. Щелкните ФАЙЛ > Открыть . Вы также можете нажать Ctrl+O.

  2. Удерживая нажатой клавишу Ctrl, щелкните имя каждой книги, которую вы хотите напечатать.

  3. Щелкните правой кнопкой мыши выделенный фрагмент и выберите 9.0031 Распечатать .

Хотите больше?

Заморозить или заблокировать панели

Вставить верхние и нижние колонтитулы

Печать листа или книги

Вы хотите распечатать рабочий лист, над которым работаете.

Щелкните ФАЙЛ и Распечатайте . Вы видите предварительный просмотр того, как будет выглядеть распечатанный рабочий лист.

Здесь Excel сообщает вам, сколько у него страниц, в данном примере 2. Нажмите Распечатать .

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

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

Щелкните стрелку Следующая страница для просмотра разных страниц и щелкните Печать .

Далее, Дополнительные параметры печати .

Учебное пособие по функции ВПР в Excel с примерами формул

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

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

Функция Excel ВПР

Что такое ВПР? Начнем с того, что это функция Excel 🙂 Что она делает? Он ищет указанное вами значение и возвращает соответствующее значение из другого столбца. С технической точки зрения, функция ВПР ищет значение в первом столбце заданного диапазона и возвращает значение в той же строке из другого столбца.

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

Буква «V» означает «вертикальный» и используется для отличия ВПР от функции ГПР, которая ищет значение в строке, а не в столбце (H означает «горизонтальный»).

Функция доступна во всех версиях Excel 365 — Excel 2007.

Совет. В Excel 365 и Excel 2021 вы можете использовать функцию XLOOKUP, которая является более гибкой и мощной преемницей VLOOKUP.

Синтаксис ВПР

Синтаксис функции ВПР следующий:

ВПР(искомое_значение, массив_таблиц, индекс_столбца, [диапазон_просмотра])

Где:

  • Lookup_value (обязательно) — значение для поиска.
    Это может быть значение (число, дата или текст), ссылка на ячейку (ссылка на ячейку, содержащую значение поиска) или значение, возвращаемое какой-либо другой функцией. В отличие от чисел и ссылок на ячейки, текстовые значения всегда должны быть заключены в «двойные кавычки».
  • Table_array (обязательно) — это диапазон ячеек, где искать искомое значение и из которого извлекать совпадения. Функция ВПР всегда выполняет поиск в первом столбце массива таблиц , который может содержать различные текстовые значения, числа, даты и логические значения.
  • Col_index_num (обязательно) — это номер столбца, из которого нужно вернуть значение. Подсчет начинается с крайнего левого столбца в массиве таблиц, который равен 1.
  • Range_lookup (необязательный) — определяет, искать приблизительное или точное совпадение:
    • ИСТИНА или опущено (по умолчанию) — примерное совпадение. Если точное совпадение не найдено, формула ищет наибольшее значение, которое меньше искомого значения. Требуется сортировка столбца поиска в порядке возрастания.
    • ЛОЖЬ — точное совпадение. Формула ищет значение, точно равное искомому значению. Если точное совпадение не найдено, возвращается значение #Н/Д.


Основная формула ВПР

Вот пример формулы Excel ВПР в самой простой форме. Пожалуйста, взгляните на приведенную ниже формулу и попробуйте «перевести» ее на английский язык:

.

=ВПР("лев", A2:B11, 2, ЛОЖЬ)

  • Аргумент 1 st ( lookup_value ) ясно указывает, что формула ищет слово «лев».
  • Аргумент 2 nd ( table_array ) это A2:B11. Имея в виду, что поиск выполняется в крайнем левом столбце, вы можете прочитать приведенную выше формулу чуть дальше: искать «лев» в диапазоне A2:A11. Пока все хорошо, правда?
  • Аргумент 3 rd col_index_num равен 2. Это означает, что мы хотим вернуть соответствующее значение из столбца B, который является вторым в массиве таблиц.
  • Аргумент 4 th range_lookup имеет значение FALSE, что означает, что мы ищем точное совпадение.

Со всеми установленными аргументами у вас не должно возникнуть проблем с прочтением всей формулы: найдите «лев» в A2:A11, найдите точное совпадение и верните значение из столбца B в той же строке.

Для удобства вы можете ввести интересующее вас значение в какую-нибудь ячейку, скажем, E1, заменить «жестко закодированный» текст ссылкой на ячейку и получить формулу для поиска любого значения, которое вы введете в E1:

=ВПР(E1, A2:B11, 2, ЛОЖЬ)

Что-то осталось неясным? Тогда попробуйте посмотреть на это так:

Как сделать ВПР в Excel

При использовании формул ВПР в реальных рабочих листах основное практическое правило заключается в следующем: заблокируйте массив таблицы с абсолютными ссылками на ячейки (например, $A$2:$C$11), чтобы предотвратить его изменение при копировании формулы в другие ячейки. .

Искомое значение в большинстве случаев должно быть относительной ссылкой (например, E2), или вы можете заблокировать только координату столбца ($E2). Когда формула копируется вниз по столбцу, ссылка автоматически корректируется для каждой строки.

Чтобы увидеть, как это работает на практике, рассмотрим следующий пример. В нашу примерную таблицу мы добавили еще один столбец, ранжирующий животных по скорости (столбец A), и хотим найти 1  , 5   и 10   самого быстрого спринтера в мире. Для этого введите ранги поиска в некоторые ячейки (E2:E4 на скриншоте ниже) и используйте следующие формулы:

Чтобы получить имена животных из столбца B:

=ВПР($E2, $A$2:$C$11, 2, ЛОЖЬ)

Чтобы извлечь скорость из столбца C:

=ВПР($E2, $A$2:$C$11, 3, ЛОЖЬ)

Введите приведенные выше формулы в ячейки F2 и G2, выберите эти ячейки и перетащите формулы в следующие строки:

Если вы исследуете формулу в нижней строке, вы заметите, что ссылка на искомое значение скорректирована для этой конкретной строки, в то время как массив таблицы не изменился:

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

Excel VLOOKUP — 5 вещей, которые нужно помнить!

  1. Функция ВПР не может смотреть слева . Он всегда ищет 90 129 в крайнем левом столбце 90 130 массива таблиц и возвращает значение из столбца справа. Если вам нужно извлечь значения слева, используйте комбинацию INDEX MATCH (или INDEX XMATCH в Excel 365), которая может не заботиться о расположении столбцов поиска и возврата.
  2. Функция ВПР нечувствительна к регистру , что означает, что прописные и строчные символы обрабатываются как эквивалентные. Чтобы различать регистр букв, используйте формулы ВПР с учетом регистра.
  3. Помните о важности последнего параметра. Используйте TRUE для приблизительного совпадения и FALSE для точного совпадения. Подробную информацию см. в разделе ВПР ИСТИНА и ЛОЖЬ.
  4. При поиске приблизительного совпадения убедитесь, что данные в столбце подстановки отсортированы по возрастанию.
  5. Если искомое значение не найдено, возвращается ошибка #Н/Д. Сведения о других ошибках см. в статье Почему функция ВПР Excel не работает.

Примеры ВПР Excel

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

Как выполнить визуальный поиск с другого листа в Excel

На практике функция ВПР Excel редко используется с данными на одном листе. Чаще всего вам придется извлекать соответствующие данные из другого рабочего листа.

Для ВПР с другого листа Excel поместите имя рабочего листа, а затем восклицательный знак в table_array Аргумент перед ссылкой на диапазон. Например, для поиска в диапазоне A2:B10 на Листе2 используйте следующую формулу:

.

=ВПР("Товар1", Лист2!A2:B10, 2)

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

Например, так можно найти значение A2 в диапазоне A2:A9.на листе Prices и вернуть соответствующее значение из столбца C:

=ВПР(A2, Цены!$A$2:$C$9, 3, ЛОЖЬ)

Примечания:

  • Если имя электронной таблицы содержит пробелов или неалфавитных символов , оно должно быть заключено в одинарные кавычки, например ‘Прайс-лист’!$A$2:$C$9.
  • Если вы используете формулу ВПР для нескольких ячеек, не забудьте заблокировать table_array знаком $, например $A$2:$C$9.

Как выполнить визуальный поиск из другой книги в Excel

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

Например, вот формула для поиска значения A2 на листе с именем Prices в рабочей книге Price_List.xlsx :

=ВПР(A2, [Price_List.xlsx]Цены!$A$2:$C$9, 3, ЛОЖЬ)

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

=ВПР(A2, '[Прайс-лист. xlsx]Цены'!$A$2:$C$9, 3, ЛОЖЬ)

Самый простой способ создать формулу ВПР, которая ссылается на другую книгу:

  1. Откройте оба файла.
  2. Начните вводить формулу, переключитесь на другую книгу и выберите массив таблицы с помощью мыши.
  3. Введите оставшиеся аргументы и нажмите клавишу Enter, чтобы завершить формулу.

Результат будет примерно таким, как на скриншоте ниже:

Как только вы закроете файл с таблицей поиска, формула ВПР продолжит работу, но теперь она будет отображать полный путь к закрытой книге:

Дополнительные сведения см. в разделе Как ссылаться на другой лист или книгу Excel.

Как выполнять визуальный поиск из именованного диапазона на другом листе

Если вы планируете использовать один и тот же диапазон поиска во многих формулах, вы можете создать для него именованный диапазон и ввести имя непосредственно в table_array аргумент.

Чтобы создать именованный диапазон, просто выберите ячейки и введите нужное имя в поле Имя слева от строки формул. Подробные инструкции см. в разделе Как назвать диапазон в Excel.

В этом примере мы дали имя Prices_2020 ячейкам данных (A2:C9) в таблице поиска и получили следующую компактную формулу:

=ВПР(A2, Цены_2020, 3, ЛОЖЬ)

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

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

=ВПР(A2, 'Прайс-лист.xlsx'!Цены_2020, 3, ЛОЖЬ)

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

Если вы преобразовали диапазон поиска в полнофункциональную таблицу Excel, вы можете выполнить ВПР на основе имени таблицы, например Price_table в следующей формуле:

=ВПР(A2, Таблица цен, 3, ЛОЖЬ)

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

Использование подстановочных знаков в формуле ВПР

Как и многие другие формулы, функция ВПР Excel поддерживает следующие подстановочные знаки:

  • Знак вопроса (?) соответствует любому одиночному символу.
  • Звездочка (*) для соответствия любой последовательности символов.

Подстановочные знаки оказываются очень полезными во многих ситуациях:

  • Когда вы не помните точный текст, который вы ищете.
  • Когда вы ищете текстовую строку, которая является частью содержимого ячейки.
  • Если столбец подстановки содержит начальные или конечные пробелы. В этом случае вы можете поломать голову, пытаясь понять, почему обычная формула не работает.

Пример 1. Поиск текста, начинающегося или заканчивающегося определенными символами

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

Чтобы вернуть фамилию из столбца A, используйте следующую формулу подстановочного знака Vlookup:

=ВПР("подтверждение*", $A$2:$B$10, 1, ЛОЖЬ)

Чтобы получить лицензионный ключ из столбца B, используйте этот (разница только в порядковом номере столбца):

=ВПР("подтверждение*", $A$2:$B$10, 2, ЛОЖЬ)

Вы также можете ввести известную часть имени в какую-нибудь ячейку, скажем, E1, и объединить подстановочный знак со ссылкой на ячейку:

=ВПР(E1&"*", $A$2:$B$10, 1, ЛОЖЬ)

На снимке экрана ниже показаны результаты:

Ниже приведены еще несколько формул ВПР с подстановочными знаками.

Найти фамилию, оканчивающуюся на «сын»:

=ВПР("*сын", $A$2:$B$10, 1, ЛОЖЬ)

Получить имя, начинающееся с «joh» и заканчивающееся на «son»:

=ВПР("джох*сын", $A$2:$B$10, 1, ЛОЖЬ)

Получить 5-значную фамилию:

=ВПР("?????", $A$2:$B$10, 1, ЛОЖЬ)

Пример 2.

Подстановочный знак ВПР на основе значения ячейки

Из предыдущего примера вы уже знаете, что можно объединить амперсанд (&) и ссылку на ячейку для создания строки поиска. Чтобы найти значение, которое содержит заданный символ (символы) в любой позиции, поставьте амперсанд до и после ссылки на ячейку.

Допустим, вы хотите получить имя, соответствующее определенному лицензионному ключу, но не знаете всего ключа, только несколько символов. С ключами в столбце A, именами в столбце B и частью целевого ключа в E1 вы можете выполнить Vlookup с подстановочными знаками следующим образом:0003

Извлечь ключ:

=ВПР("*"&E1&"*", $A$2:$B$10, 1, ЛОЖЬ)

Извлечь имя:

=ВПР("*"&E1&"*", $A$2:$B$10, 2, ЛОЖЬ)

Примечания:

  • Чтобы формула ВПР работала правильно, используйте точное совпадение (ЛОЖЬ — последний аргумент).
  • Если найдено более одного совпадения, возвращается первое .

ВПР ИСТИНА против ЛОЖЬ

А теперь пора более подробно рассмотреть последний аргумент функции ВПР Excel. Хотя параметр range_lookup необязателен, он очень важен. В зависимости от того, выберете ли вы ИСТИНА или ЛОЖЬ, ваша формула может дать разные результаты.

ВПР Excel точное совпадение (ЛОЖЬ)

Если для range_lookup задано значение FALSE, формула Vlookup ищет значение, точно равное искомому значению. Если найдено два или более совпадения, возвращается 1-е. Если точное совпадение не найдено, возникает ошибка #Н/Д.

Приблизительное соответствие ВПР Excel (ИСТИНА)

Если для параметра range_lookup установлено значение TRUE или оно опущено (по умолчанию), формула ищет ближайшее соответствие. Точнее, он сначала ищет точное совпадение, а если точное совпадение не найдено, ищет следующее наибольшее значение, которое меньше искомого значения.

Приблизительное соответствие Vlookup работает со следующими оговорками:

  • Столбец подстановки должен быть отсортирован в порядке возрастания , от меньшего к большему, в противном случае правильное значение может быть не найдено.
  • Если значение поиска меньше наименьшего значения в массиве поиска, возвращается ошибка #Н/Д.

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

Пример 1. Как сделать точное совпадение Vlookup

Чтобы найти точное совпадение, просто введите FALSE в последний аргумент.

Для этого примера возьмем таблицу скорости животных, поменяем столбцы местами и попробуем найти животных, которые могут бегать со скоростью 80, 50 и 30 миль в час. Со значениями поиска в D2, D3 и D4 введите приведенную ниже формулу в E2, а затем скопируйте ее еще в две ячейки:

= ВПР(D2, $A$2:$B$12, 2, ЛОЖЬ)

Как видите, формула возвращает «Лев» в E3, потому что они выполняются ровно 50 раз в час. Для двух других значений поиска точное совпадение не найдено, и появляются ошибки #Н/Д.

Пример 2.

Как выполнить Vlookup для приблизительного соответствия

Чтобы найти приблизительное совпадение, вам нужно сделать две важные вещи:

  • Отсортировать первый столбец table_array от меньшего к большему.
  • Используйте TRUE для аргумента range_lookup или опустите его.

Сортировка столбца подстановки очень важна, поскольку функция ВПР прекращает поиск, как только находит близкое совпадение, меньшее, чем значение подстановки. Если данные не отсортированы должным образом, вы можете получить действительно странные результаты или кучу ошибок #N/A.

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

=ВПР(D2, $A$2:$B$12, 2, ИСТИНА)

И возвращает следующие результаты:

  • Для искомого значения «80» возвращается «Cheetah», поскольку его скорость (70) является ближайшим соответствием, которое меньше искомого значения.
  • Для искомого значения «50» возвращается точное совпадение (лев).
  • Для искомого значения «30» возвращается ошибка #Н/Д, поскольку искомое значение меньше наименьшего значения в столбце поиска.


Специальные инструменты для Vlookup в Excel

Несомненно, ВПР — одна из самых мощных и полезных функций Excel, но она также и одна из самых запутанных. Чтобы сделать кривую обучения менее крутой и сделать ее более приятной, мы включили несколько инструментов, позволяющих сэкономить время, в наш Ultimate Suite for Excel.

Мастер ВПР — простой способ написания сложных формул

Интерактивный мастер ВПР проведет вас через параметры конфигурации, чтобы создать идеальную формулу для заданных вами критериев. В зависимости от вашей структуры данных он будет использовать стандартную функцию ВПР или формулу ПОИСКПОЗ ИНДЕКС, которая может извлекать значения слева.

Чтобы получить индивидуальную формулу, вам нужно сделать следующее:

  1. Запустите мастер ВПР.
  1. Выберите основную таблицу и таблицу поиска.
  2. Укажите следующие столбцы (во многих случаях они выбираются автоматически):
    • Ключевой столбец — столбец в вашей основной таблице, содержащий значения для поиска.
    • Столбец поиска — столбец для поиска.
    • Возврат столбца — столбец, из которого нужно получить значения.
  3. Нажмите кнопку Вставить .

В следующих примерах показан мастер в действии.

Стандартный ВПР

Если столбец поиска ( Животное ) является крайним левым столбцом в таблице поиска, вставляется обычная формула ВПР для точного совпадения:

ВПР слева

Когда столбец поиска ( Животное ) находится справа от столбца возврата ( Скорость ), мастер вставляет формулу ПОИСКПОЗ ИНДЕКС в ВПР справа налево:

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

Объединить две таблицы — альтернатива ВПР Excel без формул

Если ваши файлы Excel очень большие и сложные, крайний срок проекта приближается, и вы ищете кого-то, кто может протянуть вам руку помощи, попробуйте мастер объединения таблиц.

Этот инструмент является нашей визуальной и простой альтернативой функции ВПР Excel, которая работает следующим образом:

  1. Выберите основной стол.
  2. Выберите таблицу поиска.
  3. Выберите один или несколько общих столбцов в качестве уникальных идентификаторов.
  4. Укажите, какие столбцы необходимо обновить.
  5. При необходимости выберите столбцы для добавления.
  6. Дайте мастеру слияния таблиц несколько секунд на обработку… и наслаждайтесь результатами 🙂


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

Об авторе

alexxlab administrator

Оставить ответ

© 2019 ICQ Information Center