Откройте книгу. В таблице на листе «Отчёт по контрагентам»: С помощью функции СУММЕСЛИМН в ячейке В2 рассчитайте общую сумму договоров для первого контрагента (БИГБОКС), используя…
Откройте книгу. В таблице на листе «Отчёт по контрагентам»: С помощью функции СУММЕСЛИМН в ячейке В2 рассчитайте общую сумму договоров для первого контрагента (БИГБОКС), используя данные из листа «Исходные данные». В таблице с исходными данными указаны полные названия контрагентов, поэтому необходимо использовать подстановочный символ, чтобы просуммировать по кратким наименованиям, которые указаны в столбце А на листе «Отчёт по контрагентам». Задайте правильные типы ссылок для каждого аргумента функции СУММЕСЛИМН, чтобы скопировать формулу в остальные ячейки таблицы. Для числовых данных в столбце «Общая сумма договоров, руб.» установите формат с разделителем групп разрядов и без знаков после запятой. В таблице на листе «Отчёт по менеджерам»: С помощью функции СУММЕСЛИМН в ячейке В2 рассчитайте общую сумму сделок по первому менеджеру (Алиева Карина). С помощью функции СЧЁТЕСЛИМН в ячейке С2 рассчитайте общее количество сделок по первому менеджеру (Алиева Карина). С помощью функций ЕСЛИ и И в ячейке D2 рассчитайте премию по первому менеджеру (Алиева Карина), исходя из условия, что если сумма сделок не менее 11 500 000 рублей и количество сделок не менее 120, то премия составит 250 000 рублей, иначе — 100 000 рублей. Задайте правильные типы ссылок для каждого аргумента в функциях СУММЕСЛИМН, СЧЁТЕСЛИМН, ЕСЛИ и И, чтобы скопировать формулы в остальные ячейки таблицы. Для числовых данных установите формат с разделителем групп разрядов и без знаков после запятой.
Ниже подробное решение задачи с пошаговыми формулами и пояснениями. Формулы приведены для русской версии Excel. Я поясню, какие ссылки закреплять, чтобы можно было копировать формулы по таблицам.
- Таблица на листе «Отчёт по контрагентам»
Цель: в ячейке B2 посчитать общую сумму договоров для первого контрагента БИГБОКС, используя данные из листа «Исходные данные». Названия контрагентов во «Исходных данных» полные, поэтому применяем подстановочный знак к краткому названию из столбца A на текущем листе.
Формула в B2:
- =СУММЕСЛИМН('Исходные данные'!\(C:\)C; 'Исходные данные'!\(A:\)A; ""&$A2&"")
Пояснения к формулам и аргументам:
- sum_range: 'Исходные данные'!\(C:\)C — столбец, где записана общая сумма договоров. (Замените на фактический столбец в вашей таблице, если он отличается.)
- criteria_range1: 'Исходные данные'!\(A:\)A — столбец с полными названиями контрагентов.
- criteria1: ""&$A2&"" — подстановочный знак до и после краткого названия, взятого из текущей ячейки A2 на листе «Отчёт по контрагентам»; благодаря абсолютному закреплению столбца A ($A2) можно копировать формулу вниз по списку контрагентов.
- Ссылка на сумму и на критерий закреплена по столбцам (\(C:\)C и \(A:\)A), чтобы формула корректно копировалась на другие строки.
После ввода формулы протяните её вниз по колонке B, чтобы получить суммы по всем контрагентам.
Формат чисел:
- Для столбца «Общая сумма договоров, руб.» задайте формат: разделитель разрядов (тысячи) и 0 знаков после запятой. В Excel это делается через Формат ячеек → Число → Дробность: 0, поставить галочку "Использовать разделитель разрядов".
- Таблица на листе «Отчёт по менеджерам»
Цель:
- B2 — через SUMIFS посчитать общую сумму сделок по первому менеджеру Алиева Карина.
- C2 — через COUNTIFS посчитать общее количество сделок по Алиевой Карине.
- D2 — по условию начислить премию: если сумма сделок ≥ 11 500 000 и количество сделок ≥ 120, премия 250 000, иначе 100 000.
- Форматы чисел аналогично: разделитель разрядов и 0 знаков после запятой.
Формула в B2 (сумма по менеджеру):
- =СУММЕСЛИМН('Исходные данные'!\(G:\)G; 'Исходные данные'!\(F:\)F; $A2)
Пояснения:
- sum_range: 'Исходные данные'!\(G:\)G — столбец с общими суммами сделок.
- criteria_range1: 'Исходные данные'!\(F:\)F — столбец с именами менеджеров.
- criteria1: $A2 — имя менеджера на текущем листе (для первой строки это "Алиева Карина"); столбец F закреплён, а строка изменяется при копировании вниз благодаря относительной части ссылки.
Формула в C2 (количество сделок по менеджеру):
- =СЧЁТЕСЛИМН('Исходные данные'!\(F:\)F; $A2)
Пояснения:
- criteria_range1: 'Исходные данные'!\(F:\)F — тот же столбец с именами менеджеров.
- criteria1: $A2 — имя менеджера; при копировании вниз строка будет меняться (A3, A4, ...).
Формула в D2 (премия):
- =ЕСЛИ(И(B2>=11500000; C2>=120); 250000; 100000)
Пояснения:
- И(B2>=11500000; C2>=120) — оба условия должны выполняться.
- Если условия выполняются — премия 250000, иначе 100000.
Примечания по ссылкам:
- sum_range и criteria_range1 в формулах B2 и C2 закреплены по столбцам (\(G:\)G, \(F:\)F и аналогично), чтобы можно было копировать формулы в вниз по строкам.
- Имя менеджера в критерии — $A2, чтобы при копировании вниз оно подставлялось как A3, A4 и т. д. При необходимости можно зафиксировать столбец A: $A2 (то есть закрепить колонку A, строка изменяется).
Формат чисел:
- Для столбцов B, C и D на листе «Отчёт по менеджерам» также рекомендуется установить формат с разделителем групп разрядов и без знаков после запятой (0 десятичных). Делайте это через Формат ячеек → Число → Дробность: 0, включить разделитель тысяч.
Дополнительные примечания:
- Если хотите строгий матч без подстановочных символов для контрагента на первом листе (а не частичный по имени), можно заменить критерий ""&$A2&"" на просто $A2. В вашей задаче указано использовать подстановочный символ, поэтому использован вариант с подстановочным знаком.
- Если в ваших таблицах суммируется и используется другой столбец для общих сумм или имен менеджеров, просто поменяйте ссылки на соответствующие столбцы в формулах, сохранив логику: сумма в sum_range, критерий по имени в criteria_range и имя в виде $A2 (или $A$2, если хотите зафиксировать именно строку 2 для всех менеджеров).
Если хотите, могу адаптировать формулы под конкретные названия столбцов в вашем файле — пришлите схему таблиц (какие столбцы содержат что: полные названия контрагентов, краткие названия, сумма договоров, менеджеры и т. п.), и я дам точные формулы под них.
