Компьютерный форум OSzone.net  

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)
-   Microsoft Office (Word, Excel, Outlook и т.д.) (http://forum.oszone.net/forumdisplay.php?f=115)
-   -   Обработка запроса с использованием формул (http://forum.oszone.net/showthread.php?t=321424)

victor21043 04-12-2016 14:47 2692783

Обработка запроса с использованием формул
 
Уважаемые пользователи портала,
Нужно:Посчитать продажи(сумму) в шт по каждому поставщику
У каждого поставщика есть несколько продаж.

На рабочем листе("Отчёт") предоставлена одна таблица, слева("умная").На основании неё, создана вспомогательная.

Решение задачи нужно "формулой-матрёшкой"( не пакетом анализа или макросом).
Желательно,но не обязательно,без использования вспомогательной таблицы.

Благодарю,
Фото https://cloud.mail.ru/public/6McA/MJ3pBGrKB
Книга1.xlsm https://cloud.mail.ru/public/4tPE/fyu5MFGYa

a_axe 04-12-2016 15:09 2692791

victor21043, на мой взгляд - задача для инструмента сводная таблица Excel 2010.
Если решить нужно именно формулами, можно например в G8 вбить следующую формулу:
=СУММЕСЛИ(Table[ПОСТАВЩИК];"=" &I8;Table[ПРОДАЖИ (ШТ)])
и протянуть ее ниже для остальных ячеек.

victor21043 04-12-2016 16:53 2692833

a_axe,Благодарю
Несколько уточнений:
-Раз мы использовали текстовый оператор(&,сжпробелы итд) в формуле,то результат у нас будет не число,а похожий на него текст(те надо ли добавлять бинарный оператор-ковертор =--СУММЕСЛИ( )?
-Структура критерия не понятна: "=" &$I8
Зачем нужно брать равно в кавычки(это же не текст,"равно") и ставить пробел,и потом производить конкатенацию*?
a1+a2& " " &"ластиков"
Благодарю,

a_axe 04-12-2016 17:34 2692857

Цитата:

Цитата victor21043
Раз мы использовали текстовый оператор(&,сжпробелы итд) в формуле,то результат у нас будет не число »

Нет, результатом вычислений суммесли будет число. Вбейте формулу в ячейку G8 и сможете убедиться, что результат - число. Причин для использования оператора -- вероятно нет.
Цитата:

Цитата victor21043
-Структура критерия не понятна: "=" &$I8 »

Если быть точным - "=" &I8, это текст с логическим условием, при условии истинности которого к сумме будет прибавляться значение каждой из ячеек. Для строчки 8 значение критерия после вычисления станет равно "=621", функция сложит значения тех строчек, поставщик которых - 621. Поменяете в I8 поставщика - формула все пересчитает.
Цитата из справки (справедливо только для "критерия" функции "суммесли"):
Цитата:

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

Цитата victor21043
Зачем нужно брать равно в кавычки(это же не текст,"равно") и ставить пробел,и потом производить конкатенацию*?[/post]

Таков синтаксис функции суммесли, критерий должен быть заключен в кавычки. Чтобы не вписывать для каждой ячейки критерий вручную ( вбить в формулу "=621"), задача по формированию текста критерия поставлена Excel.
Тут глубокий смысл: что вбито в формулу вы не видите (пока специально не посмотрите), поэтому наличие ошибки распознать тяжело. А если поставщиков будет 300 штук, вы еще и умахаетесь их в формулы вбивать, и вероятность ввода формулы с ошибкой очень сильно возрастает. А когда перечень поставщиков поменяется, предстоит работа по их поиску и корректировке. Все трудоемкие задачи должны возлагаться на Excel, а не на пользователя.
Так вот сводная таблица в принципе лишена этих недостатков: она сама выберет поставщиков и выполнит суммирование (а может еще больше). Вот представьте, что у вас 296 поставщиков, а записей в таблице - 8324 штуки. Как Вы будете из 8324 записей вытаскивать эти 296 поставщиков - вручную? Сводная таблица сделает это за Вас, и на оформление уйдет около минуты.
Цитата:

Цитата victor21043
a1+a2& " " &"ластиков" »

И какой на Ваш взгляд должен получится результат у приведенной формулы? Кусок & " " &"ластиков" лучше записать & " ластиков", это абсолютно одно и то же. Формула, я полагаю имеется в виду =(a1+a2) & " ластиков", и работать она будет для двух ячеек. Если ячеек будет 1000 шт, использовать ее затруднительно.

Iska 04-12-2016 19:08 2692885

Командирским тоном: а кто сильно умный — будет таскать чугуний кого не устраивает «неправильный» Excel — будет учить правильный Access. :)

bredych 05-12-2016 16:33 2693196

Цитата:

Цитата victor21043
Нужно:Посчитать продажи(сумму) в шт по каждому поставщику
У каждого поставщика есть несколько продаж. »

если условие одно - sumif. Если два - можно составную формулу с 2 циклами sumif, но проще использовать синтаксис с sumifs (добавлена начиная с офиса 2007) - там условий можно сколкьо угодно.
Если нужна совместимость со старыми офисами (напр, хр, 2003) - можно заюзать формулу с sumproduct - я так делал в пору работы по аудиторской части...


Время: 06:07.

Время: 06:07.
© OSzone.net 2001-