Расчет надбавки в excel в зависимости от выполнения плана

Обновлено: 03.07.2024

KPI — это показатель эффективности, который позволяет объективно оценить эффективность рабочего процесса. Эта система используется для оценки различных показателей (деятельность в целом по компании, отдельные структуры, конкретные специалисты). Он выполняет не только контрольные функции, но и стимулирует родовую деятельность. Часто на основе KPI строится система оплаты труда. Это методика формирования переменной части заработной платы.

Ключевые показатели эффективности KPI: примеры в Excel

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

Каждое предприятие определяет ключевые показатели эффективности и вес каждого отдельного человека. Сроки зависят от задач компании. Например:

Цель - обеспечить план продаж продукции в размере 500 000 рублей ежемесячно. Ключевой показатель эффективности – это план продаж. Система измерения: есть фактическая сумма продаж/плановая сумма продаж.
Задача - увеличить объем отгрузок за период на 20%. Ключевой показатель – это средний объем отгрузки. Система измерения: фактическая средняя стоимость отгрузки / плановая средняя стоимость отгрузки.
Задача — увеличить количество клиентов на 15% в конкретном регионе. Ключевой показатель – это количество клиентов в базе данных компании. Система измерения: фактическое количество клиентов/запланированное количество клиентов.

Коэффициент вариации (шкалы) компания также определяет самостоятельно. Например:

Выполнение плана менее 80% - недопустимо.
Выполнение плана на 100% - коэффициент 0,45.
Выполнение плана 100 - 115% - коэффициент 0.005 за каждые 5%.
Отсутствие ошибок – коэффициент 0,15.
За отчетный период замечаний не было – коэффициент 0,15.

Это только вариант определения мотивационных факторов.

Ключевой момент в измерении KPI – соотношение фактических и плановых показателей. Почти всегда заработная плата работника состоит из оклада (постоянная часть) и премии (переменная часть/переменная часть). Мотивационный фактор влияет на формирование переменной части.

Предположим, что соотношение фиксированной и переменной частей заработной платы - 50 × 50. Ключевые показатели эффективности и вес каждого из них:

Примите следующие значения коэффициентов (одинаковые для индикатора 1 и индикатора 2):

Таблица ключевых показателей эффективности в Excel:

Зарплата – это постоянная часть заработной платы, зависящая от количества отработанных часов. Для удобства расчетов принято, что фиксированная и переменная части заработной платы равны.
Процент выполнения плана продаж и плана работ рассчитывается как отношение фактических показателей к плановым.
Для расчета премии используются коэффициенты. Формулы расчета KPI в Excel для каждого сотрудника. Выполнение продаж: Объем работ: Мы приняли, что влияние индекса 1 и индекса 2 на размер премии одинаково. Значения коэффициентов также равны. Поэтому для расчета индекса 1 и индекса 2 используются одни и те же формулы (меняются только ссылки на ячейки).
Формула расчета суммы премии к начислению: =C3*(F3+G3). Планируемая премия умножается на сумму индекса 1 и индекса 2 для каждого работника.
Зарплата - оклад + надбавка.

Это пример таблицы ключевых показателей эффективности в Excel. Каждая компания составляет свою таблицу (с учетом особенностей работы и системы премирования).

Матрица KPI и пример в Excel

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

Ключевые показатели – критерии оценки работы персонала. Они разные для каждой позиции.
Веса – числа в диапазоне от 0 до 1, общая сумма которых равна 1. Отражают приоритеты каждого ключевого показателя с учетом целей компании.
База – допустимое минимальное значение индекса. Следование базовому уровню – не дает никакого результата.
Норма – плановый уровень. Что то сотрудник должен выполнять обязательно. Ниже - сотрудник не справился со своими обязанностями.
Цель – это ценность, к которой нужно стремиться. Индикатор превышения, позволяющий улучшить результаты.
Факт – это фактические результаты.
Индекс KPI показывает уровень результатов по отношению к норме.

Расчет формулы KPI:

Показатель KPI = ((Факт – База) / (Норма – База)) * 100 %.

Как заполнить матрицу для офис-менеджера:

Коэффициент эффективности — это средний показатель KPI индекса: =СРЗНАЧ(G2:G6). Наглядно показана оценка сотрудника с помощью условного форматирования.

Вот на что обращают внимание компании, когда решают, сколько с вас брать

Джин Фолгер имеет более чем 15-летний опыт работы финансовым писателем, освещающим недвижимость, инвестиции, активную торговлю, экономику и пенсионное планирование. Она является соучредителем PowerZone Trading, компании, которая с 2004 года предоставляет услуги по программированию, консультированию и разработке стратегий активным трейдерам и инвесторам.

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

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

Ключевые выводы

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

Что такое страховая премия?

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

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

Сколько стоит страховая премия?

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

Вы будете платить больше за "лучшее" покрытие. Например, полис медицинского страхования с франшизой в размере 1000 долларов будет дороже, чем полис с франшизой в размере 5000 долларов. Точно так же полис автострахования с франшизой 0 долларов США будет дороже, чем полис с 500 долларов США, при прочих равных условиях.

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

Как рассчитать страховые взносы

Страховые компании учитывают несколько факторов при расчете страховых взносов:

Инвестопедия / Эллен Линднер

Ваш возраст.Страховые компании смотрят на ваш возраст, потому что это может предсказать вероятность того, что вам придется использовать страховку. Имея медицинскую страховку, молодые люди с меньшей вероятностью нуждаются в медицинской помощи, поэтому их страховые взносы, как правило, дешевле. Страховые взносы увеличиваются по мере того, как люди стареют, и у них больше шансов нуждаться в большем количестве медицинских услуг. А водители-подростки все еще работают над накоплением опыта, поэтому их автострахование стоит дороже. Точно так же пожилые водители, у которых, как правило, более медленные рефлексы, также будут платить больше.
Тип покрытия. В общем, у вас есть несколько вариантов, когда вы покупаете страховой полис. Чем более полное покрытие вы получите, тем дороже оно будет стоить. Например, если у вас есть полис автострахования, который покрывает только ответственность, он будет дешевле, чем если у вас есть план с страхованием на случай ДТП, комплексным страхованием, страхованием ответственности, медицинскими выплатами и незастрахованным или недостаточно застрахованным автомобилистом.
Объем покрытия. Чем меньше покрытие, тем дешевле страховые взносы — независимо от того, что вы страхуете. Например, если вы покупаете медицинскую страховку, вы будете платить более низкие страховые взносы за тот же тип страхового покрытия, если у вас более высокая франшиза и более высокий максимальный размер наличных средств. Точно так же застраховать дом за 400 000 долларов будет дороже, чем застраховать дом за 200 000 долларов.
Личная информация. В зависимости от типа страховки, которую вы покупаете, страховая компания может внимательно изучить такие вещи, как ваша история претензий, водительское стаж, кредитная история, пол, семейное положение, образ жизни, семейная история болезни, здоровье, статус курения, хобби, работа и место жительства.
Актуарные таблицы. В большинстве страховых компаний работают актуарии — профессионалы в области бизнеса, которые оценивают риск финансовых потерь, используя математику и статистику для прогнозирования вероятности страхового возмещения на основе большинства вышеупомянутых критериев. Обычно они составляют так называемую актуарную таблицу, которая предоставляется отделу андеррайтинга страховой компании, который использует входные данные для определения страховых взносов.

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

Как снизить свои премии

Страховые компании занимаются оценкой рисков. Чем выше риск, тем выше премии. Тем не менее, есть способы снизить страховые взносы.

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

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

Есть и другие способы сэкономить на страховых взносах, но они требуют больше усилий. Например, в большинстве штатов с курильщиков взимается до 50% больше, чем с некурящих за полисы медицинского страхования. Например, если вы курильщик и платите 600 долларов США в месяц за медицинскую страховку, вы можете уменьшить свой страховой взнос, скажем, до 400 долларов США, если бросите курить.

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

Что такое страховая премия?

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

Сколько стоят страховые взносы?

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

Означает ли более высокая страховая премия лучшую страховку?

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

Как я могу снизить свои страховые взносы?

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

Итог

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

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

Изображение: iStock/Андрей Попов

Windows: обязательно к прочтению

Если у вас когда-либо была работа, приносящая комиссионное вознаграждение, вы знаете, как сложно его рассчитать. Фиксированная комиссия выплачивается одинаково, независимо от общей суммы. Например, если ставка составляет 2%, вы получаете 2%, независимо от того, составляет ли ваша сумма 1000 долларов или 1 000 000 долларов. Многоуровневая комиссия (обычно) стимулирует продажи, потому что чем лучше вы работаете, тем выше процент.

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

Я использую Microsoft 365 в 64-разрядной системе Windows 10, но вы можете работать в более ранних версиях. Для вашего удобства вы можете скачать демонстрационный файл в формате .xlsx. Это решение совместимо с веб-версией.

Простейшая иерархия

Простейшая многоуровневая иерархия может и не быть настоящей многоуровневой системой с точки зрения бухгалтерского учета, но мы собираемся рассматривать ее именно так. Например, предположим, что первая ставка в размере 2% выплачивается на суммы до 50 000 долларов. Продажи на сумму более 50 000 долларов США получают 2,5%, а на общую сумму более 80 000 долларов США - 3%. На данном этапе важно отметить, что процент выплачивается по всем итогам после достижения контрольного показателя, поэтому некоторые могут не считать эту систему настоящей многоуровневой.

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

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

Суммы за месяц менее 50 000 долларов США умножаются на 0,02; 50 000 долл. США – это первая веха.
Суммы за месяц менее 80 000 долл. США умножаются на 0,025; 80 000 долл. США – это второй этап.
Суммы за месяц свыше 80 000 долларов США умножаются на 0,03; всего три уровня, третьего этапа нет.

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

Как создать многоуровневую таблицу

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

Как видно на рисунке А, я создал простую таблицу уровней, в которой отражены ставки из предыдущего раздела. (Лист на рисунке A специально сделан простым.) Примените бухгалтерский формат к F1:F3, а числовой формат к G1:G3. Возможно, вы захотите построить таблицу комиссионных на другом листе, но я показываю все это на одном листе, чтобы вы могли видеть решение и исходные данные вместе. Наш следующий шаг — добавить выражения, которые объединяют данные и таблицу уровней, чтобы возвращать ежемесячные комиссионные для всего персонала. Вот почему таблица уровней немного смещена.

Рисунок А

Как создать выражение для расчета комиссий

Если вы ищете решения с многоуровневой комиссией, вы можете почувствовать себя немного в тупике и захотите сдаться. В нашем случае решение намного проще, чем большинство из того, что вы найдете. Это не потому, что я такой блестящий; скорее, мы рассматриваем простую проблему.Проще говоря, нам нужно выражение, которое сравнивает месячный итог с ближайшим значением вехи в столбце F таблицы уровней и возвращает соответствующую скорость в столбце G. Это может сделать функция ВПР(). Затем нужно просто умножить эту ставку на месячную сумму.

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

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

Рисунок Б

Рисунок C

Давайте разберем выражение. Во-первых, функция ВПР() сравнивает месячную сумму в таблице продаж, 52 008 долларов США (в C7). Природа этой функции помогает нам, потому что ей не нужно находить точное совпадение, и она продолжает оценивать список до тех пор, пока не встретит значение, превышающее исходное значение. В данном случае это 80 000 долларов — 80 000 > 52 008. Аргумент 2 указывает функции перейти на 1 столбец от вех в столбце F и вернуть это значение, равное 0,025. Последняя часть выражения умножает 0,025 на 52 008 долларов США, чтобы получить 1 300,20 долларов США.

Теперь давайте представим его как выражение:

Как изменить комиссии в таблице Excel

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

Рисунок D

Оставайтесь с нами

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

Еженедельный информационный бюллетень Microsoft

Будьте инсайдером Microsoft в своей компании, прочитав эти советы, рекомендации и памятки по Windows и Office.

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

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

Функция ВПР Microsoft Excel проще в использовании, чем вы думаете. Более того, она невероятно мощная, и вам обязательно захочется иметь ее в своем арсенале аналитического оружия.

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

Как работает функция ВПР?

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

Формула всегда выполняет поиск вправо.

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

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

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

В чем секрет работы функции ВПР? Уникальные идентификаторы.

Уникальный идентификатор – это часть информации, которую используют оба ваших источника данных, и, как следует из названия, она уникальна (т. е. идентификатор связан только с одной записью в вашей базе данных). Уникальные идентификаторы включают коды продуктов, складские единицы (SKU) и контактные данные клиентов.

Хорошо, достаточно объяснений: давайте посмотрим еще один пример ВПР в действии!

Пример ВПР

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

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

Как использовать функцию ВПР в Excel

Укажите столбец ячеек, который вы хотите заполнить новыми данными.
Выберите «Функция» (Fx) > ВПР и вставьте эту формулу в выделенную ячейку.
Введите значение поиска, для которого вы хотите получить новые данные.
Введите массив таблиц электронной таблицы, в которой находятся нужные данные.
Введите номер столбца данных, которые должны возвращать Excel.
Введите поисковый диапазон, чтобы найти точное или приблизительное совпадение с вашим поисковым значением.
Нажмите "Готово" (или "Ввод") и заполните новый столбец.

Вот как выглядит функция ВПР:

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

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

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

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

2. Выберите «Функция» (Fx) > ВПР и вставьте эту формулу в выделенную ячейку.

Слева от текстовой панели над таблицей вы увидите небольшой значок функции, который выглядит как скрипт: Fx. Щелкните первую пустую ячейку под заголовком столбца, а затем щелкните значок этой функции. Справа от экрана появится поле под названием «Построитель формул» или «Вставить функцию» (в зависимости от используемой версии Excel).

Найдите и выберите "ВПР" из списка параметров, включенных в построитель формул. Затем выберите «ОК» или «Вставить функцию», чтобы начать создание ВПР. Ячейка, которую вы сейчас выделили в электронной таблице, теперь должна выглядеть так: "=VLOOKUP()"

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

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

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

Первый критерий — это значение для поиска. Это значение вашей электронной таблицы, с которой связаны данные, которые вы хотите, чтобы Excel нашел и вернул вам.Чтобы ввести его, щелкните ячейку, содержащую значение, для которого вы пытаетесь найти совпадение. В нашем примере, показанном выше, он находится в ячейке A2. Вы начнете переносить новые данные в D2, так как эта ячейка представляет MRR для имени клиента, указанного в ячейке A2.

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

4. Введите массив таблиц электронной таблицы, в которой находятся нужные данные.

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

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

Например, если ваши данные расположены на "Листе2" между ячейками C7 и L18, запись массива таблиц будет "Лист2!C7:L18".

5. Введите номер столбца данных, которые должны возвращать Excel.

Под полем массива таблиц введите «номер индекса столбца» массива таблиц, в котором выполняется поиск. Например, если вы фокусируетесь на столбцах от B до K (обозначается как «B:K» при вводе в поле «табличный массив»), но конкретные значения, которые вы хотите, находятся в столбце K, вы вводите «10» в поле "номер индекса столбца", так как столбец K является 10-м столбцом слева.

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

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

Чтобы ответить на ваш животрепещущий вопрос: да, вы можете разрешить Excel искать приблизительное совпадение вместо точного совпадения. Для этого просто введите TRUE вместо FALSE в четвертом поле, показанном выше.

Если функция ВПР настроена на приблизительное совпадение, она ищет данные, наиболее близкие к вашему поисковому значению, а не данные, идентичные этому значению. Если вы просматриваете данные, связанные, например, со списком ссылок на веб-сайты, и некоторые из ваших ссылок имеют в начале «https://», возможно, вам следует найти приблизительное совпадение на тот случай, если есть ссылки, которые не имеют этого тега «https://». Таким образом, остальная часть ссылки может совпасть без этого начального текстового тега, из-за чего ваша формула ВПР вернет ошибку, если Excel не сможет ее найти.

7. Нажмите "Готово" (или "Ввод") и заполните новый столбец.

Чтобы официально внести нужные значения в новый столбец из шага 1, нажмите "Готово" (или "Ввод", в зависимости от вашей версии Excel) после заполнения поля "Поиск диапазона". Это заполнит вашу первую ячейку. Вы можете воспользоваться этой возможностью, чтобы посмотреть в другой таблице, чтобы убедиться, что это правильное значение.

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

ВПР не работает?

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

Синтаксис (то есть, как вы структурировали формулу)
Значения (т. е. являются ли данные, которые он ищет, точными и правильно отформатированы)

Устранение неполадок с синтаксисом функции ВПР

Начните с просмотра формулы ВПР, которую вы написали в указанной ячейке.

Имеется ли в виду правильное значение поиска для идентификатора ключа?
Указывает ли он правильный диапазон массива таблиц для значений, которые необходимо получить?
Указывает ли он правильный лист для диапазона?
Правильно ли написан этот лист?
Используется ли правильный синтаксис для ссылки на лист? (например, Страницы!B:K или "Лист 1"!B:K)
Был ли указан правильный номер столбца? (например, A равно 1, B равно 2 и т. д.)
Правильный или неверный способ настройки таблицы?

Устранение неполадок со значениями ВПР

Отформатированы ли значения по вертикали и справа налево?
Соответствуют ли значения тому, как вы их называете?

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

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

ВПР как мощный маркетинговый инструмент

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

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

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