Банальная задача из 3-х частей: подбор знач-я и суммиров-е по 2 критериям, дубли

Автор bmg33, 12 августа 2016, 23:26

0 Пользователи и 1 гость просматривают эту тему.

bmg33

Цитата: rami от 13 августа 2016, 19:08
Первый параметр — условие (текст или результат в виде текста), второй — массив поиска (один столбец или одна строка), результат — номер позиции найденного. Условие не может быть двусмысленным (найди или шляпу или тапочки)
Вы меня неправильно поняли или я неправильно пояснил.
Функция MATCH в Excel может объединять в критерии данные из двух ячеек, а точно также поступать с массивами. Поэтому, когда есть два критерия для сравнения, в Excel очень просто вписать этих через &. Меня к этому привели уже сегодняшние изыскания.
К сожалению Calc так делает только частично, а именно - слепить критерий из двух ячеек через & может, а сделать то же с массивами - видимо нет, во всяком случае у меня это не сработало, а какой-то информации по этому поводу в интернете я не нашел.
Условие не может быть двусмысленным, но состоять из двух критериев соответствующих массиву - да. Посмотрите мой файл, он пояснит то, что я пытаюсь сделать лучше, чем я словами :)

kompilainenn

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут



kompilainenn

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

celler

Если по двум критериям, то может так?
Конечно, если там будут многие тысячи записей, то это будет всё тормозить.

rami

Цитата: celler от 14 августа 2016, 15:07
Если по двум критериям, то может так?
Конечно, если там будут многие тысячи записей, то это будет всё тормозить.
bmg33 сказал:
Цитата: bmg33 от 12 августа 2016, 21:26Сама таблица сделана просто для примера, задачи оригинала отличаются и нужны для больничной бухгалтерии
Мы не знаем, что будет в реальном документе, но в данном примере суммирование по двум критериям бессмысленно, разве только для выявления ошибок ввода данных. Но в целом так суммировать нормально.

bmg33

Всем спасибо большое за помощь.

rami, в файле примера действительно нет необходимости использовать 2 критерия, там второй критерий уникален сам по себе. Но это просто файл примера, в действительности второй критерий может быть не уникальным - там содержатся тысячи фамилий и очень-очень много дублей.
Я все-таки нашел как можно решить вопрос в интернете, просто сделал вспомогательный столбец, где объединил первый_критерий&второй_критерий диапазона в котором производится поиск, а в формуле точно также объединил через два критерия, которые ищу. Реализовал через index и match.

За алфавитный указатель фамилий еще одно большое спасибо :) я бы наверное никогда не догадался так сделать :)

celler, спасибо за интересный вариант. Я пока не понял как это работает :) но разберусь.

rami

Цитата: bmg33 от 15 августа 2016, 08:41Я все-таки нашел как можно решить вопрос в интернете, просто сделал вспомогательный столбец, где объединил первый_критерий&второй_критерий диапазона в котором производится поиск, а в формуле точно также объединил через два критерия, которые ищу.
celler сделал то же самое раньше.

Но лучше воспользоваться формулой (в ячейке F4):=SUMPRODUCT($'Цена закупки'.$A$3:$A$15=D4,$'Цена закупки'.$B$3:$B$15=E4,$'Цена закупки'.$C$3:$C$15)Без дополнительных колонок и объединений, да и условий может быть сколько угодно.
Цитата: bmg33 от 15 августа 2016, 08:41celler, спасибо за интересный вариант. Я пока не понял как это работает  но разберусь.
Не, не разберётесь, celler хорошо спрятал два вспомогательных столбца ;D
Цитата: bmg33 от 15 августа 2016, 08:41... там содержатся тысячи фамилий и очень-очень много дублей
На больших объёмах данных лучше избегать лишние формулы, форматы, вспомогательные столбцы и др. так как будет люто тормозить. Для таких задач нужно пользоваться Base.

ArsKam

Цитата: rami от 13 августа 2016, 19:08Условие не может быть двусмысленным (найди или шляпу или тапочки)
Здравствуйте.
Я запутался, т. е. невозможно по 2м и более критериям?
Забыл написать, что без дополнительных столбцов

eeigor

Цитата: ArsKam от 20 ноября 2020, 16:18Я запутался, т. е. невозможно по 2м и более критериям?
Вникать в содержание ветки нет времени.
Отвечу на поставленный вопрос. Всё возможно, количество критериев не ограничено. Принцип следующий: вы пишите формулу, где каждое сравнение - это результат действия какой-то операции (ищем, сравниваем, проверяем и т. п.), и этот результат - логический параметр (ИСТИНА/ЛОЖЬ). Далее, если критерии надо соединить оператором "ИЛИ", то логические параметры складываем (1+0+1=2  - сработало два критерия (достаточно и одного), результат больше 0, т. е. ИСТИНА), а если критерии надо соединить оператором "И", то логические параметры умножаем (1*0*1=0  - не сработал один критерий (а надо, чтобы сработали все), результат равен 0, т. е. ЛОЖЬ).
Пишите формулы, складывайте, умножайте... Пользуйтесь!
Для этих целей, когда все критерии должны выполняться, чаще всего используют формулу СУММПРОИЗВ(), которая "заточена" под операции с массивами (при вводе не требует фигурных скобок {=СУММПРОИЗВ(...)}). Здесь каждый массив надо отделить разделителем - точкой с запятой, и, если я не ошибаюсь, каждый логический результат надо ещё и преобразовать к числовому значению, и лучший вариант для этого - поставить знак минус перед скобками, но так, чтобы общее количество минусов было чётным, чтобы не изменился знак итогового результата (если массив один, то поставить надо два минуса для той же цели).
=СУММПРОИЗВ(-(...);-(...))  - эта формула сама будет перемножать массивы (как следует из её названия).

Может быть непонятно, ибо СУММПРОИЗВ() выполняет операции с массивами. Это отдельная тема. Ваша квалификация неизвестна. Но на вопрос я ответил.

UPD: Поправляюсь: в формуле СУММПРОИЗВ() можно все массивы-критерии связать через знаки сложения/умножения, не используя разделитель аргументов. То есть всё выражение с массивами ввести на место первого аргумента, и тогда преобразование логического результата к числовому не потребуется.

Вместо СУММПРОИЗВ() можно использовать формулу СУММЕСЛИМН().
Также вы можете использовать формулы для работы с базой данных, например, БДСУММ(), но для её использования надо организовать диапазон критериев. То есть в формулу вводятся не критерии, а ссылка на диапазон с критериями. Критерии в одной строке соединяются оператором "И", а критерии в разных строках - оператором "ИЛИ".
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

economist

Для решений "чисто на формулах" можно в 100 раз увеличить число помощников и готовых ответов, если написать русские формулы (LO Calc их поддерживает) и спросить/поискать  на форуме экселистов типа https://www.planetaexcel.ru/ или http://forum.ru-board.com
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

eeigor

ArsKam, не совсем понятно, в чём вы запутались.

И лучшей книгой по этой теме для меня была книга Дж.Уокенбаха "Формулы Excel".
Но есть ещё лучше (на английском языке):
"Ctrl+Shift+Enter: Mastering Excel Array Formulas" by Mike Girvin, 2013

Excel уже работает с динамическими формулами массива, но для LO эта книга ещё долго будет актуальна.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

kompilainenn

Цитата: eeigor от 20 ноября 2020, 17:25Excel уже работает с динамическими формулами массива
только 365 офис и то не у всех
Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

eeigor

Добавлен пример с формулой массива для возвращения списка уникальных значений ФИО без необходимости вести дополнительное поле с уникальным идентификатором поставщика.
Это мегаформула, и её не обязательно понимать до конца.

Но лучше создать отдельный словарь "Поставщики", где они не будут повторяться. И тогда эта формула отпадёт за ненадобностью. Организуйте правильно данные. Создайте необходимые словари, ну, хотя бы один - для поставщиков.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community