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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск данных по нескольким значениям. - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск данных по нескольким значениям. (Формулы/Formulas)
Поиск данных по нескольким значениям.
MStoks Дата: Суббота, 29.04.2017, 23:55 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Доброго времени суток.
Нужна помощь с формулой чтобы рассчитать % от выручки при выполнении и невыполнении плана.
Каждому складу присвоена своя группа и подгруппа, исходя из присвоенных данных, при выполнении или невыполнении плана идет свой % от выручки.
При том, что % от выручки может меняться, если Склад сделал сверх плана в своей группе.
Через ВПР тут не получится, пробовал через ИНДЕКС и ПОИСКПОЗ, но не разобрался, как искать при 3 значениях, одно из которых может меняться и влиять на выбор строки и столбца.
Например: Склад Уфа, у которой Группа 1 и Подгруппа А1, при выполнении плана (200 000) получает 20% от выручки, но если она сделает свыше 280 000, то получит 22% от выручки.
К сообщению приложен файл: 0484484.xls(32Kb)


Сообщение отредактировал MStoks - Суббота, 29.04.2017, 23:56
 
Ответить
СообщениеДоброго времени суток.
Нужна помощь с формулой чтобы рассчитать % от выручки при выполнении и невыполнении плана.
Каждому складу присвоена своя группа и подгруппа, исходя из присвоенных данных, при выполнении или невыполнении плана идет свой % от выручки.
При том, что % от выручки может меняться, если Склад сделал сверх плана в своей группе.
Через ВПР тут не получится, пробовал через ИНДЕКС и ПОИСКПОЗ, но не разобрался, как искать при 3 значениях, одно из которых может меняться и влиять на выбор строки и столбца.
Например: Склад Уфа, у которой Группа 1 и Подгруппа А1, при выполнении плана (200 000) получает 20% от выручки, но если она сделает свыше 280 000, то получит 22% от выручки.

Автор - MStoks
Дата добавления - 29.04.2017 в 23:55
vikttur Дата: Воскресенье, 30.04.2017, 00:30 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2400
Репутация: 413 ±
Замечаний: 0% ±

1. Данные должны быть в одинаквых форматах (в столбце А второго листа группы были записаны текстом)
2. Диапазоны через тире - текст. Зачем из текста извлекать начало диапазона, если можно задать вместо диапазона это самое начало?

Формула без самопересчитываемых (летучих) функций:
Код
=ИНДЕКС('2'!$C$3:$J$30;ПОИСКПОЗ(C2;'2'!$A$3:$A$30;)-1+ПОИСКПОЗ(E2;ИНДЕКС('2'!$B$3:$B$30;ПОИСКПОЗ(C2;'2'!$A$3:$A$30;)):ИНДЕКС('2'!$B$3:$B$30;ПОИСКПОЗ(C2;'2'!$A$3:$A$30;)+3));ПОИСКПОЗ(D2;'2'!$C$1:$J$1;))

Для выполнения плана (формула столбца G) добавить +1 к поиску позиции подгруппы.

P.S. В формуле была ошибка определения строки. Заменил формулу и файл.
К сообщению приложен файл: 9324612.xls(35Kb)


Сообщение отредактировал vikttur - Воскресенье, 30.04.2017, 01:01
 
Ответить
Сообщение1. Данные должны быть в одинаквых форматах (в столбце А второго листа группы были записаны текстом)
2. Диапазоны через тире - текст. Зачем из текста извлекать начало диапазона, если можно задать вместо диапазона это самое начало?

Формула без самопересчитываемых (летучих) функций:
Код
=ИНДЕКС('2'!$C$3:$J$30;ПОИСКПОЗ(C2;'2'!$A$3:$A$30;)-1+ПОИСКПОЗ(E2;ИНДЕКС('2'!$B$3:$B$30;ПОИСКПОЗ(C2;'2'!$A$3:$A$30;)):ИНДЕКС('2'!$B$3:$B$30;ПОИСКПОЗ(C2;'2'!$A$3:$A$30;)+3));ПОИСКПОЗ(D2;'2'!$C$1:$J$1;))

Для выполнения плана (формула столбца G) добавить +1 к поиску позиции подгруппы.

P.S. В формуле была ошибка определения строки. Заменил формулу и файл.

Автор - vikttur
Дата добавления - 30.04.2017 в 00:30
MStoks Дата: Воскресенье, 30.04.2017, 01:59 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Формула без самопересчитываемых (летучих) функций:

Супер! pray pray
Единственное, только по 7 группе по всем подгруппам (А1, А2, А3, А4) % с листа '2' считывает не верно. Во вложении выделил желтым пример.
и еще такой вопрос, как можно посчитать Максимальный %, если склад делает свыше плана, и ему к примеру не хватает немного до следующего порока, где % выше?
К сообщению приложен файл: 6712622.xls(34Kb)
 
Ответить
Сообщение
Формула без самопересчитываемых (летучих) функций:

Супер! pray pray
Единственное, только по 7 группе по всем подгруппам (А1, А2, А3, А4) % с листа '2' считывает не верно. Во вложении выделил желтым пример.
и еще такой вопрос, как можно посчитать Максимальный %, если склад делает свыше плана, и ему к примеру не хватает немного до следующего порока, где % выше?

Автор - MStoks
Дата добавления - 30.04.2017 в 01:59
vikttur Дата: Воскресенье, 30.04.2017, 12:02 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 2400
Репутация: 413 ±
Замечаний: 0% ±

Вы взяли файл до исправления. Я вчера новый выложил.
 
Ответить
СообщениеВы взяли файл до исправления. Я вчера новый выложил.

Автор - vikttur
Дата добавления - 30.04.2017 в 12:02
jakim Дата: Воскресенье, 30.04.2017, 13:33 | Сообщение № 5
Группа: Друзья
Ранг: Ветеран
Сообщений: 801
Репутация: 206 ±
Замечаний: 0% ±

Для Excel 2013 подойдут такие формулы

Код
=AGGREGATE(14;6;'2'!$C$3:$J$30/('2'!A$3:A$30='1'!C2)/('2'!B$3:B$30<='1'!E2)/('2'!C$1:J$1='1'!D2)/('2'!C$2:J$2="Не выполнен план");1)

Код
=AGGREGATE(14;6;'2'!$C$3:$J$30/('2'!A$3:A$30='1'!C2)/('2'!B$3:B$30>='1'!E2)/('2'!C$1:J$1='1'!D2)/('2'!C$2:J$2="Выполнен план");1)
К сообщению приложен файл: 0484484.xlsx(12Kb)
 
Ответить
Сообщение
Для Excel 2013 подойдут такие формулы

Код
=AGGREGATE(14;6;'2'!$C$3:$J$30/('2'!A$3:A$30='1'!C2)/('2'!B$3:B$30<='1'!E2)/('2'!C$1:J$1='1'!D2)/('2'!C$2:J$2="Не выполнен план");1)

Код
=AGGREGATE(14;6;'2'!$C$3:$J$30/('2'!A$3:A$30='1'!C2)/('2'!B$3:B$30>='1'!E2)/('2'!C$1:J$1='1'!D2)/('2'!C$2:J$2="Выполнен план");1)

Автор - jakim
Дата добавления - 30.04.2017 в 13:33
MStoks Дата: Воскресенье, 30.04.2017, 21:18 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Для Excel 2013 подойдут такие формулы

Спасибо огромное!! Встала, как родная!!

Раньше не знал об этом форуме! Посоветовал коллега! Буду почаще заходить.


Сообщение отредактировал MStoks - Воскресенье, 30.04.2017, 21:19
 
Ответить
Сообщение
Для Excel 2013 подойдут такие формулы

Спасибо огромное!! Встала, как родная!!

Раньше не знал об этом форуме! Посоветовал коллега! Буду почаще заходить.

Автор - MStoks
Дата добавления - 30.04.2017 в 21:18
MStoks Дата: Воскресенье, 30.04.2017, 21:21 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Вы взяли файл до исправления. Я вчера новый выложил.

Не поддалась этакая.. может в моей таблице что-то не то. Но идею реализовал для поиска других значений! Спасибо гуру!!
 
Ответить
Сообщение
Вы взяли файл до исправления. Я вчера новый выложил.

Не поддалась этакая.. может в моей таблице что-то не то. Но идею реализовал для поиска других значений! Спасибо гуру!!

Автор - MStoks
Дата добавления - 30.04.2017 в 21:21
jakim Дата: Воскресенье, 30.04.2017, 22:05 | Сообщение № 8
Группа: Друзья
Ранг: Ветеран
Сообщений: 801
Репутация: 206 ±
Замечаний: 0% ±

Максимальный %

Код
=AGGREGATE(14;6;'2'!$C$3:$J$30/('2'!A$3:A$30='1'!C2)/('2'!C$1:J$1='1'!D2)/('2'!C$2:J$2="Выполнен план");1)

Сколько нужно сделать для MAX %

Код
=AGGREGATE(15;6;'2'!B$3:B$30/('2'!A$3:A$30='1'!C2)/('2'!C$1:J$1='1'!D2)/('2'!$C$3:$J$30='1'!I2)/('2'!C$2:J$2="Выполнен план");1)
К сообщению приложен файл: 6712622.xlsx(14Kb)
 
Ответить
Сообщение
Максимальный %

Код
=AGGREGATE(14;6;'2'!$C$3:$J$30/('2'!A$3:A$30='1'!C2)/('2'!C$1:J$1='1'!D2)/('2'!C$2:J$2="Выполнен план");1)

Сколько нужно сделать для MAX %

Код
=AGGREGATE(15;6;'2'!B$3:B$30/('2'!A$3:A$30='1'!C2)/('2'!C$1:J$1='1'!D2)/('2'!$C$3:$J$30='1'!I2)/('2'!C$2:J$2="Выполнен план");1)

Автор - jakim
Дата добавления - 30.04.2017 в 22:05
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск данных по нескольким значениям. (Формулы/Formulas)
Страница 1 из 11
Поиск:

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