Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Поиск остатка в таблице - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск остатка в таблице (Формулы/Formulas)
Поиск остатка в таблице
Stormy Дата: Понедельник, 09.05.2016, 15:53 | Сообщение № 1
Группа: Проверенные
Ранг: Обитатель
Сообщений: 357
Репутация: 12 ±
Замечаний: 0% ±

Excel 2010
Доброго времени суток!
Всех с Днем Победы!
Возникла такая проблема, не могу понять как ее решить ((
Есть таблица ввода ( фрагмент в приложенном файле на Лист 4) Назову ее Вводная .
Данная таблица используется для ввода накладных.
Есть таблица с данными ( фрагмент в приложенном файле на Лист 5) назову ее Результат
При заполнение таблицы Вводная в каждой строчке указываю для какого подразделения (цеха) используется данный товар.
Необходимо брать последний остаток для указанного цеха из таблицы Результат или же использовать остаток сформированной выше строки ( для данного цеха)
Постарался формулами и выделением показать окончательный результат.
Спасибо за уделенное время.
К сообщению приложен файл: 1152424.xlsx (15.6 Kb)

Место для рекламы.

Сообщение отредактировал Stormy - Понедельник, 09.05.2016, 16:09
 
Ответить
СообщениеДоброго времени суток!
Всех с Днем Победы!
Возникла такая проблема, не могу понять как ее решить ((
Есть таблица ввода ( фрагмент в приложенном файле на Лист 4) Назову ее Вводная .
Данная таблица используется для ввода накладных.
Есть таблица с данными ( фрагмент в приложенном файле на Лист 5) назову ее Результат
При заполнение таблицы Вводная в каждой строчке указываю для какого подразделения (цеха) используется данный товар.
Необходимо брать последний остаток для указанного цеха из таблицы Результат или же использовать остаток сформированной выше строки ( для данного цеха)
Постарался формулами и выделением показать окончательный результат.
Спасибо за уделенное время.
Автор - Stormy
Дата добавления - 09.05.2016 в 15:53
pabchek Дата: Понедельник, 09.05.2016, 16:06 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте! Вот, если правильно понял. Формула массива
Код
=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(K$6:K6;ПОИСКПОЗ(B7&C7;B$6:B6&C$6:C6;0));ИНДЕКС(Лист5!Q$3:Q13;ПОИСКПОЗ(B7&C7;Лист5!I$3:I13&Лист5!C$3:C13;0)));0)-J7
К сообщению приложен файл: 0242418.xlsx (15.5 Kb)

"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
СообщениеЗдравствуйте! Вот, если правильно понял. Формула массива
Код
=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(K$6:K6;ПОИСКПОЗ(B7&C7;B$6:B6&C$6:C6;0));ИНДЕКС(Лист5!Q$3:Q13;ПОИСКПОЗ(B7&C7;Лист5!I$3:I13&Лист5!C$3:C13;0)));0)-J7
Автор - pabchek
Дата добавления - 09.05.2016 в 16:06
Stormy Дата: Понедельник, 09.05.2016, 16:13 | Сообщение № 3
Группа: Проверенные
Ранг: Обитатель
Сообщений: 357
Репутация: 12 ±
Замечаний: 0% ±

Excel 2010
pabchek,
С праздником!
Добавил одну строчку в таблицу Вводная (Лист 4) указал цех Реклама выдало неправильный результат.
Должно было получится 764 , а получилось -6
К сообщению приложен файл: 9104370.jpg (29.1 Kb)

Место для рекламы.

Сообщение отредактировал Stormy - Понедельник, 09.05.2016, 16:14
 
Ответить
Сообщениеpabchek,
С праздником!
Добавил одну строчку в таблицу Вводная (Лист 4) указал цех Реклама выдало неправильный результат.
Должно было получится 764 , а получилось -6
Автор - Stormy
Дата добавления - 09.05.2016 в 16:13
buchlotnik Дата: Понедельник, 09.05.2016, 16:23 | Сообщение № 4
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
Stormy, "по фотографии" - Виктор предложил формулу массива (ctrl+shift+enter).
 
Ответить
СообщениеStormy, "по фотографии" - Виктор предложил формулу массива (ctrl+shift+enter).
Автор - buchlotnik
Дата добавления - 09.05.2016 в 16:23
pabchek Дата: Понедельник, 09.05.2016, 16:30 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
И кроме того, моя формула неверна, поскольку дает первое совпадающее значение, а нужно последнее. Вот исправился. Простая (не массивная) формула
Код
=ЕСЛИОШИБКА(ИНДЕКС(K$6:K6;ПРОСМОТР(;-1/(C$6:C6=C7)*(B$6:B6=B7);СТРОКА(A$1:A1)));ИНДЕКС(Лист5!Q$4:Q$13;ПРОСМОТР(;-1/(Лист5!C$4:C$13=C7)*(Лист5!I$4:I$13=B7);СТРОКА(Лист5!A$1:A$10))))-J7
К сообщению приложен файл: 9490794.xlsx (15.4 Kb)

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Понедельник, 09.05.2016, 16:31
 
Ответить
СообщениеИ кроме того, моя формула неверна, поскольку дает первое совпадающее значение, а нужно последнее. Вот исправился. Простая (не массивная) формула
Код
=ЕСЛИОШИБКА(ИНДЕКС(K$6:K6;ПРОСМОТР(;-1/(C$6:C6=C7)*(B$6:B6=B7);СТРОКА(A$1:A1)));ИНДЕКС(Лист5!Q$4:Q$13;ПРОСМОТР(;-1/(Лист5!C$4:C$13=C7)*(Лист5!I$4:I$13=B7);СТРОКА(Лист5!A$1:A$10))))-J7
Автор - pabchek
Дата добавления - 09.05.2016 в 16:30
Stormy Дата: Понедельник, 09.05.2016, 16:38 | Сообщение № 6
Группа: Проверенные
Ранг: Обитатель
Сообщений: 357
Репутация: 12 ±
Замечаний: 0% ±

Excel 2010
pabchek,
Попробуйте дописать еще одну строчку в Лист 4 , в моем случае должен произойти минус от остатка, Ваш пример неверные данные выдает

Место для рекламы.
 
Ответить
Сообщениеpabchek,
Попробуйте дописать еще одну строчку в Лист 4 , в моем случае должен произойти минус от остатка, Ваш пример неверные данные выдает
Автор - Stormy
Дата добавления - 09.05.2016 в 16:38
Stormy Дата: Понедельник, 09.05.2016, 16:41 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 357
Репутация: 12 ±
Замечаний: 0% ±

Excel 2010
Логика подсказывает, а ум отказывается понимать ((
Нужно найти последнее значение для указанного в столбце В цеха, в таблице Лист 4 и отнимать расход от него.
Если же для этого цеха в таблице Лист 4 это первая строчка, то взять последней остаток из таблицы Лист 5

Место для рекламы.
 
Ответить
СообщениеЛогика подсказывает, а ум отказывается понимать ((
Нужно найти последнее значение для указанного в столбце В цеха, в таблице Лист 4 и отнимать расход от него.
Если же для этого цеха в таблице Лист 4 это первая строчка, то взять последней остаток из таблицы Лист 5
Автор - Stormy
Дата добавления - 09.05.2016 в 16:41
pabchek Дата: Понедельник, 09.05.2016, 16:41 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Цитата Stormy, 09.05.2016 в 16:38, в сообщении № 6
неверные данные

да, скобки не поставил
Код
=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(K$6:K6;ПРОСМОТР(;-1/((C$6:C6=C7)*(B$6:B6=B7));СТРОКА(A$1:A1)));ИНДЕКС(Лист5!Q$4:Q$13;ПРОСМОТР(;-1/((Лист5!C$4:C$13=C7)*(Лист5!I$4:I$13=B7));СТРОКА(Лист5!A$1:A$10))));0)-J7
К сообщению приложен файл: 3598107.xlsx (15.5 Kb)

"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
Сообщение
Цитата Stormy, 09.05.2016 в 16:38, в сообщении № 6
неверные данные

да, скобки не поставил
Код
=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(K$6:K6;ПРОСМОТР(;-1/((C$6:C6=C7)*(B$6:B6=B7));СТРОКА(A$1:A1)));ИНДЕКС(Лист5!Q$4:Q$13;ПРОСМОТР(;-1/((Лист5!C$4:C$13=C7)*(Лист5!I$4:I$13=B7));СТРОКА(Лист5!A$1:A$10))));0)-J7
Автор - pabchek
Дата добавления - 09.05.2016 в 16:41
Stormy Дата: Понедельник, 09.05.2016, 16:46 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 357
Репутация: 12 ±
Замечаний: 0% ±

Excel 2010
pabchek,
Посмотрите, последний остаток в таблице Лист 4 для цеха Реклама был 770 . Потом добавили строчку с расходом 6 , должно получится 764 , а в Вашем примере ( у меня ) выдает результат -6

Место для рекламы.
 
Ответить
Сообщениеpabchek,
Посмотрите, последний остаток в таблице Лист 4 для цеха Реклама был 770 . Потом добавили строчку с расходом 6 , должно получится 764 , а в Вашем примере ( у меня ) выдает результат -6
Автор - Stormy
Дата добавления - 09.05.2016 в 16:46
pabchek Дата: Понедельник, 09.05.2016, 17:10 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Вместо Товар 8 поставьте Товар 2

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Понедельник, 09.05.2016, 17:12
 
Ответить
СообщениеВместо Товар 8 поставьте Товар 2
Автор - pabchek
Дата добавления - 09.05.2016 в 17:10
Pelena Дата: Понедельник, 09.05.2016, 17:12 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 19176
Репутация: 4413 ±
Замечаний: ±

Excel 365 & Mac Excel
Ещё вариант
Код
=ЕСЛИ(B7="";"";ЕСЛИОШИБКА(ПРОСМОТР(2;1/($B$6:B6=B7);$K$6:K6);ПРОСМОТР(2;1/(Лист5!$I$4:$I$13=B7);Лист5!$Q$4:$Q$13))-J7)
К сообщению приложен файл: 1777689.xlsx (15.9 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЕщё вариант
Код
=ЕСЛИ(B7="";"";ЕСЛИОШИБКА(ПРОСМОТР(2;1/($B$6:B6=B7);$K$6:K6);ПРОСМОТР(2;1/(Лист5!$I$4:$I$13=B7);Лист5!$Q$4:$Q$13))-J7)
Автор - Pelena
Дата добавления - 09.05.2016 в 17:12
_Boroda_ Дата: Понедельник, 09.05.2016, 17:53 | Сообщение № 12
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
И еще вариант
Код
=ПРОСМОТР(;-1/(Лист5!I$4:I$99=B7);Лист5!Q$4:Q$99)-СУММЕСЛИ(B$7:B7;B7;J$7:J7)
К сообщению приложен файл: 1152424_1.xlsx (15.8 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеИ еще вариант
Код
=ПРОСМОТР(;-1/(Лист5!I$4:I$99=B7);Лист5!Q$4:Q$99)-СУММЕСЛИ(B$7:B7;B7;J$7:J7)
Автор - _Boroda_
Дата добавления - 09.05.2016 в 17:53
pabchek Дата: Понедельник, 09.05.2016, 23:54 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Поспекулировал на формуле Александра _Boroda_, и все же мне кажется, что соответствие должно быть и по цеху и по товару))
Код
=ЕСЛИОШИБКА(ПРОСМОТР(;-1/((Лист5!C$4:C$13=C7)*(Лист5!I$4:I$13=B7));Лист5!Q$4:Q$13)-СУММЕСЛИМН(J$7:J7;B$7:B7;B7;C$7:C7;C7);"нет в результатах")
К сообщению приложен файл: 6007525.xlsx (15.5 Kb)

"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
СообщениеПоспекулировал на формуле Александра _Boroda_, и все же мне кажется, что соответствие должно быть и по цеху и по товару))
Код
=ЕСЛИОШИБКА(ПРОСМОТР(;-1/((Лист5!C$4:C$13=C7)*(Лист5!I$4:I$13=B7));Лист5!Q$4:Q$13)-СУММЕСЛИМН(J$7:J7;B$7:B7;B7;C$7:C7;C7);"нет в результатах")
Автор - pabchek
Дата добавления - 09.05.2016 в 23:54
Stormy Дата: Вторник, 10.05.2016, 06:50 | Сообщение № 14
Группа: Проверенные
Ранг: Обитатель
Сообщений: 357
Репутация: 12 ±
Замечаний: 0% ±

Excel 2010
Pelena, _Boroda_, pabchek,
Спасибо, за примеры и уделенное время.

Место для рекламы.
 
Ответить
СообщениеPelena, _Boroda_, pabchek,
Спасибо, за примеры и уделенное время.
Автор - Stormy
Дата добавления - 10.05.2016 в 06:50
Stormy Дата: Среда, 11.05.2016, 16:20 | Сообщение № 15
Группа: Проверенные
Ранг: Обитатель
Сообщений: 357
Репутация: 12 ±
Замечаний: 0% ±

Excel 2010
Добрый день!
Все получилось и работает именно так как нужно. Еще раз спасибо.
Теперь возникла иная задача.
Как просуммировать остатки для всех цехов? То есть сложить последний остаток Рекламы, Мебели, Метал. .
[moder]Не безобразничайте!
Это другой вопрос, следовательно, другая тема.

Место для рекламы.

Сообщение отредактировал _Boroda_ - Среда, 11.05.2016, 16:44
 
Ответить
СообщениеДобрый день!
Все получилось и работает именно так как нужно. Еще раз спасибо.
Теперь возникла иная задача.
Как просуммировать остатки для всех цехов? То есть сложить последний остаток Рекламы, Мебели, Метал. .
[moder]Не безобразничайте!
Это другой вопрос, следовательно, другая тема.
Автор - Stormy
Дата добавления - 11.05.2016 в 16:20
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск остатка в таблице (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!