Образец заполнение трудовой книжки 2020: инструкция и правила, пример — Бухонлайн

Содержание

Трудовая книжка работника: бланки и образцы заполнения

Абакан

Азов

Алапаевск

Алейск

Алушта

Альметьевск

Ангарск

Апатиты

Арзамас

Арсеньев

Артем

Архангельск

Асбест

Астрахань

Байконур

Балаково

Балашиха

Барнаул

Белгород

Белебей

Белорецк

Березовский

Благовещенск

Боровичи

Борское

Братск

Брянск

Валуйки

Великий Новгород

Великий Устюг

Верхняя Пышма

Владивосток

Владикавказ

Владимир

Волгоград

Волгодонск

Волжский

Вологда

Воронеж

Воткинск

Геленджик

Горно-Алтайск

Дербент

Димитровград

Динская

Домодедово

Дубна

Ейск

Екатеринбург

Елизово

Ессентуки

Железногорск

Зеленокумск

Иваново

Ижевск

Иркутск

Йошкар-Ола

Кабицыно

Казань

Калининград

Калуга

Кемерово

Кинешма

Киров

Кирово-Чепецк

Кисловодск

Клин

Ковров

Козьмодемьянск

Комсомольск-на-Амуре

Королёв

Коряжма

Костомукша

Кострома

Красногорск

Краснодар

Краснознаменск

Краснокамск

Краснокумское

Красноярск

Криулино

Кропоткин

Кстово

Кудрово

Курган

Курск

Кызыл

Лесной городок

Лесосибирск

Липецк

Лыткарино

Магадан

Магнитогорск

Майкоп

Малгобек

Махачкала

Миасс

Мирный

Можайск

Мосальск

Москва

Мостец

Мочище

Муравленко

Мурманск

Муром

Мытищи

Мытищи

Набережные Челны

Нарьян-Мар

Находка

Невинномысск

Невьянск

Нижневартовск

Нижнекамск

Нижний Новгород

Нижний Тагил

Новокузнецк

Новокуйбышевск

Новороссийск

Новосибирск

Новоуральск

Новохаритоново

Новочеркасск

Новый Уренгой

Ноябрьск

Обнинск

Одинцово

Озерск

Октябрьский

Омск

Орёл

Оренбург

Павловский Посад

Пенза

Первоуральск

Переславль-Залесский

Пермь

Петрозаводск

Петропавловск-Камчатский

Подольск

Покров

Прокопьевск

Протвино

Псков

Пушкино

Пыть-Ях

Пятигорск

Радужный

Раменское

Рославль

Ростов-на-Дону

Рыбинск

Рязань

Салехард

Самара

Санкт-Петербург

Саранск

Саратов

Саяногорск

Саянск

Севастополь

Сергиев Посад

Серпухов

Симферополь

Смоленск

Снежинск

Солнечногорск

Сочи

Ставрополь

Старый Оскол

Судоверфь

Сургут

Сызрань

Сыктывкар

Тамбов

Тверь

Тимашевск

Тихвин

Тобольск

Тольятти

Томск

Торжок

Туапсе

Тула

Тюмень

Узюково

Улан-Удэ

Ульяновск

Усинск

Усолье-Сибирское

Уссурийск

Усть-Илимск

Уфа

Ухта

Фрязино

Хабаровск

Ханты-Мансийск

Химки

Чайковский

Чебоксары

Челябинск

Черемшан

Череповец

Чехов

Чита

Чунский

Чусовой

Шали

Шаховская

Шмидтово

Электросталь

Энгельс

Юг

Южно-Сахалинск

Якутск

Ялта

Ялуторовск

Ярославль

Ведение сведений о трудовой деятельности в электронном виде («электронная трудовая книжка»)

Общие сведения

В соответствии с федеральными законами от 16 декабря 2019 г. № 439-ФЗ «О внесении изменений в Трудовой кодекс Российской Федерации в части формирования сведений о трудовой деятельности в электронном виде» и № 436-ФЗ «О внесении изменений в Федеральный закон «Об индивидуальном (персонифицированном) учете в системе обязательного пенсионного страхования» работодатели с 15 февраля 2020 г. представляют сведения о трудовой деятельности работников в Пенсионный фонда Российской Федерации (далее – ПФР) в
электронном виде.

Формирование сведений о трудовой деятельности лиц, впервые поступающих на работу с 1 января 2021 г., осуществляется только в электронном виде. Если в 2020 г. работник не подал никакого заявления, то на него ведется трудовая книжка. Право выбора способа ведения трудовой книжки сохраняется для тех, кто в 2020 г. был безработным или же не исполнял трудовые функции в связи с временной нетрудоспособностью, нахождением в отпуске, отстранением от работы. Для этого необходимо подать работодателю одно из письменных заявлений по основному месту работы, в том числе при трудоустройстве.
Перечень сведений в «электронной трудовой книжке» аналогичен практически всем сведениям в трудовой книжке (информация о работнике; даты приема, увольнения, перевода; место работы; вид мероприятия; должность, профессия, специальность, квалификация, структурное подразделение; вид поручаемой работы; основание кадрового мероприятия; причины прекращения трудового договора). Вопросы-ответы о переходе на электронную трудовую книжку, перечне сведений, содержащихся в электронной трудовой книжке, и др. содержатся на официальном сайте ПФР в разделе «Электронная трудовая книжка».
В целях пополнения имеющейся у Пенсионного фонда Российской Федерации информации сведениями о периодах работы до 1 января 2020 г. принят Федеральный закон от 24 февраля 2021 г. № 30-ФЗ «О внесении изменений в статьи 12 и 16 Федерального закона «Об индивидуальном (персонифицированном) учете в системе обязательного пенсионного страхования». В соответствии с нормами указанного закона граждане вправе обратиться в органы Пенсионного фонда Российской Федерации для включения сведений о трудовой деятельности в свой индивидуальный лицевой счет за периоды работы до 1 января 2020 года, записи о которых содержатся в трудовой книжке.

Нормативные правовые акты по ведению сведений о трудовой деятельности в электронном виде («электронная трудовая книжка»)

  1. Федеральный закон от 16 декабря 2019 г. № 439-ФЗ «О внесении изменений в Трудовой кодекс Российской Федерации в части формирования сведений о трудовой деятельности в электронном виде»
  2. Федеральный закон от 16 декабря 2019 г. № 436-ФЗ «О внесении изменений в Федеральный закон «Об индивидуальном (персонифицированном) учете в системе обязательного пенсионного страхования»
  3. Федеральный закон от 24 апреля 2020 г. № 136-ФЗ «О внесении изменений в статьи 2 и 11 Федерального закона «Об индивидуальном (персонифицированном) учете в системе обязательного пенсионного страхования»
  4. Указ Президента Российской Федерации от 6 октября 2020 г. № 616 «О внесении изменений в некоторые акты Президента Российской Федерации»
  5. Постановление Правительства Российской Федерации от 10 июля 2020 г. № 1017 «О внесении изменений в некоторые акты Правительства Российской Федерации в связи с принятием Федерального закона «О внесении изменений в Трудовой кодекс Российской Федерации в части формирования сведений о трудовой деятельности в электронном виде»
  6. Постановление Правительства Российской Федерации от 19 июня 2020 г. № 887 «Об особенностях правового регулирования трудовых отношений и иных непосредственно связанных с ними отношений в 2020 году»
  7. Приказ Минтруда России от 20 января 2020 г. № 23н «Об утверждении формы сведений о трудовой деятельности, предоставляемой работнику работодателем, формы предоставления сведений о трудовой деятельности из информационных ресурсов Пенсионного фонда Российской Федерации и порядка их заполнения» (вместе с «Порядком заполнения формы «Сведения о трудовой деятельности, предоставляемые работнику работодателем (СТД-Р)» и формы «Сведения о трудовой деятельности, предоставляемые из информационных ресурсовПенсионного фонда Российской Федерации (СТД-ПФР)»)
  8. Приказ Минтруда России от 24 августа 2020 г. № 533н «Об утверждении особенностей представления сведений о трудовой деятельности государственными органами в отношении отдельных категорий зарегистрированных лиц»
  9. Постановление Правления Пенсионного фонда Российской Федерации от 25 декабря 2019 г. № 730п «Об утверждении формы и формата сведений о трудовой деятельности зарегистрированного лица, а также порядка заполнения форм указанных сведений» (вместе с «Порядком заполнения формы «Сведения о трудовой деятельности зарегистрированного лица (СЗВ-ТД)», «Форматомсведений для формы «Сведения о трудовой деятельности (СЗВ-ТД) зарегистрированного лица» в электронном виде»)

Дубликат трудовой книжки: образец заполнения 2020 года

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

Как получить дубликат трудовой книжки при утере

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

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

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

Соискатель вправе восстановить утерянный документ. Для этого может потребоваться:

  1. Изначально необходимо заявление о выдаче дубликата трудовой книжки. С ним следует обратиться на прежнее место занятости.
  2. Предыдущий работодатель формирует всю доступную информацию об общем стаже, полученных поощрениях, периоде работы и вносит соответствующие записи не позднее 15-ти рабочих дней.
  3. Если трудовая деятельность имела ранее место у нескольких нанимателей, то образец заполнения дубликата трудовой книжки при утере должен соответствовать реальным фактам. Данные вносят на основании справок и иных документов. Иногда можно воспользоваться даже показаниями свидетелей о наличии стажа. А при факте ликвидации предприятия требуемую информацию обычно ищут в местных архивах.

Также см. «Можно ли иметь две трудовые книжки одновременно: последствия и ответственность».

В каких случаях выдается дубликат трудовой книжки

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

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

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

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

Также см. «Как восстановить трудовую книжку, если вы ее потеряли».

Как сделать дубликат трудовой книжки

Вслед за обращением бывшего сотрудника за выдачей дубликата утерянного документа закон дает руководству 15 рабочих дней для восстановления книжки. При этом изначально потребуется получить письменное прошение сотрудника. Единой обязательной формы для такого обращения нет. Поэтому совсем не трудно составить заявление на выдачу дубликата трудовой книжки. Образец может быть выполнен в произвольной форме.

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

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

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

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

Установление факта работы допустимо на основе показаний не менее 2-х свидетелей.

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

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

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

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

1Указывают общий стаж работы, который есть у сотрудника до поступления к работодателю, ответственному за выдачу дубликата.
2В первой графе проставляют порядковый номер записи. Как правило, это – № 1.
3Графа 2 содержит сведения о дате события.
4В графу 3 заносят информацию о занимаемой должности. Данные о работодателях в дубликате трудовой книжки приводят после указания даты (в отличие от оригинала). Затем действует общий порядок: наименование места работы предшествует данным графы 2.
5Перечисляют документы, сведения которых позволили внести запись о работе, стаже.

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

Также см. «Запись в трудовой книжке об увольнении в 2020 году».

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

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

Также см. «Правила заполнения трудовых книжек в 2020 году».

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

На нашем сайте бланк этой книги можно скачать здесь.

Теперь вы знаете, как оформить дубликат трудовой книжки по образцу 2020-го года. Процедура особой сложности не представляет. Достаточно иметь заявление сотрудника и внести требуемые сведения в новый бланк с пометкой «дубликат».

Также см. «Дубликат трудовой книжки: как с ним работать».

Бланки заявлений и иных документов

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

Бланк заявления о назначении и выплате ежемесячного пособия по уходу за ребенком до 1,5 лет и ежемесячной компенсационной выплаты по уходу за ребенком до трех лет

~47 кБ, *.doc (Размещен 25.08.2016)

Образец заполнения заявления о назначении и выплате ежемесячного пособия по уходу за ребенком

Образец заполнения заявления о назначении и выплате ежемесячного пособия по уходу за ребенком до 1,5 лет и ежемесячной компенсационной выплаты по уходу за ребенком до трех лет

~422 кБ, *. pdf (Размещен 25.08.2016)

Заявление о назначении единовременного пособия при постановке на учет в медицинское учреждение в ранние сроки беременности

Бланк заявления

~19 кБ, *.docx (Размещен 25.08.2016)

Образец заполнения заявления о назначении единовременного пособия

Образец заполнения заявления о назначении единовременного пособия при постановке на учет в медицинское учреждение в ранние сроки беременности

~270 кБ, *.pdf (Размещен 25.08.2016)

Заявление о назначении единовременного пособия при рождении ребенка

Бланк заявления

~48 кБ, *. doc (Размещен 25.08.2016)

Образец заполнения заявления о назначении единовременного пособия при рождении ребенка

~397 кБ, *.pdf (Размещен 25.08.2016)

Заявление о выплате пособия на погребение

~23 кБ, *.docx (Размещен 02.09.2016)

Образец заполнения заявления о выплате пособия на погребение

~217 кБ, *.pdf (Размещен 02.09.2016)

В России вводятся трудовые книжки нового образца

  © Пресс-служба Государственной Думы

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

Старые документы не нужно будет переоформлять

Ранее Минтруд уже скорректировал форму бланка трудовой книжки, а также обновил инструкцию по её ведению и хранению. Из неё, например, исключили некоторые устаревшие положения о способах заполнения бланков и учёте книжек работодателем. Для сведений о работе теперь отвели 14 страниц вместо 10, а для информации о награждениях — 7 страниц вместо 10. Эта инструкция вступит в действие с 1 сентября 2021 года, пояснила «Парламентской газете» директор Института профессионального кадровика, юрист Валентина Митрофанова.

С 2023 года гражданам будут выдавать бумажные трудовые книжки нового образца. Согласно постановлению, опубликованному на сайте Правительства 30 июля, порядок их изготовления определит Минфин. Он же решит вопросы, связанные с обеспечением работодателей новыми документами. В кабмине подчеркнули, что работникам не придётся переоформлять свои старые трудовые книжки — они смогут без ограничений пользоваться ими. «То же касается бланков и вкладышей старого образца, которые хранятся у работодателей, — они также будут действительны», — отметили в пресс-службе Правительства.

«Сейчас в России идёт обновление всех нормативных актов, которые вступили в силу до 1 января 2020 года, в том числе и в трудовом законодательстве, — сказала Валентина Митрофанова. — Соответственно, обновляется и бланк трудовой книжки».

Сколько россиян выбрали электронный формат

С 1 января 2020 года работодатели вносят в Пенсионный фонд сведения о трудовой деятельности работников в электронном виде. Сотрудникам дали право выбора между бумажной и электронной книжкой. Тем, кто выбрал цифровой формат, начальство выдало бумажные документы на руки. Их нужно хранить, так как они подтверждают весь предыдущий стаж, разъяснил Пенсионный фонд на своём сайте в январе 2021 года.  Людям, которые впервые устраиваются на работу, трудовую оформят только в электронном виде. Информацию о своём стаже они могут найти в личном кабинете на сайте ПФР.  

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

• Чем удобна электронная трудовая книжка • В электронные трудовые книжки будут вносить больше подробностей • Какие документы перейдут в электронный вид

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

«С 2020 года вся трудовая деятельность ведётся в электронном виде, независимо от того, выбрал ли человек электронную книжку или решил сохранить бумажную», — отметил в комментарии «Парламентской газете» глава Комитета Госдумы по труду и соцполитике, замруководителя фракции ЛДПР Ярослав Нилов.  Все эти данные находятся в системе персонифицированного учёта, которую ведёт Пенсионный фонд.

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

«Когда разваливался Советский Союз, «сгорали» целые предприятия, бухгалтерия, отделы кадров, уничтожали трудовые книжки, и подтвердить стаж теперь невозможно, а это влияет на размер пенсии», — пояснил парламентарий. Он сам сохранил бумажный вариант документа. «При этом я понимаю, что цифровой формат — это удобно и современно», — сказал Ярослав Нилов.

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

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

Отличие выписки из трудовой книжки от ее копии

В делопроизводстве применяется ГОСТ Р 7. 0.8-2013 «СИБИД. Делопроизводство и архивное дело. Термины и определения», введенный в действие 1 марта 2014 года. В нем раскрываются такие термины, как копия документа и выписка из него:

  • копия – это экземпляр документа, воспроизводящий информацию подлинника.
  • выписка – это копия части документа, заверенная в установленном порядке.

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

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

Форма выписки из трудовой книжки — образец

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

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

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

Содержание выписки

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

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

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

Еще больше материалов по теме в рубрике: «Трудовая книжка». 

Переход на электронные трудовые книжки

По общему правилу те работники, которых работодатель уведомил по 30 июня 2020 года об изменениях в трудовом законодательстве, обязаны по 31 декабря 2020 года направить работодателю письменное заявление о выборе продолжения ведения им бумажных трудовых книжек или об отказе от ведения трудовых книжек и предоставлении им сведений о трудовой деятельности в соответствии со статьей 66.1 ТК РФ.

Работодатель самостоятельно ведет учет полученных заявлений от работников (учет может осуществляться в информационных системах работодателя). Главное — фиксировать дату подачи заявления и его содержание, так как эта информация в дальнейшем будет передаваться в Пенсионный фонд РФ в составе сведений о трудовой деятельности по форме СЗВ-ТД, утвержденной постановлением Правления Пенсионного фонда РФ от 25. 12.2019 № 730П.

Работодатель может приложить форму заявления к уведомлению. Это могут быть два заявления (одно с просьбой продолжать вести трудовую книжку, а второе об отказе ведения трудовой книжки и предоставлении работнику сведений о трудовой деятельности в соответствии со статьей 66.1 Трудового кодекса РФ) или одно заявление, в котором работника просят отметить, подчеркнуть, выделить одно из действий.

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

ЗАЯВЛЕНИЕ

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

Должность, личная подпись, расшифровка

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

ЗАЯВЛЕНИЕ

В связи с изменениями в трудовом законодательстве, связанными с формированием сведений о трудовой деятельности в электронном виде, прошу предоставлять мне сведения о трудовой деятельности в электронном виде в соответствии со статьей 66. 1 Трудового кодекса Российской Федерации.

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

Должность, личная подпись, расшифровка

Создать новую рабочую книгу

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

Создать книгу

  1. Откройте Excel.

  2. Выберите Пустая книга или нажмите Ctrl+N.

  3. Начните печатать.

Создание книги на основе шаблона

  1. Выберите Файл > Новый .

  2. Дважды щелкните шаблон.

  3. Открыть новую пустую книгу
    1. Перейдите на вкладку Файл .

    2. Щелкните Новый .

    3. В разделе Доступные шаблоны дважды щелкните Пустая рабочая книга .

      Сочетание клавиш  Чтобы быстро создать новую пустую книгу, вы также можете нажать CTRL+N.

    Создание новой книги на основе существующей книги

    1. Перейдите на вкладку Файл .

    2. Щелкните Новый .

    3. В разделе Шаблоны щелкните Создать из существующих .

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

    5. Щелкните книгу, а затем щелкните Создать новый .

    Создать новую книгу на основе шаблона

    1. Перейдите на вкладку Файл .

    2. Щелкните Новый .

    3. Выполните одно из следующих действий:

      • Чтобы использовать недавно использовавшийся шаблон, щелкните Последние шаблоны , а затем дважды щелкните нужный шаблон.

      • Чтобы использовать собственный шаблон, на вкладке Мои шаблоны , а затем на вкладке Персональные шаблоны диалогового окна Создать дважды щелкните нужный шаблон.

        Примечание. На вкладке Персональные шаблоны перечислены созданные вами шаблоны. Если вы не видите шаблон, который хотите использовать, убедитесь, что он находится в правильной папке.Пользовательские шаблоны обычно хранятся в папке Templates , которая обычно находится в папке C:\Users\user_name\AppData\Local\Microsoft\Templates в Windows Vista и C:\Documents and Settings\user_name\Application Data\Microsoft\Templates в Windows Vista. Майкрософт Windows XP.

    Совет: Чтобы получить больше шаблонов книг, вы можете загрузить их из Microsoft Office.ком. В Доступные шаблоны в разделе Шаблоны Office.com щелкните определенную категорию шаблонов, а затем дважды щелкните шаблон, который нужно загрузить.

    Нужна дополнительная помощь?

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

    См. также

    Вставка или удаление рабочего листа

    Создание форм, которые пользователи заполняют или печатают в Excel

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

    Шаг 1. Показать вкладку «Разработчик»

    1. В меню Excel щелкните Настройки .

    2. В разделе Authoring щелкните View .

    3. Шаг 2. Добавление и форматирование элементов управления содержимым
      1. На вкладке Developer щелкните элемент управления, который вы хотите добавить.

      2. Щелкните на рабочем листе в том месте, где вы хотите вставить элемент управления.

      3. Чтобы задать определенные свойства для элемента управления, удерживайте нажатой клавишу CONTROL и щелкните элемент управления, а затем щелкните Формат элемента управления .

      4. В поле Управление форматом задайте нужные свойства, такие как шрифт, выравнивание и цвет.

      5. Повторите шаги с 1 по 4 для каждого элемента управления, который вы хотите добавить.

      Шаг 3. Защитите лист, содержащий форму

      1. В меню Инструменты выберите пункт Защита , а затем щелкните Защитить лист .

      2. Выберите нужные параметры защиты.

      3. Шаг 4. Проверка формы (необязательно)

        Если вы хотите, вы можете протестировать форму, прежде чем распространять ее.

        1. Защитите форму, как описано в шаге 3.

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

        Как создать пользовательскую форму Excel для ввода данных

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

        См. также пользовательскую форму Excel с полями со списком.

        Введение в пользовательские формы

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

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

        Видео: Создание пользовательской формы Excel

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

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

        Заполненная пользовательская форма — как это работает

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

        Создание пользовательской формы — часть 1

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

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

        Создание пользовательской формы — часть 2

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

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

        Создание пользовательской формы — часть 3

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

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

        Настройка рабочего листа

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

        1. Открыть новую книгу
        2. Дважды щелкните вкладку листа для Sheet1
        3. Тип: PartsData
        4. Нажмите клавишу Enter
        5. В ячейках A1:D1 введите заголовки для базы данных запасов деталей, как показано справа.
        6. Выберите файл | Сохраните и сохраните книгу. В этом примере файл был назван PartsLocDB.xls .
          ПРИМЕЧАНИЕ. В Excel 2007 и более поздних версиях сохраните файл как файл с поддержкой макросов. тип файла.

        Создание пользовательской формы

        Пользовательские формы

        создаются в редакторе Visual Basic.

        1. Чтобы открыть редактор Visual Basic, удерживайте клавишу Alt и нажмите клавишу F11. ключ
        2. Выберите Вид | Project Explorer, чтобы увидеть список проектов.(Как правило, это отображается в левой части окна VBE.)
        3. В обозревателе проектов выберите проект PartLocDB.
        4. В строке меню выберите Вставка | Пользовательская форма
        5. Появится пустая пользовательская форма, и должна открыться панель инструментов. (Если панель инструментов не отображается, выберите «Просмотр» | ящик с инструментами)  

        Имя пользовательской формы

        1. Чтобы открыть окно свойств, нажмите клавишу F4
        2. В окне «Свойства» дважды щелкните имя — UserForm1 , в правом верхнем углу окна.
        3. Введите:   frmPartLoc
          и нажмите клавишу Enter
        4. Имя формы изменится в Project Explorer, но форма по-прежнему показывает UserForm1 в строке заголовка.
        5. В окне «Свойства» дважды щелкните свойство «Заголовок» — UserForm1 .
        6. Введите:   Инвентаризация деталей
          и нажмите клавишу Enter
        7. В строке заголовка появится новый заголовок.

        Добавить текстовое поле в пользовательскую форму

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

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

        Добавление метки к пользовательской форме

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

        1. На панели инструментов нажмите кнопку «Ярлык».
        2. В пользовательской форме щелкните слева от текстового поля, чтобы добавить стандартный этикетка по размеру.
        3. Выбрав новую метку, дважды щелкните свойство Caption. в окне свойств.
        4. Введите:   Часть
          и нажмите клавишу Enter
        5. .
        6. При необходимости можно изменить размер метки, чтобы она не закрывала текстовое поле — наведите курсор на маркер на его правой границе и перетащите влево.
        7. Нажмите на пустую часть пользовательской формы, чтобы выбрать пользовательскую форму и для отображения панели инструментов.

        Добавить оставшиеся текстовые поля и метки

        Повторите вышеуказанные шаги, чтобы добавить:

        • текстовое поле с именем txtLoc с меткой Location
        • текстовое поле с именем txtDate с меткой Date
        • текстовое поле с именем txtQty с меткой Количество  

        Если текстовые поля не выровнены, вы можете выровнять их:

        1. Нажмите на первое текстовое поле
        2. Удерживая нажатой клавишу Ctrl, щелкните оставшиеся текстовые поля
        3. Выберите Формат | Выровнять | Левые
        4. Нажмите на пустую часть пользовательской формы, чтобы выбрать пользовательскую форму и для отображения панели инструментов.

        Добавить кнопки в форму пользователя

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

        1. На панели инструментов нажмите кнопку CommandButton.
        2. В пользовательской форме щелкните в левом нижнем углу, чтобы добавить стандартный размер Командная кнопка.
        3. Выбрав новый CommandButton, дважды щелкните свойство Name. в окне свойств.
        4. Введите:   cmdДобавьте
          и нажмите клавишу Enter
        5. Выбрав новый CommandButton, дважды щелкните свойство Caption. в окне свойств.
        6. Введите:   Добавьте эту часть
          и нажмите клавишу Enter
        7. Нажмите на пустую часть пользовательской формы, чтобы выбрать пользовательскую форму и для отображения панели инструментов.
        8. Повторите описанные выше шаги, чтобы добавить CommandButton с именем cmdClose , с этикеткой закрыть
        9. При необходимости вы можете изменить положение кнопок, перетащив их в новое место в пользовательской форме.

        Добавить код к кнопкам

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

        Добавить код на кнопку cmdAdd

        1. Нажмите кнопку cmdAdd
        2. В строке меню выберите Вид | Код.
        3. Это создает процедуру, в которую вы можете добавить свой код.

        4. Там, где мигает курсор, введите следующий код:
         Частная подпрограмма cmdAdd_Click()
        Dim iRow As Long
        Dim ws As рабочий лист
        Установите ws = Рабочие листы ("PartsData")
        
        'найти первую пустую строку в базе данных
        iRow = ws. Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Строка + 1
        
        'проверить номер детали
        Если Обрезать(Me.txtPart.Value) = "" Тогда
          Me.txtPart.SetFocus
          MsgBox "Пожалуйста, введите номер детали"
          Выйти из подпрограммы
        Конец, если
        
        'копируем данные в базу
        'используйте линии защиты и снятия защиты,
        ' с вашим паролем
        ' если рабочий лист защищен
        С ВС
        ' .Снять защиту с пароля:="пароль"
          .Cells(iRow, 1).Value = Me.txtPart.Value
          .Cells(iRow, 2).Value = Me.txtLoc.Value
          .Ячейки (iRow, 3). Значение = Me.txtDate.Value
          .Cells(iRow, 4).Value = Me.txtQty.Value
        ' .Защитить пароль:="пароль"
        Конец с
        
        'очистить данные
        Me.txtPart.Value = ""
        Me.txtLoc.Value = ""
        Me.txtDate.Value = ""
        Me.txtQty.Value = ""
        Me.txtPart.SetFocus
        
        Конец суб 
        1. В строке меню выберите Вид | Object, чтобы вернуться в UserForm.

        Добавить код к кнопке cmdClose

        1. Выберите кнопку cmdClose
        2. В строке меню выберите Вид | Код.
        3. Там, где мигает курсор, введите следующий код:
         Частная подпрограмма cmdClose_Click()
          Разгрузи меня
        Конец суб 
        1. В строке меню выберите Вид | Object, чтобы вернуться в UserForm.

        Чтобы разрешить пользователям закрывать форму нажатием клавиши Esc:

        1. Выберите кнопку cmdClose
        2. В окне свойств измените свойство Cancel на True   
        Чтобы запретить пользователям закрывать форму нажатием кнопки X

        Когда пользовательская форма открыта, справа вверху появляется значок X.Кроме того с помощью кнопки «Закрыть форму» люди смогут закрыть форму с помощью X. Если вы хотите предотвратить это, выполните следующие действия.

        1. Щелкните правой кнопкой мыши пустую часть пользовательской формы
        2. Выберите Вид | Код
        3. В раскрывающемся списке Процедура в правом верхнем углу выберите QueryClose
        4. Там, где мигает курсор, вставьте выделенный код из следующий образец
         Private Sub UserForm_QueryClose (Отмена как целое число, _
          CloseMode как целое число)
            Если CloseMode = vbFormControlMenu Тогда
            Отмена = Истина
            MsgBox "Пожалуйста, используйте кнопку закрытия формы!"
          Конец, если 
        Конец суб 
        1. В строке меню выберите Вид | Object, чтобы вернуться в UserForm.

        Теперь, если кто-то щелкнет X в пользовательской форме, он увидит ваше сообщение.

        Проверка пользовательской формы

        Чтобы протестировать форму, вы можете запустить ее из VBE.

        1. Нажмите на пустую часть пользовательской формы, чтобы выбрать пользовательскую форму и для отображения панели инструментов.
        2. В строке меню выберите «Выполнить» | Запустите Sub/UserForm.

        3. В текстовом поле Деталь введите: 12345
        4. Нажмите клавишу табуляции, чтобы перейти к следующему текстовому полю.
        5. Когда текстовые поля будут заполнены, нажмите «Добавить эту часть». кнопка.
        6. Нажмите кнопку «Закрыть форму», чтобы вернуться в VBE.

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

        1. Щелкните правой кнопкой мыши пустую часть пользовательской формы
        2. Выберите Порядок вкладок
        3. Выберите элемент управления в списке и нажмите кнопку Переместить вверх или Переместить Вниз кнопка
        4. Нажмите кнопку ОК

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

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

        1. Переключитесь на Excel и активируйте книгу PartLocDB.xls
        2. Дважды щелкните вкладку листа для Листа2
        3. Тип: Ввод данных о деталях
        4. Нажмите клавишу Enter
        5. На панели инструментов «Рисование» щелкните инструмент «Прямоугольник» (в Excel 2007 / 2010, используйте фигуру на вкладке «Вставка»)
        6. В центре рабочего листа нарисуйте прямоугольник и отформатируйте его по желанию.
        7. Выделив прямоугольник, введите:
          Нажмите здесь, чтобы добавить информацию о детали

        8. Щелкните правой кнопкой мыши границу прямоугольника и выберите «Назначить макрос».
        9. Нажмите кнопку Новый
        10. Там, где мигает курсор, введите: frmPartLoc.Показать
        11. Перейдите в окно Excel и нажмите кнопку, чтобы открыть форму пользователя.
        • ПРИМЕЧАНИЕ : пока пользовательская форма открыта, вы не сможете выполнять любые другие действия в Excel, такие как ввод данных в рабочий лист. Сначала вам нужно закрыть форму.
        • Если вы хотите, чтобы пользователи могли выполнять другие действия в Excel пока форма открыта, измените указанную выше строку на: frmPartLoc.Показывать False , чтобы отключить модальные настройки.

        Завершить рабочую тетрадь

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

        1. Переключитесь на Excel и активируйте книгу PartLocDB.xls
        2. Выберите лист PartsData .
        3. В строке меню выберите Формат | Лист | Скрыть (в Excel 2007/ 2010, используйте команды Формат на вкладке Главная)
        4. Удалить все остальные листы, кроме Ввод данных деталей
        5. Нажмите кнопку Щелкните здесь, чтобы добавить информацию о детали , и введите ваши данные в базу.
        6. Закройте и сохраните книгу

        Получить образец файла

        Учебники по теме

        Пользовательская форма с Комбинированные поля

        Форма пользователя Зависимые поля со списком

        Форма пользователя Комбобокс VBA

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

        Пользовательская форма со страницами справки

        Как создать электронную таблицу в Excel, Word и Google Sheets

        На вкладке Home вы управляете форматированием и внешним видом вашего листа, а также некоторыми простыми формулами, которые вам всегда понадобятся.

         

        A. Инструменты копирования и вставки: Используйте эти инструменты для быстрого дублирования данных и стилей форматирования в электронной таблице. Инструмент Копировать может либо скопировать выбранную ячейку или группу ячеек, либо скопировать область электронной таблицы, которую вы будете использовать в качестве изображения в другом документе. Инструмент Cut перемещает выделенные ячейки в новое место назначения, а не дублирует их.

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

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

        B. Инструменты визуального форматирования: Многие из этих инструментов аналогичны инструментам Microsoft Word. Вы можете использовать инструменты форматирования, чтобы изменить шрифт, размер и цвет набранных слов, а также сделать их полужирными, курсивными или подчеркнутыми. Он также имеет несколько параметров форматирования для электронных таблиц. Вы можете выбрать, какие стороны ячейки получат дополнительные границы, а также их стиль и толщину. Вы также можете изменить цвет выделения всей ячейки. Это полезно для создания визуально привлекательных границ или различения строк или столбцов на больших листах или для выделения определенной ячейки, которую вы хотите выделить.

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

        D. Возможности форматирования нескольких ячеек: Этот раздел содержит две очень важные функции, которые решают типичные проблемы новых пользователей Excel. Первый — Wrap Text .Обычно, когда вы вводите текст в ячейку, которая превышает размер ячейки, он перетекает в следующую ячейку. Например, если вы введете «Бюджетные элементы» в ячейку A1, часть слова «Элементы» попадет в ячейку B1. Затем, если вы наберете B1, вы закроете все символы из A1, которые расширяются до B1. Дополнительный текст из ячейки A1 все еще существует, но теперь он скрыт. Если вы не хотите расширять ячейки, щелкните значок Wrap Text на A1 — это разделит «Элементы бюджета» на две сложенные строки вместо одной в A1.Это делает всю строку выше для размещения содержимого. Теперь ввод в B1 не закроет существующий текст.

        Другим инструментом в этом разделе является Объединить и Центрировать . Бывают случаи, когда вы можете захотеть объединить несколько ячеек и заставить их действовать как одна длинная ячейка. Например, вы можете захотеть, чтобы заголовок для всей таблицы был четким и легко читаемым. Выберите все ячейки, которые вы хотите объединить, нажмите Объединить , а затем введите заголовок и отформатируйте его. Хотя по умолчанию для заголовков используется центрированный текст, просто щелкните стрелку раскрывающегося списка, чтобы выбрать различные параметры объединения и разделения.

        E. Настройки формата на основе чисел: В раскрывающемся меню есть параметры для форматирования чисел. Например, валюта помещает все, что вы выбираете, в формат «0,00 доллара США», а процентов превращает 0,5 или ½ в «50%», параметры даты. Это основные параметры формата, но вы можете выбрать Дополнительные форматы номеров в раскрывающемся меню, чтобы получить более специальные варианты использования (валюты разных стран или добавление форматирования «(xxx)xxx-xxxx» к последовательностям телефонных номеров. ).Часто вы можете использовать эти инструменты для целых столбцов, чтобы все данные в одной категории вели себя одинаково.

        F. Форматирование таблицы или листа: Форматировать как таблицу и Стили ячеек позволяют использовать предустановки или настраивать таблицы (например, с чередующимися цветами строк и выделенными полосами заголовков). Выберите диапазон данных и выберите стиль для стандартизации форматирования.

        Условное форматирование немного сложнее. Используйте раскрывающееся меню, чтобы выбрать один из вариантов, например вставить полезные визуальные значки для представления состояния или завершения или изменить цвет разных строк.Наиболее важными являются условных правил , которые созданы с помощью простой логики. Например, предположим, что у вас есть столбец с данными от A1 до A3, а A4 содержит сумму этих трех ячеек. Вы можете поместить форматирование на A4 с правилом, которое гласит: «Если A4 > 0, то выделить A4 зеленым цветом». Затем вы можете добавить еще одно правило, которое гласит: «Если A4 < 0, то выделить A4 красным цветом». Теперь у вас есть краткая визуальная ссылка, где зеленый = положительное число, а красный = отрицательное число, которое будет меняться в зависимости от того, что вы вводите в A1, A2 и A3.

        G. Инструменты форматирования строк и столбцов: Раскрывающееся меню Вставка помещает ячейки, строки или столбцы до или после выбранной области на листе, а Удалить удаляет их. Раскрывающийся список Format позволяет изменять высоту строк и ширину столбцов. Он также имеет параметры для скрытия и отображения определенных разделов.

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

        Используйте Fill , чтобы взять содержимое ячейки и расширить его в любом направлении на любое количество ячеек. Если ячейка содержит значение, Заполнить будет просто копировать значение снова и снова. Если он содержит формулу, он будет пересчитывать свое относительное положение для каждой новой ячейки. Если первая ячейка равна A1+B1, то следующая будет равна A2+B2 и так далее.

        Кнопка Очистить позволяет либо очистить значение, либо просто очистить форматирование ячейки.

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

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

         

        A. Эти инструменты управляют сводными таблицами , важной функцией Excel. Воспринимайте сводные таблицы как «отчеты», быстрый способ просмотреть все данные, проанализировать тенденции и сделать выводы. Выбрав как минимум две строки данных и щелкнув сводную таблицу , вы сможете быстро создать визуально привлекательную таблицу. При выполнении этого процесса запускается PivotTable Builder , который помогает выбирать столбцы для включения, сортировать их и перетаскивать для быстрого создания таблицы.Они могут включать сворачиваемые строки, чтобы сделать отчеты интерактивными и лаконичными. Также есть кнопка для рекомендуемых сводных таблиц, которая может помочь, когда вы не знаете, с чего начать.

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

        B. В этом разделе можно вставлять визуальные элементы, такие как файлы изображений, готовые фигуры и SmartArt.Вы можете добавлять фигуры и изменять их размер, цвет и положение для создания интуитивно понятных наборов данных и отчетов. Объекты SmartArt — это готовые диаграммы, в которые можно вставлять текст и информацию. Они отлично подходят для представления того, о чем говорят данные, в другом месте вашей рабочей тетради.

        C. Эти инструменты предназначены для вставки элементов из других продуктов Microsoft, таких как Bing Maps, готовых информационных карточек о людях (только из учетных записей Microsoft) и надстроек из их магазина.

        D. Используйте эти инструменты для создания диаграмм и графиков.Большинство из них работают, только если вы выберете один или несколько наборов данных (только числа, со словами для заголовков или категорий). Диаграммы и графики функционируют так, как вы ожидаете — просто выберите данные, которые вы хотите визуализировать, а затем выберите нужный тип визуализации (гистограммы, точечные диаграммы, круговые диаграммы или линейные графики). При его создании появятся параметры форматирования, где вы можете изменить цвет, метки и многое другое.

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

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

        G. Этот инструмент гиперссылки позволяет превратить ячейку или таблицу в интерактивную ссылку. Как только зритель нажмет на затронутую ячейку (ячейки), он попадет на любой выбранный вами веб-сайт или сайт интрасети.

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

        I. Текстовое поле полезно, когда вы создаете отчет и не хотите, чтобы вводимые слова вели себя как ячейки. Это позволяет легко перемещать текст, а не вырезать и вставлять ячейки (что потенциально может испортить форматирование реальных данных). Следующая область предназначена для Верхних и нижних колонтитулов , что приведет вас к представлению макета страницы — здесь вы можете добавить верхние и нижние колонтитулы для всей страницы. WordArt , с другой стороны, позволяет украшать текст. Вставка Object позволяет размещать целые файлы (документы Word, PDF и т. д.) на листе.

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

         

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

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

        C. Это позволяет вам выбрать, сколько страниц в ширину и сколько страниц вниз вы хотите напечатать.

        D. В этом разделе можно включить отображение автоматических сеток для работы с листом и его печати вместе с заголовками строк и столбцов (A, B, C, 1, 2, 3 и т. д.).

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

         

        A. Кнопка Insert Function полезна для тех, кто не знает стенографии.Это вызывает дополнительный раздел Formula Builder , в котором описывается каждая функция, и вы можете выбрать ту, которую хотите использовать.

        B. Эти кнопки делят все функции по категориям.

        • Автосумма работает так же, как и на вкладке Главная.
        • Недавно использованные удобен для вызова часто используемых формул, чтобы сэкономить время на просмотр меню.
        • Финансовый включает в себя все, что связано с валютой, стоимостью, амортизацией, доходностью, курсом и многим другим.
        • Логический включает условные функции, такие как «ЕСЛИ X, ТО Y».
        • Функции Text помогают очищать, регулировать и анализировать ячейки простого текста, например отображать количество символов в ячейке (полезно для сообщений Twitter), объединять две разные строки с помощью Concatenate или извлекать числовые значения из текстовых записей, которые не правильно отформатированы.
        • Дата и время Функции помогают придать смысл ячейкам, отформатированным по времени, и включают такие записи, как «СЕГОДНЯ», которые вводят текущую дату.
        • Функции поиска и справочника помогают извлекать информацию из разных частей рабочей книги, избавляя вас от необходимости искать ее.
        • Math & Trig Функции — это именно то, на что они похожи, они включают в себя все виды математических дисциплин, которые вы только можете себе представить.
        • Дополнительные функции включает статистические и инженерные данные.

        C. Этот раздел содержит параметры тегов. Если есть диапазон ячеек или таблица, на которые вам часто приходится ссылаться в формулах, вы можете определить их имя и пометить здесь.Например, предположим, что у вас есть столбец, содержащий весь список продуктов, которые вы продаете. Вы можете выделить имена в этом списке и Определить имя как «Список продуктов». Каждый раз, когда вы хотите сослаться на этот столбец в формуле, вы можете просто ввести «ProductList» (вместо того, чтобы снова искать этот набор данных или запоминать их позиции в ячейках).

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

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

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

         

        A. Это инструменты импорта базы данных, позволяющие импортировать данные из любой веб-, файловой или серверной базы данных.

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

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

        D. Это инструменты для обработки данных. Вы можете взять одну длинную строку, например, разделенную запятыми или пробелами, и разделить ее на столбцы с помощью Text to Columns . Вы можете искать и удалять дубликаты, объединять ячейки и проверять, соответствуют ли данные определенным критериям, чтобы оценить их точность. Анализ «что, если» помогает заполнить пробелы неполными данными, используя существующие данные и тенденции, чтобы определить вероятные результаты для новых сценариев.

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

         
        Вкладка Review является частью ленты , которая помогает при совместном использовании и проверке точности.

         

        A. Это простые текстовые проверки (как в Word), которые позволяют найти ячейки с орфографическими ошибками или найти более подходящие слова с помощью Тезауруса.

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

        C. Инструменты комментирования позволяют соавторам «общаться» друг с другом на листе.

        D. Инструменты защиты и совместного использования позволяют приглашать соавторов и ограничивать доступ к определенным частям листа.Вы можете вручную назначить различные уровни доступа — например, вы можете разрешить подрядчику редактировать только ячейки, относящиеся к часам, которые он отработал, но не ячейки, в которых рассчитывается его заработная плата. Как и в случае с Word, совместное использование листа с помощью Tracked Changes означает, что вы можете видеть все, что было сделано с листом.

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

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

         

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

        B. Используйте эти кнопки, чтобы выбрать, хотите ли вы видеть сетки, заголовки, строку формул и линейку.

        C. Это еще один способ управления увеличением и уменьшением ячеек.

        D. Область замораживания Элементы управления являются важной частью создания пригодной для использования электронной таблицы. Используя эти инструменты, вы можете зафиксировать ряд строк и/или столбцов во время прокрутки.Например, если в первой строке были все заголовки ваших столбцов, и она оставалась замороженной, вы всегда будете знать, на какой столбец смотрите, когда прокручиваете вниз.

        E. Макросы — это способ автоматизации процессов в Excel. Однако это далеко за пределами Excel 101.

        ВПР на нескольких листах в Excel с примерами

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

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

        Как выполнять ВПР между двумя листами

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

        Общая формула ВПР с другого листа выглядит следующим образом:

        ВПР(искомое_значение, Лист!диапазон , col_index_num, [диапазон_поиска])

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

        • Lookup_values ​​ находятся в столбце A на листе Summary , и мы ссылаемся на первую ячейку данных, которая является A2.
        • Table_array — это диапазон A2:B6 на листе за январь. Чтобы сослаться на него, поставьте перед ссылкой на диапазон имя листа, за которым следует восклицательный знак: Jan!$A$2:$B$6.

          Обратите внимание, что мы блокируем диапазон абсолютными ссылками на ячейки, чтобы предотвратить его изменение при копировании формулы в другие ячейки.
          Col_index_num равно 2, потому что мы хотим скопировать значение из столбца B, который является 2-м -м столбцом в массиве таблиц.

        • Range_lookup имеет значение FALSE для поиска точного совпадения.

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

        =ВПР(A2, янв!$A$2:$B$6, 2, ЛОЖЬ)

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

        Аналогичным образом вы можете выполнять Vlookup данные из листов Feb и Mar :

        =ВПР(A2, февраль!$A$2:$B$6, 2, ЛОЖЬ)

        =ВПР(A2, март!$A$2:$B$6, 2, ЛОЖЬ)

        Советы и примечания:
        • Если имя листа содержит пробелов или неалфавитных символов , оно должно быть заключено в одинарные кавычки, например ‘Jan Sales’!$A$2:$B$6 .Дополнительные сведения см. в разделе Как сослаться на другой лист в Excel.
        • Вместо того, чтобы вводить имя листа непосредственно в формуле, вы можете переключиться на лист поиска и выбрать там диапазон. Excel автоматически вставит ссылку с правильным синтаксисом, избавив вас от необходимости проверять имя и устранять неполадки.

        ВПР из другой книги

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

        Например, чтобы найти значение A2 в диапазоне A2:B6 на листе Jan в рабочей книге Sales_reports.xlsx , используйте следующую формулу:

        =ВПР(A2, [Sales_reports.xlsx]Ян!$A$2:$B$6, 2, ЛОЖЬ)

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

        Vlookup на нескольких листах с IFERROR

        Если вам нужно искать между более чем двумя листами, самое простое решение — использовать функцию ВПР в сочетании с ЕСЛИОШИБКА.Идея состоит в том, чтобы вложить несколько функций ЕСЛИОШИБКА для проверки нескольких рабочих листов одну за другой: если первая функция ВПР не находит соответствия на первом листе, выполняется поиск на следующем листе и т. д.

        ЕСЛИОШИБКА(ВПР(…), ЕСЛИОШИБКА(ВПР(…), …, » Не найдено «))

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

        Сначала мы вытащим предметы.Для этого мы указываем формуле ВПР искать номер заказа в A2 на листе East и возвращать значение из столбца B (столбец 2 nd в table_array A2:C6). Если точное совпадение не найдено, то ищите в листе West . Если оба Vlookup терпят неудачу, возвращается «Не найдено».

        =ЕСЛИОШИБКА(ВПР(A2, Восток!$A$2:$C$6, 2, ЛОЖЬ), ЕСЛИОШИБКА(ВПР(A2, Запад!$A$2:$C$6, 2, ЛОЖЬ), "Не найдено") )

        Чтобы вернуть сумму, просто измените номер индекса столбца на 3:

        =ЕСЛИОШИБКА(ВПР(A2, Восток!$A$2:$C$6, 3, ЛОЖЬ), ЕСЛИОШИБКА(ВПР(A2, Запад!$A$2:$C$6, 3, ЛОЖЬ), "Не найдено") )

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

        Vlookup в нескольких книгах

        Чтобы выполнить визуальный поиск между двумя или более книгами, заключите имя книги в квадратные скобки и поместите его перед именем листа. Например, вот как вы можете выполнить Vlookup в два разных файла ( Book1 и Book2 ) с помощью одной формулы:

        =ЕСЛИОШИБКА(ВПР(A2, [Книга1.xlsx]Восток!$A$2:$C$6, 2, ЛОЖЬ), ЕСЛИОШИБКА(ВПР(A2, [Book2.xlsx]Запад!$A$2:$C$6, 2, ЛОЖЬ),"Не найдено"))

        Сделать номер индекса столбца динамическим для нескольких столбцов Vlookup

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

        • Для аргумента col_index_num используйте функцию COLUMNS, которая возвращает количество столбцов в указанном массиве: COLUMNS($A$1:B$1).(Координата строки не имеет особого значения, это может быть просто любая строка.)
        • В аргументе искомое_значение заблокируйте ссылку на столбец с помощью знака $ ($A2), чтобы он оставался фиксированным при копировании формулы в другие столбцы.

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

        =ЕСЛИОШИБКА(ВПР($A2, Восток!$A$2:$C$6, СТОЛБЦЫ($A$1:B$1), ЛОЖЬ), ЕСЛИОШИБКА(ВПР($A2, Запад!$A$2:$C$6, COLUMNS($A$1:B$1), FALSE), "Не найдено"))

        При вводе в столбец B COLUMNS($A$1:B$1) оценивается как 2, указывая VLOOKUP вернуть значение из столбца 2 nd в массиве таблиц.

        При копировании в столбец C (т. е. при перетаскивании формулы из B2 в C2) B$1 изменяется на C$1, поскольку ссылка на столбец является относительной. Следовательно, COLUMNS($A$1:C$1) оценивается как 3, заставляя VLOOKUP возвращать значение из столбца 3 rd .

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

        Vlookup нескольких листов с INDIRECT

        Еще один способ ВПР между несколькими листами в Excel — использование комбинации функций ВПР и ДВССЫЛ.Этот метод требует небольшой подготовки, но в итоге у вас будет более компактная формула для ВПР в любом количестве электронных таблиц.

        Общая формула для ВПР по листам выглядит следующим образом:

        ВПР( искомое_значение , ДВССЫЛ(«‘»&ИНДЕКС( Искомые_листы , ПОИСКПОЗ(1, —(СЧЁТЕСЛИ(ДВССЫЛ(«‘» & Лист_список & «‘! искомый_диапазон «), искомое_значение) >0), 0)) & «‘! table_array «), col_index_num , FALSE)

        Где:

        • Lookup_sheets — именованный диапазон, состоящий из имен листов поиска.
        • Lookup_value — значение для поиска.
        • Lookup_range — диапазон столбцов в таблицах поиска, где искать значение поиска.
        • Table_array — диапазон данных в справочниках.
        • Col_index_num — номер столбца в табличном массиве, из которого нужно вернуть значение.

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

        • Это формула массива, которую необходимо ввести, нажав одновременно клавиши Ctrl + Shift + Enter.
        • Все листы должны иметь одинаковый порядок столбцов .
        • Поскольку мы используем один массив таблиц для всех листов поиска, укажите наибольший диапазон , если ваши листы имеют разное количество строк.

        Как использовать формулу для ВПР между листами

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

        1. Запишите все имена поисковых листов где-нибудь в своей книге и назовите этот диапазон ( Lookup_sheets в нашем случае).
        2. Скорректируйте общую формулу для ваших данных. В этом примере мы будем:
          • поиск значения A2 ( lookup_value )
          • в диапазоне A2:A6 ( lookup_range ) на четырех листах ( Восток , Север , Юг и Запад ) и
          • извлекает совпадающие значения из столбца B, который является столбцом 2 ( col_index_num ) в диапазоне данных A2:C6 ( table_array ).

          С приведенными выше аргументами формула принимает следующий вид:

          = ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Лист_просмотра, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'"& Просматриваемые_листы&"'!$A$2:$A$6"), $A2)> 0), 0)) &"'!$A$2:$C$6"), 2, ЛОЖЬ)

          Обратите внимание, что мы блокируем оба диапазона ($A$2:$A$6 и $A$2:$C$6) абсолютными ссылками на ячейки.

        3. Введите формулу в самую верхнюю ячейку (B2 в этом примере) и нажмите Ctrl + Shift + Enter, чтобы завершить ее.
        4. Дважды щелкните или перетащите маркер заполнения, чтобы скопировать формулу вниз по столбцу.

        В результате у нас есть формула для поиска номера заказа на 4 листах и ​​извлечения соответствующего товара. Если определенный номер заказа не найден, отображается ошибка #Н/Д, как в строке 14:

        .

        Чтобы вернуть сумму, просто замените 2 на 3 в аргументе col_index_num , так как суммы находятся в столбце 3 rd массива таблиц:

        =ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Лист_просмотра, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'" & Просматриваемые_листы & "'!$A$2:$A$6"), $A2) >0), 0)) & "'!$A$2:$C$6"), 3, ЛОЖЬ)

        Если вы хотите заменить стандартное обозначение ошибки #N/A своим собственным текстом, оберните формулу в функцию IFNA:

        = ЕСЛИНА(ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Лист_просмотра, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'" & Просматриваемые_листы & "'!$A$2:$A$6")), $ A2)>0), 0)) & "'!$A$2:$C$6"), 3, FALSE), "Не найдено")

        Виртуальный просмотр нескольких листов между книгами

        Эта общая формула (или любой ее вариант) также может использоваться для визуального поиска нескольких листов в другой рабочей книге .Для этого соедините имя рабочей книги внутри INDIRECT, как показано в приведенной ниже формуле:

        .

        =ЕСЛИНА(ВПР($A2, ДВССЫЛ("'[Книга1.xlsx]" & ИНДЕКС(Лист_просмотра, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'[Книга1.xlsx]" & Листы_просмотра & "'! $A$2:$A$6"), $A2)>0), 0)) & "'!$A$2:$C$6"), 2, FALSE), "Не найдено")

        Vlookup между листами и возвращение нескольких столбцов

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

        В этом примере мы хотим вернуть имена элементов (столбец B) и суммы (столбец C), которые являются столбцами 2 nd и 3 rd в массиве таблицы соответственно. Итак, искомый массив {2,3}.

        = ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Лист_просмотра, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'"& Листы_просмотра &"'!$A$2:$C$6"), $A2) >0), 0)) &"'!$A$2:$C$6"), {2,3}, ЛОЖЬ)

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

        • В первой строке выберите все ячейки для заполнения (в нашем примере B2:C2).
        • Введите формулу и нажмите Ctrl + Shift + Enter. При этом в выбранные ячейки вводится одна и та же формула, которая возвращает разные значения в каждом столбце.
        • Перетащите формулу в оставшиеся строки.

        Как работает эта формула

        Чтобы лучше понять логику, давайте разобьем эту базовую формулу на отдельные функции:

        = ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Лист_просмотра, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'"& Листы_просмотра&"'!$A$2:$A$6"), $A2)> 0), 0)) &"'!$A$2:$C$6"), 2, ЛОЖЬ)

        Работая изнутри наружу, вот что делает формула:

        СЧЁТЕСЛИ и ДВССЫЛ

        В двух словах, ДВССЫЛ создает ссылки для всех листов поиска, а СЧЁТЕСЛИ подсчитывает количество вхождений значения поиска (A2) в каждом листе:

        --(СЧЁТЕСЛИ(ДВССЫЛ("'"&Lookup_sheets&"'!$A$2:$A$6"), $A2)>0)

        Подробнее:

        Сначала вы объединяете имя диапазона (Lookup_sheets) и ссылку на диапазон ($A$2:$A$6), добавляя апострофы и восклицательный знак в нужных местах, чтобы получить внешнюю ссылку, и передаете полученную текстовую строку в НЕПРЯМОЙ функция для динамического обращения к листам поиска:

        INDIRECT({"'Восток'!$A$2:$A$6"; "'Юг'!$A$2:$A$6"; "'Север'!$A$2:$A$6"; "'Запад '!$A$2:$A$6"})

        СЧЁТЕСЛИ проверяет каждую ячейку в диапазоне A2:A6 на каждом листе подстановки со значением в A2 на основном листе и возвращает количество совпадений для каждого листа.В нашем наборе данных номер заказа в A2 (101) находится на листе West , что равно 4 th в именованном диапазоне, поэтому СЧЁТЕСЛИ возвращает этот массив:

        {0;0;0;1}

        Далее вы сравниваете каждый элемент вышеуказанного массива с 0:

        --({0; 0; 0; 1}>0)

        Это дает массив значений TRUE (больше 0) и FALSE (равно 0), которые вы приводите к 1 и 0 с помощью двойного унарного (—), и в результате получаете следующий массив:

        {0; 0; 0; 1}

        Эта операция является дополнительной мерой предосторожности для обработки ситуации, когда лист поиска содержит несколько вхождений искомого значения, и в этом случае СЧЁТЕСЛИ вернет число больше 1, в то время как нам нужны только 1 и 0 в конечном массиве (через мгновение , вы поймете почему).

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

        ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Просмотр_листов, ПОИСКПОЗ(1, {0;0;0;1}, 0)) &"'!$A$2:$C$6"), 2, ЛОЖЬ )

        ИНДЕКС и ПОИСКПОЗ

        В этот момент вступает в действие классическая комбинация ПОИСКПОЗ ИНДЕКС:

        ИНДЕКС(Lookup_sheets, MATCH(1, {0;0;0;1}, 0))

        Функция ПОИСКПОЗ, настроенная на точное совпадение (0 в последнем аргументе), ищет значение 1 в массиве {0;0;0;1} и возвращает его позицию, которая равна 4:

        ИНДЕКС(Lookup_sheets, 4)

        Функция ИНДЕКС использует число, возвращенное ПОИСКПОЗ, в качестве аргумента номера строки (номер_строки) и возвращает 4-е значение в именованном диапазоне Просматриваемые_листы , то есть Запад .

        Таким образом, формула сводится к следующему:

        ВПР($A2, ДВССЫЛ("'"&"Запад"&"'!$A$2:$C$6"), 2, ЛОЖЬ)

        ВПР и НЕПРЯМАЯ

        Функция ДВССЫЛ обрабатывает текстовую строку внутри нее:

        НЕПРЯМАЯ("'"&"Запад"&"'!$A$2:$C$6")

        И преобразует его в ссылку, ведущую к аргументу table_array функции ВПР:

        ВПР($A2, 'Запад'!$A$2:$C$6, 2, ЛОЖЬ)

        Наконец, эта очень стандартная формула ВПР ищет значение A2 в первом столбце диапазона A2:C6 на листе West и возвращает совпадение из столбца 2 nd .Вот и все!

        Динамическая ВПР для возврата данных с нескольких листов в разные ячейки

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

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

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

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

        • East_Sales — A2:B6 на листе Восток
        • North_Sales — A2:B6 на северном листе
        • South_Sales — A2:B6 на Южном листе
        • West_Sales — A2:B6 на западном листе

        ВПР и вложенные ЕСЛИ

        Если у вас есть достаточное количество листов для поиска, вы можете использовать вложенные функции ЕСЛИ, чтобы выбрать лист на основе ключевых слов в предопределенных ячейках (ячейки с B1 по D1 в нашем случае).

        С искомым значением в A2 формула выглядит следующим образом:

        =ВПР($A2, ЕСЛИ(B$1="восток", Восток_Продажи, ЕСЛИ(B$1="север", Север_Продажи, ЕСЛИ(B$1="юг", Юг_Продажи, ЕСЛИ(B$1="запад", Запад_Продажи)))), 2, ЛОЖЬ)

        В переводе на английский язык часть IF гласит:

        Если B1 равен East , просмотрите диапазон с именем East_Sales ; если B1 — North , посмотрите в диапазоне с именем North_Sales ; если B1 равен South , посмотрите в диапазоне с именем South_Sales ; и если B1 равен West , посмотрите в диапазоне с именем West_Sales .

        Диапазон, возвращаемый IF, переходит к table_array функции VLOOKUP, которая извлекает совпадающее значение из столбца 2 nd на соответствующем листе.

        Умное использование смешанных ссылок для искомого значения ($A2 — абсолютный столбец и относительная строка) и логической проверки ЕСЛИ (B$1 — относительный столбец и абсолютная строка) позволяет копировать формулу в другие ячейки без каких-либо изменений — Excel корректирует ссылки автоматически на основе относительного положения строки и столбца.

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

        НЕПРЯМАЯ ВПР

        При работе с большим количеством листов несколько уровней вложенности могут сделать формулу слишком длинной и трудной для чтения. Гораздо лучший способ — создать динамический диапазон просмотра с помощью ДВССЫЛ:

        =ВПР($A2, ДВССЫЛ(B$1&"_Продажи"), 2, ЛОЖЬ)

        Здесь мы объединяем ссылку на ячейку, содержащую уникальную часть именованного диапазона (B1) и общую часть (_Продажи).Это создает текстовую строку, например «East_Sales», которая ДВССЫЛЕННО преобразуется в имя диапазона, понятное Excel.

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

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

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

        Примеры нескольких листов Vlookup (файл .xlsx)

        Вас также может заинтересовать

        (Не рекомендуется) Читать Microsoft Файл электронной таблицы Excel

        xlsread не рекомендуется.Вместо этого используйте readtable , readmatrix или readcell . Удалять не планируется xlsread .

        Начиная с R2019a, импорт данных электронной таблицы в виде таблицы, матрицы или массива ячеек с помощью readtable , readmatrix или readcell соответственно. читаемый , readmatrix и readcell имеют функции эти преимущества по сравнению с функцией xlsread :

        • Улучшенная межплатформенная поддержка и производительность

        • Автоматическое определение формата и типа данных

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

        В этой таблице показано типичное использование xlsread и способы обновления ваш код для использования readtable , readmatrix или вместо readcell .

        6 900 xlsread :

        не рекомендуется

        рекомендуется

        Чтение данных электронной таблицы в виде таблица:

        Однако, чтобы продолжить чтение ваших данных в виде матрицы, use:

        Чтение данных электронной таблицы в виде массива ячеек с использованием xlsread :

         [~,~,C] = xlsread(имя файла) 

        Импорт данных электронной таблицы в виде таблица:

        Однако, чтобы продолжить импорт данных в виде массива ячеек, use:

        Чтение определенного листа и диапазона в виде матрицы с использованием xlsread :

         M = xlsread(имя файла,лист,диапазон) 

        Чтение определенного листа и диапазона как таблица:

         T = readtable(имя файла,'Лист',лист,'Диапазон',диапазон) 
        Однако, чтобы продолжить чтение ваших данных в виде матрицы, use:
         M = readmatrix(имя файла,'Лист',лист,'Диапазон',диапазон) 

        Прочитать определенный лист и диапазон как массив ячеек, используя xlsread :

         [~,~,C] = xlsread(имя файла,лист,диапазон) 

        Чтение определенного листа и диапазона как таблица:

         T = readtable(имя файла,'Лист',лист,'Диапазон',диапазон) 
        Однако, чтобы продолжить чтение ваших данных в виде ячейки array:
         C = readcell(filename,'Sheet',sheet,'Range',range) 

        Создать форму в Excel для ввода данных

        данные являются частью вашей повседневной работы.

        Это поможет вам избежать ошибок и ускорить процесс ввода данных . Это также поможет вам сосредоточиться на одной записи за раз !

        Это удобный и быстрый способ ввода записей в Excel путем отображения одной строки информации за раз без необходимости перехода от одного столбца к другому.

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

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

        Попрощайтесь с вводом данных в эту таблицу построчно по строкам….

         

        Ниже мы рассмотрим 11 лучших советов и рекомендаций по вводу данных в Excel , которые будут вам полезны:

        1. #1 – Создать форму в Excel Панель инструментов (QAT)
        2. # 3 — Доступ к форме в любое время
        3. # 4 — Обзор через записи
        4. # 5 — редактировать существующую запись
        5. # 6 — Критерии поиска
        6. # 7 — Восстановить запись
        7. # 8 — проверка данных в формах
        8. # 9 — удалить запись
        9. # 10 — Закрыть форму
        10. # 11 — сочетания клавиш клавиатуры для форм ввода данных

         

        Обязательно загрузите книгу Excel ниже и следуйте инструкциям:

        ЗАГРУЗИТЕ РАБОЧУЮ ТЕТРАДЬ EXCEL

        Хотите знать, как использовать данные ry Форма?

        *** Посмотрите наше видео и пошаговое руководство ниже с бесплатной загружаемой рабочей книгой Excel для практики ***

        Смотрите на YouTube и ставьте лайки!

        Загрузите обучающий файл YouTube Excel

         

        Смотрите его на YouTube и ставьте лайки!

        Загрузите обучающий файл YouTube Excel

        Посмотрите его на YouTube и поставьте лайк!

        Загрузите учебный файл YouTube Excel

        1.Создать форму в Excel

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

        *** Посмотрите наше видео ниже о том, как создать форму в Excel за 5 минут!*** в Excel с минутами без использования VBA!!

        Выполните следующие действия:

        ШАГ 1: Преобразуйте имена столбцов в таблицу, перейдите к Вставка> Таблица

        Убедитесь, что Моя таблица имеет 46 заголовков 90.

        ШАГ 2: Давайте добавим Функциональность создания формы , чтобы понять, как создать заполняемую форму в Excel.

        Перейдите к Файл > Параметры

         

        ШАГ 3: Перейдите к Настроить ленту.

        Выберите Команды не на ленте и Форма . Это тот функционал, который нам нужен.

        Нажмите  Новая вкладка.

         

        ШАГ 4: В разделе Новая вкладка выберите Новая группа, и нажмите Добавить.

        Это добавит форм на новую вкладку на нашей ленте.

        Уведомление о том, что есть также Rename кнопка, вы можете использовать его, чтобы переименовать новую вкладку и новой группы в нечто более описательное, как Форма :

        Шаг 5 : Выберите свою таблицу и на новой вкладке Form выберите Form .

         

        ШАГ 6: Появится новое диалоговое окно формы!

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

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

        Нажмите Закрыть, чтобы выйти из этого экрана и просмотреть данные в таблице Excel.

         

        Теперь вы можете использовать эту новую форму для непрерывного ввода данных в таблицу Excel!

         

         

        2.Добавить на панель быстрого доступа (QAT)

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

        Чтобы добавить на панель быстрого доступа, выполните следующие действия:

        ШАГ 1: Щелкните маленькую стрелку рядом с QAT .

        ШАГ 2: Нажмите Дополнительные команды в раскрывающемся списке.

        ШАГ 3: В диалоговом окне «Параметры Excel» выберите Все команды из Выберите команды из списка .

        ШАГ 4: Выберите Форма из списка и нажмите Добавить>> .

        ШАГ 5: Форма теперь доступна на панели быстрого доступа настройки . Нажмите ОК .

        Форма ввода данных теперь является частью панели быстрого доступа.

         

        3. Доступ к форме в любое время

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

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

         

        4. Обзор через записи

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

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

        Это сэкономит время, если у вас есть данные с несколькими столбцами и записями.

         

        5. Редактировать существующую запись

        Используйте кнопки Найти предыдущую и Найти следующую , чтобы найти запись , которую нужно отредактировать.

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

        Таблица данных будет обновлена ​​с учетом внесенных изменений.

         

        6. Критерии поиска

        Использование подстановочных знаков

        ).

        ШАГ 1: В форме ввода данных нажмите кнопку Criteria

        ШАГ 2: слово восток)

        ШАГ 3: Нажмите Найти далее , чтобы найти записи, содержащие слово восток .

        Форма ввода данных Excel найдет для вас три записи в этом сценарии!

         

        Использование знака «больше» или «меньше»

        Если вы хотите найти людей с зарплатой больше или равной 75 000 долларов США, вы можете сделать это, выполнив следующие действия:

        ШАГ 1: ШАГ 46: В форме ввода данных нажмите кнопку Критерии

        ШАГ 2: В поле Зарплата введите >=75000.

        ШАГ 3: Нажмите Найти далее , чтобы найти все записи с зарплатой больше или равной 75 000 долларов США .

         

        7. Восстановить запись

        Предположим, вы случайно удалили имя записи.

        И вы не помните, что было написано в этом поле! Не паникуйте.

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

        Данные снова появятся в соответствующем поле.

        Одна вещь, которую вам нужно иметь в виду, это то, что кнопка «Восстановить» полезна только в том случае, если вы не нажали Enter .

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

         

        8. Проверка данных в формах

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

        Посмотрим как!

        Допустим, вы добавляете правило списка в столбец «Регион» с помощью проверки данных.

        ШАГ 1: Выберите столбец Region .

        ШАГ 2: Перейдите на вкладку Данные > Инструменты данных (группа) > Проверка данных .

        ШАГ 3: В диалоговом окне «Проверка данных» щелкните раскрывающийся список Разрешить и выберите Список .

        ШАГ 4: В поле Источник введите Северо-восток, Северо-запад, Юго-восток, Юго-запад, и нажмите OK .

        Проверка данных теперь вставлена ​​в столбец «Регион», где вам разрешено вводить только значения, представленные в списке (северо-восток, северо-запад, юго-восток, юго-запад).

         

        ШАГ 5: Щелкните значок Forms в QAT.

        ШАГ 6: Измените регион для записи 1 с северо-восток на восток и нажмите OK .

        После нажатия ОК, вы увидите сообщение об ошибке , как показано ниже:

        9. Удалить запись

        Шаг 1: Используйте полосу прокрутки для найма запись, которую вы хотите удалить.

        ШАГ 2: Просто нажмите кнопку Удалить .

        ШАГ 3: На экране появится подтверждающее сообщение. Нажмите OK .

        Требуемая запись будет удалена из таблицы данных.

        10. Закройте форму

        Чтобы закрыть диалоговое окно для форм данных, просто нажмите кнопку «Закрыть» (X) в правом верхнем углу бикса.

         

        11. Сочетания клавиш для форм ввода данных

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

      4. Нажмите Введите , чтобы перейти к следующей записи в формах Excel.
      5. Нажмите кнопку Esc на клавиатуре, чтобы закрыть форму Excel.
      6.  

        Это завершает наш учебник по 11 основным вещам, которые вы должны знать , если ввод данных — это то, что вы делаете в Excel. Это не только сделает процесс быстрее, но и намного проще и веселее!

        При использовании формы ввода данных Excel нужно помнить следующее:

        • Вы можете добавить максимум 32 поля на запись.
        • Вы не можете распечатать запись формы данных.
        • Прежде чем нажать Enter, вы можете восстановить все изменения, внесенные в данные.

        Итак, попробуйте! Я уверен, что вам понравится!!

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

Добавить комментарий

Ваш адрес email не будет опубликован.