Страницы: 1
RSS
вопрос про поиск некскольких страницах (столбцах)
 
Здравствуйте. Мне нужен совет форумчан.
У меня вот такие таблицы (все на разных страницах)

столбцы "A" и "F" это не уникальный идентификатор заказа (т.е. он может повторяться)
столбец "B" и "H" это кол-во заказы
столбец "C" и "G" это производимя единица, что важно, имеет в своем составе компоненты (болты, гайки и тп)

первое,
по дате (2022-08-31) мне нужно найти все совпадающией значение в красной таблице стобец ("A" и "С") и, используя их, найти кол-во в синей таблице столбец "H"  
т.е. ответ должен быть 86 (80 стульев, 5 кроватей и 1 шкаф)
Результат должен выводиться в ячейке "E2"

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

Это можно реализовать без VBA ?
Спасибо
Изменено: Sergio P - 11.08.2022 12:24:11
 
Держите решение по первой задаче.
Код
=SUMPRODUCT(COUNTIFS($C$2:$C$7;$G$2:$G$6;$A$2:$A$7;$F$2:$F$6;$D$2:$D$7;$E$1)*$H$2:$H$6)

По второй, сделал как понял.
Код
=$E$2*SUMPRODUCT(SUMIFS(INDEX($H$10:$J$12;;MATCH(D10;$H$9:$J$9;));$G$10:$G$12;$G$10:$G$12))
Изменено: memo - 12.08.2022 08:57:13
 
Добрый день. Спасибо за вариант.
второе пока не смотрел, так как хотел с первым разобраться.

Если мне нужно вставить "не равно" и перечислить некоторые значения, которые не должны участвовать в калькуляции в столбец "С", я могу сделать вот так вот:
Цитата
=SUMPRODUCT(COUNTIFS($C$2:$C$7,$G$2:$G$6,$C$2:$C$7,<>"Кровать",$A$2:$A$7,$F$2:$F$6,$D$2:$D$7,$E$1)*$H$2:$H$6)
Если нет, то я могу, на худой конец, сделать это в Access
Спасибо
Изменено: Sergio P - 12.08.2022 23:43:57
 
Можно так:
Код
=SUMPRODUCT(COUNTIFS($C$2:$C$7;$G$2:$G$6;$C$2:$C$7;"<>Кровать";$A$2:$A$7;$F$2:$F$6;$D$2:$D$7;$E$1)*$H$2:$H$6)
 
Спасибо. Я посмотрел, первый вариант работает только тогда, когда нет одинаковых значений (в A, C и F, G). Если они есть, он начинает их перемножать.

Наверное тут нельзя ничего сделать?

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

По первому проверил и нашел комбинированное решение. Спасибо

По второму ответ не правильный (см. приложение). Нужно перемножать не на полученное число, а на кол-во каждого товара в отдельности. (т.е. используя предыдущие данные). Только тут должна быть привязана дата. Возможно можно сделать массивами?  
 
Если поменять местами стул, кровать и шкаф в серой таблице то можно так:
Код
=SUM(SUMIFS(INDEX($I$10:$K$12;;MATCH(D10;$I$9:$K$9;));$H$10:$H$12;$H$10:$H$12)*LARGE(IFERROR(1/(1/(COUNTIFS($C$2:$C$7;$G$2:$G$6;$A$2:$A$7;$F$2:$F$6;$D$2:$D$7;$E$1)*$H$2:$H$6));"");ROW($H$10:$H$12)-ROW($H$10)+1))

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

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

К тому же отсутствуют проверки:

1) в серой таблице указана спецификация. если заменить слово "Шкаф" на "Шина", то ничего не происходит с вычислениями. А должна быть проверка по этим словам.

2) если в серой таблице поменять местами спецификацию "Шкафа" со спецификацией "Кровати", то подсчет будет не правильный. Выходит, тут Название артикула не имеет никакого значения.

3) Если из А5 удалить или заменить значение D100, то перестает считать вообще, а должно продолжать считать оставшиеся совпадения (т.е. A2 и A3)

Тоже самое если удалить/заменить что-то в ячейках C2:H7

Думаете, что это нельзя поправить?

P.S.

С данными у меня все гораздо сложнее, просто я вынес данные с трёх таблиц в одну, для удобства

 
Sergio P,
попробуйте добавить уникальный список из Ваших таблиц. См.вложение (работает только для 365 офиса)

Я все же думаю, что в Вашем случае, лучше рассмотреть решение на VBA
Изменено: evgeniygeo - 02.09.2022 06:48:32
 
Цитата
Sergio P написал:
1) в серой таблице указана спецификация. если заменить слово "Шкаф" на "Шина", то ничего не происходит с вычислениями. А должна быть проверка по этим словам.
Конечно, ведь расчеты ведутся перемножением соответствий по словарю, (где из синей и красной таблиц словарем служит красная таблица) на массив значений из серой таблицы, которая. как я понимал стоит вообще особняком и никаких проверок там и должно было быть. А теперь выходит, что названия из нее должны быть вторым словарем для первых двух... Мой Вам совет - пересмотрите структуру. Я просто не представляю, как при таком иезуитском способе организации данных можно в дальнейшем избежать ошибок, даже если удастся создать формулу со всеми проверками и учетом всех нюансов.
 

Спасибо за совет. 365 у меня и стоит.

См. приложение. Я привел данные в порядок

Я не знаю, таблицы, что подгружаются из Access сами по себе уникальные или нет?

Я так понял, что Вы предлагаете для 2ю задачи использовать промежуточную таблицу для определение местоположения (колонка “М”), что отличное решение, но остаются вопросы:

  1. Если поменять данные местами в серой таблице сумма меняется
  2. Я вынес специфакацию на отдельных лист см «спецификация», и создал умную таблицу.

Как я говорил, я подгружаю данные с access через «данные>обновить все данные» и последовательность может поменяться.

 
Наверное, больше вариантов никто не предложит?
Страницы: 1
Наверх