Грамотно сделать одну функцию из трех

@Nikita53 · Регистрация: 27.03.2017

Студворк — интернет-сервис помощи студентам

Доброго времени суток! В экселе не силен, прошу помощи.
Требуется рассчитать заработную плату каждого сотрудника в процентном соотношении от суммы кассы: если в кассе от 0 до 500 тыс, до зп 10% от фактической суммы в кассе; от 500 001 до 600 000 то 12%; от 600 001 до 2 500 000 то 15%. Получились следующие формулы (на примере Дениса) в файле выделил красным:

Code
1
2
3
=СУММПРОИЗВ(($G$2:$G$1000="Денис")*(($S$4<500000)*10%)*$J$2:$J$1000)
=СУММПРОИЗВ(($G$2:$G$1000="Денис")*(($S4>500001)*($S4<600000)*12%)*$J$2:$J$1000)
=СУММПРОИЗВ(($G$2:$G$1000="Денис")*(($S4>600001)*($S4<3000000)*15%)*$J$2:$J$1000)

поодиночке все работает, но когда слепил вот такого крокодила:

Code
1
=СУММПРОИЗВ(((((($G$2:$G$1000="$S$1")*(($S$4>0)*($S$4<500000)*10%)*$J$2:$J$1000)+(($S$4>500001)*($S$4<600000)*12%)*$J$2:$J$1000)+(($S$4>600001)*($S$4<3000000)*15%)*$J$2:$J$1000)))

все стало криво считать
Бьюсь головой второй день((( заранее спасибо всем неравнодушным

PS. нужно учесть, что в дальнейшем все это залью в гугл таблицы

@Vlad999 · 28.03.2017, 10:47

если в ГУГЛ таблицах есть ВПР то пробуйте

Visual Basic
1
=СУММПРОИЗВ(ВПР(S4;{0;0,1:500001;0,12:600001;0,15};2;1)*($G$2:$G$1000="Денис")*$J$2:$J$1000)

@Nikita53 · 28.03.2017, 11:34 **[ТС]**

Спасибо за ответ!
впр есть, скопировал вашу формулу, в ячейке выдал это: #ССЫЛ!(функция vlookup возвращает значение, которое находится за пределами допустимого диапазона). Что бы сие значило??

@AlexM · 28.03.2017, 12:03

Сообщение от Nikita53

но когда слепил вот такого крокодила...все стало криво считать

Уберите кавычки "$S$1" и будет считать.
В вашей формуле если значение в $S4 будет равно 500000 или 600000, то формула вернет неверное значение. Правильнее так

Code
1
=СУММПРОИЗВ(((((($G$2:$G$1000=$S$1)*(($S$4>=0)*($S$4<500000)*10%)*$J$2:$J$1000)+(($S$4>=500000)*($S$4<600000)*12%)*$J$2:$J$1000)+(($S$4>=600000)*($S$4<3000000)*15%)*$J$2:$J$1000)))

Формула Vlad999 рабочая, если у вас получается ошибка, то покажите файл. Если в ней учесть 500000 и 600000 то формула получится

Code
1
=СУММПРОИЗВ(ВПР(S4;{0;0,1:500000;0,12:600000;0,15};2;1)*($G$2:$G$1000="Денис")*$J$2:$J$1000)

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

Code
1
=СУММПРОИЗВ(ВПР(S4%%;{0;10:50;12:60;15};2)%*($G$2:$G$1000="Денис")*$J$2:$J$1000)

@Nikita53 · 28.03.2017, 12:28 **[ТС]**

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

Добавлено через 1 минуту
Причем и мой "крокодил" работает в экселе корректно, после того, как по вашему совету убрал кавычки, а в гугле почему-то стал криво считать

@AlexM · 28.03.2017, 12:38

Формула Vlad999 для Google

Code
1
=SUMPRODUCT(VLOOKUP(S4;{0\0,1;500000\0,12;600000\0,15};2;1)*($G$2:$G$1000="Денис")*$J$2:$J$1000)

Добавлено через 6 минут
и сокращенная

Code
1
=SUMPRODUCT(VLOOKUP((S4%)%;{0\10;50\12;60\15};2)%*($G$2:$G$1000="Денис")*$J$2:$J$1000)

Файл

@Nikita53 · 28.03.2017, 13:01 **[ТС]**

Все работает!!! Счастью моему нет предела!))) Спасибо Вам огромное!))

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

@AlexM · 28.03.2017, 13:22

Думаю только с дополнительным столбцом, например М.
В М2 вставить формулу

Code
1
=ВПР(S$4;{0;0,1:500000;0,12:600000;0,15};2;1)*(G2="Денис")*J2

или сокращенную
и протянуть вниз по таблице. Ну а дальше ПРОМЕЖУТОЧНЫЕ.ИТОГИ()

Code
1
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;M2:M999)

@Nikita53 · 28.03.2017, 14:33 **[ТС]**

немного не понял, промежуточные итоги куда вставлять?

Добавлено через 4 минуты
все понял! но проблема та же) в эксель все работает как часы, в гугле опять ошибку выдает

@AlexM · 28.03.2017, 14:45

См.Файл

@Nikita53 · 28.03.2017, 14:54 **[ТС]**

или может как-нибудь приклеить

Code
1
2
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;M2:M999) к 
=SUMPRODUCT(VLOOKUP((S4%)%;{0\10;50\12;60\15};2)%*($G$2:$G$1000="Денис")*$J$2:$J$1000)

@AlexM · 28.03.2017, 14:55

Nikita53, Вы ссылку открыли?

@Nikita53 · 28.03.2017, 15:40 **[ТС]**

да, открыл, скопировал, все великолепно работает и считает! Сэкономили мне кучу времени и нервов!! Спасибо огромное!!!
мой вопрос был, теперь можно ли как-то приклеить

Code
1
2
 =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;M2:M999) к 
=SUMPRODUCT(VLOOKUP((S4%)%;{0\10;50\12;60\15};2)%*($G$2:$G$1000="Денис")*$J$2:$J$1000)

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

Добавлено через 12 минут
кстати, промежуточные итоги вставил в М1

@AlexM · 28.03.2017, 15:54

В сообщении №8 это решение и вы его уже используете. В М2

Code
1
=VLOOKUP((S4%)%;{0\10;50\12;60\15};2)%*(G2="Денис")*J2

@Nikita53 · 28.03.2017, 16:00 **[ТС]**

теперь все, допер!) еще раз спасибо огромное за уделенное время и действенную помощь! без вас не справился бы

@Nikita53 · 29.03.2017, 17:09 **[ТС]**

Добрый день! Если не заняты, позвольте спросить по вчерашнему вопросу. в ячейке S1 стоит формула для расчета зп по определенному проценту относительно сборов, в М1 тот же расчет, но с возможностью выбора фильтром по периоду. Вопрос: какой формулой можно реализовать обратный расчет, не (сумма сборов-зп), а наоборот (зп-сумма сборов), чтобы также соблюдались те же условия относительно % (например если зп 64800, то сборы сотрудника составили 540 000, соответственно это 12%). Пробовал через ВПР, но ничего не вышло, не допираю логику обратного рассчета. Вроде прописываю все условия, а выдает ту же сумму что и зп((
Заранее благодарен за ответ ! ! !

@AlexM · 29.03.2017, 17:18

Nikita53, Во вчерашнем и сегодняшнем файле в S1 нет формул.

@Nikita53 · 29.03.2017, 17:22 **[ТС]**

извините, не то прикрепил. вот в этом файле, в ячейке S5

@AlexM · 29.03.2017, 17:31

У вас в S4 уже есть нужные данные.

@Nikita53 · 29.03.2017, 17:44 **[ТС]**

Верно, есть, но эти данные не реагируют на фильтр. Т.е. если моя таблица растянется на год, S4 будет показывать общую сумму сборов за весь год, а не сумму с которой рассчиталась зп. Думал вывести эти данные в отдельную ячейку (например S6), чтобы было видно сразу все: S4-сумма сборов по сотруднику всего, S5- зп относительно отфильтрованного периода, S6- с какой суммы была рассчитана зп. Для меня в этом все сложность представляют проценты, что для расчета зп (с которым вы мне помогли вчера), что для рассчета суммы от которой рассчитывалась эта зп

Новые блоги и статьи Все статьи Все блоги /
SDL3 для Web (WebAssembly): Реализация движения на Box2D v3 - трение и коллизии с повёрнутыми стенами 8Observer8 20.02.2026 Содержание блога Box2D позволяет легко создать главного героя, который не проходит сквозь стены и перемещается с заданным трением о препятствия, которые можно располагать под углом, как верхнее. . .	Конвертировать закладки radiotray-ng в m3u-плейлист damix 19.02.2026 Это можно сделать скриптом для PowerShell. Использование . \СonvertRadiotrayToM3U. ps1 <path_to_bookmarks. json> Рядом с файлом bookmarks. json появится файл bookmarks. m3u с результатом. # Check if. . .	Семь CDC на одном интерфейсе: 5 U[S]ARTов, 1 CAN и 1 SSI Eddy_Em 18.02.2026 Постепенно допиливаю свою "многоинтерфейсную плату". Выглядит вот так: https:/ / www. cyberforum. ru/ blog_attachment. php?attachmentid=11617&stc=1&d=1771445347 Основана на STM32F303RBT6. На борту пять. . .	Камера Toupcam IUA500KMA Eddy_Em 12.02.2026 Т. к. у всяких "хикроботов" слишком уж мелкий пиксель, для подсмотра в ESPriF они вообще плохо годятся: уже 14 величину можно рассмотреть еле-еле лишь на экспозициях под 3 секунды (а то и больше),. . .
И ясному Солнцу zbw 12.02.2026 И ясному Солнцу, и светлой Луне. В мире покоя нет и люди не могут жить в тишине. А жить им немного лет.	«Знание-Сила» zbw 12.02.2026 «Знание-Сила» «Время-Деньги» «Деньги -Пуля»	SDL3 для Web (WebAssembly): Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 12.02.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами и вызывать обработчики событий столкновения. . . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 11.02.2026 Содержание блога Библиотека SDL3 содержит встроенные инструменты для базовой работы с изображениями - без использования библиотеки SDL3_image. Пошагово создадим проект для загрузки изображения. . .

@Nikita53 0 / 0 / 0 Регистрация: 27.03.2017 Сообщений: 15
	28.03.2017, 11:34 [ТС]
	Спасибо за ответ! впр есть, скопировал вашу формулу, в ячейке выдал это: #ССЫЛ!(функция vlookup возвращает значение, которое находится за пределами допустимого диапазона). Что бы сие значило?? 0

@Nikita53 0 / 0 / 0 Регистрация: 27.03.2017 Сообщений: 15
	28.03.2017, 12:28 [ТС]
	Прошу прощения. Обе формулы и ваша и Влада работают. Я просто сразу прописал их в гугл таблицах, минуя эксель. В экселе все работает, а в гугле нет((( Добавлено через 1 минуту Причем и мой "крокодил" работает в экселе корректно, после того, как по вашему совету убрал кавычки, а в гугле почему-то стал криво считать 0

@Nikita53 0 / 0 / 0 Регистрация: 27.03.2017 Сообщений: 15
	28.03.2017, 13:01 [ТС]
	Все работает!!! Счастью моему нет предела!))) Спасибо Вам огромное!)) Добавлено через 5 минут Еще вопрос, если не затруднит, можно ли как-то прописать формулой следующее: если я скрываю фильтром (в гугле) все даты кроме определенного периода (если таблица расписана на год, а я выбираю фильтром например 2 недели в мае), как прописать, чтобы все скрытые фильтром даты не учитывались в подсчете той функции, которую вы написали выше для подсчета зп? пробовал присоединить "промежуточные итоги" к "крокодилу" но получилась какая-то фигня откровенная( 0

@Nikita53 0 / 0 / 0 Регистрация: 27.03.2017 Сообщений: 15
	28.03.2017, 14:33 [ТС]
	немного не понял, промежуточные итоги куда вставлять? Добавлено через 4 минуты все понял! но проблема та же) в эксель все работает как часы, в гугле опять ошибку выдает 0

@AlexM 2904 / 1718 / 705 Регистрация: 04.09.2015 Сообщений: 3,445
	28.03.2017, 14:45
	См.Файл 1

@AlexM 2904 / 1718 / 705 Регистрация: 04.09.2015 Сообщений: 3,445
	28.03.2017, 14:55
	Nikita53, Вы ссылку открыли? 1

@Nikita53 0 / 0 / 0 Регистрация: 27.03.2017 Сообщений: 15
	28.03.2017, 16:00 [ТС]
	теперь все, допер!) еще раз спасибо огромное за уделенное время и действенную помощь! без вас не справился бы 0

@AlexM 2904 / 1718 / 705 Регистрация: 04.09.2015 Сообщений: 3,445
	29.03.2017, 17:18
	Nikita53, Во вчерашнем и сегодняшнем файле в S1 нет формул. 1

@AlexM 2904 / 1718 / 705 Регистрация: 04.09.2015 Сообщений: 3,445
	29.03.2017, 17:31
	У вас в S4 уже есть нужные данные. 1

@Nikita53 0 / 0 / 0 Регистрация: 27.03.2017 Сообщений: 15
	29.03.2017, 17:44 [ТС]
	Верно, есть, но эти данные не реагируют на фильтр. Т.е. если моя таблица растянется на год, S4 будет показывать общую сумму сборов за весь год, а не сумму с которой рассчиталась зп. Думал вывести эти данные в отдельную ячейку (например S6), чтобы было видно сразу все: S4-сумма сборов по сотруднику всего, S5- зп относительно отфильтрованного периода, S6- с какой суммы была рассчитана зп. Для меня в этом все сложность представляют проценты, что для расчета зп (с которым вы мне помогли вчера), что для рассчета суммы от которой рассчитывалась эта зп 0

Грамотно сделать одну функцию из трех

Решение