Заполнение таблицы данными из другой, по нескольким параметр

Страница 1 из 1 1
Модератор форума: китин, _Boroda_, DrMini

Заполнение таблицы данными из другой, по нескольким параметр (Формулы/Formulas)

Macintoshka

Дата: Среда, 19.08.2015, 14:33 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 33

Репутация: 9 ±

Замечаний: 20% ±

Уважаемые Форумчане,
Просьба помочь в решение след задачи

Имеем прай-лист на трубную продукцию, где цена зависит от:
- Марки
- Диаметра
- Погран-перехода
- Покупателя
- В некоторых случаях от грузополучателя ( См. Покупатель 20 из примера)

Сложности:
- Пробовал ч-з формулу СУММПРОИЗВ, но диапазон диаметров учесть не могу
- Цена на погран-переходе МОСКВА и ВОРОНЕЖ - одинаковые
- Цены для покупателей 1;2;8;9 совпадают ( или стоит записать их в столбце ниже? чтобы было проще использовать ф-лу?)
- Цена для покупателя 20 зависит от грузополучателей (1 и 2)

Надеюсь на позитивный отклик,
Заранее большое спасибо

ЗЫ если пример не корректный, просьба, указать - скорректирую

К сообщению приложен файл: 2146398.xlsx (26.1 Kb)

Ответить

Michael_S

Дата: Среда, 19.08.2015, 15:04 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 2012

Репутация: 373 ±

Замечаний: 0% ±

Excel2016

если Price-list составить правильно, то как-то так

К сообщению приложен файл: Macintoshka.xlsx (29.0 Kb)

Ответить

_Boroda_

Дата: Среда, 19.08.2015, 15:12 | Сообщение № 3

Группа: Админы

Ранг: Местный житель

Сообщений: 16893

Репутация: 6611 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

А если Прайс не менять (и если я ничего нигде не напутал), то вот так

Код

=СУММ('Price-list'!G$4:L$63*ЕЧИСЛО(ПОИСК(C2;ПРОСМОТР(СТРОКА('Price-list'!A$4:A$63);СТРОКА('Price-list'!A$4:A$63)/('Price-list'!D$4:D$63<>"");'Price-list'!C$4:C$63&'Price-list'!D$4:D$63)))*(A2='Price-list'!E$4:E$63)*ЕЧИСЛО(ПОИСК(E2;'Price-list'!G$3:L$3))*ЕСЛИ(D2="";1;D2=ПРОСМОТР(СТРОКА('Price-list'!A$4:A$63);СТРОКА('Price-list'!A$4:A$63)/('Price-list'!B$4:B$63<>"");'Price-list'!B$4:B$63))*ЕСЛИОШИБКА((--ЛЕВБ('Price-list'!F$4:F$63;2)<=B2)*(--ПСТР('Price-list'!F$4:F$63;4;9)>=B2);1))

К сообщению приложен файл: 2146398_1.xlsx (27.4 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Michael_S

Дата: Среда, 19.08.2015, 15:26 | Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 2012

Репутация: 373 ±

Замечаний: 0% ±

Excel2016

Цитата _Boroda_, 19.08.2015 в 15:12, в сообщении № 3

А если Прайс не менять

[offtop]у меня счас появился принцип - "все извращения за ваши бабки"

Если серьезно - я пока такие формулы не осиливаю

[/offtop]

Ответить

Macintoshka

Дата: Среда, 19.08.2015, 15:29 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 33

Репутация: 9 ±

Замечаний: 20% ±

Огромное спасибо! Постараюсь разобраться с каждым из решений, с вопросами вернусь

Ответить

Macintoshka

Дата: Среда, 19.08.2015, 16:53 | Сообщение № 6

Группа: Пользователи

Ранг: Новичок

Сообщений: 33

Репутация: 9 ±

Замечаний: 20% ±

Цитата _Boroda_, 19.08.2015 в 15:12, в сообщении № 3

А если Прайс не менять (и если я ничего нигде не напутал), то вот так

Не очень нагло будет попросить вас разобрать формулу? Работает круто, но понять ее не просто.
В частности не ясен маневр *ЕЧИСЛО(ПОИСК(C2;ПРОСМОТР(СТРОКА('Price-list'!A$4:A$63);СТРОКА('Price-list'!A$4:A$63)/('Price-list'!D$4:D$63<>"");'Price-list'!C$4:C$63&'Price-list'!D$4:D$63)
Столбец А ведь не заполнен, для чего отсыл к нему идет?

Ответить

_Boroda_

Дата: Среда, 19.08.2015, 17:20 | Сообщение № 7

Группа: Админы

Ранг: Местный житель

Сообщений: 16893

Репутация: 6611 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Цитата Michael_S, 19.08.2015 в 15:26, в сообщении № 4

я пока такие формулы не осиливаю

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

Код

ПРОСМОТР(СТРОКА('Price-list'!A$4:A$63);СТРОКА('Price-list'!A$4:A$63)/('Price-list'!B$4:B$63<>"");'Price-list'!B$4:B$63)

-такой ПРОСМОТР дает из массива типа ААА;"";"";ВВВ;"";"";"";... вот такой ААА;ААА;ААА;ВВ;ВВ;ВВ;ВВ;...
как работает -
1. кусок СТРОКА('Price-list'!A$4:A$63)/('Price-list'!B$4:B$63<>"") дает или номер строки, или ошибку деления на 0 (когда B$4:B$63="") - 4;ош;ош;7;ош;ош;ош;...
2. в куске ПРОСМОТР(СТРОКА('Price-list'!A$4:A$63);СТРОКА('Price-list'!A$4:A$63)/('Price-list'!B$4:B$63<>"") ищем числа 4;5;6;...;63 в массиве из п.1
3. если находим, то выводим соответствующее значение из массива 'Price-list'!B$4:B$63, если не находим, то "поднимаемся" наверх до того момента, как находили (в примере для значения 9 соответствие будет 7, а для 6 будет 4) - ищим максимальную найденную в п.2 строку, не большую искомой.
Еще объяснялку для ПРОСМОТР можно посмотреть здесь http://www.excelworld.ru/forum/2-16573-138042-16-1427281717

Пока писал для Михаила, Иван еще вопрос задал. Сразу допишу
СТРОКА('Price-list'!A$4:A$63) дает нам номера строк с 4 по 63 - нам все равно, какой у ссылок столбец, можно вообще вот так написать СТРОКА('Price-list'!$4:$63)
ПРОСМОТР(СТРОКА('Price-list'!A$4:A$63);СТРОКА('Price-list'!A$4:A$63)/('Price-list'!D$4:D$63<>"");'Price-list'!C$4:C$63&'Price-list'!D$4:D$63) даст нам сцепленные значения из столбцов С и D (принцип см. этом посте выше) ПОИСКом мы ищем в этой сцепке значение С2 - итогом будет или какое-то число, или ошибка - и с помощью ЕЧИСЛО преобразуем полученное в ИСТИНА или ЛОЖЬ, которые после умножения дают нам 1 или 0.

Цитата Michael_S, 19.08.2015 в 15:26, в сообщении № 4

я пока такие формулы не осиливаю

Код

ПРОСМОТР(СТРОКА('Price-list'!A$4:A$63);СТРОКА('Price-list'!A$4:A$63)/('Price-list'!B$4:B$63<>"");'Price-list'!B$4:B$63)

Ответить

Сообщение

Цитата Michael_S, 19.08.2015 в 15:26, в сообщении № 4

я пока такие формулы не осиливаю

Код

ПРОСМОТР(СТРОКА('Price-list'!A$4:A$63);СТРОКА('Price-list'!A$4:A$63)/('Price-list'!B$4:B$63<>"");'Price-list'!B$4:B$63)

Автор - _Boroda_
Дата добавления - 19.08.2015 в 17:20

Заполнение таблицы данными из другой, по нескольким параметр (Формулы/Formulas)

Страница 1 из 1
1