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

Вход

Регистрация

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

 

= Мир MS Excel/Оптимизация функции ЕСЛИ с несколькими условиями - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Оптимизация функции ЕСЛИ с несколькими условиями (Формулы/Formulas)
Оптимизация функции ЕСЛИ с несколькими условиями
scryde2015 Дата: Вторник, 29.01.2019, 06:32 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 40% ±

Excel 2016
Здравствуйте. Крик души... Как можно оптимизировать (сократить) данную строчку (в функции "ЕСЛИ")
Код
=ЕСЛИ(R[-84]C[-3]="хоз.материалы, инвентарь, канц.элект.товары";Статьи!R[-83]C[-1];ЕСЛИ(R[-84]C[-3]="дератизация";Статьи!R[-81]C[-1];ЕСЛИ(R[-84]C[-3]="учетно-бланочная и бланочная продукция";Статьи!R[-80]C[-1];ЕСЛИ(R[-84]C[-3]="вода";Статьи!R[-79]C[-1];ЕСЛИ(R[-84]C[-3]="тепло";Статьи!R[-78]C[-1];ЕСЛИ(R[-84]C[-3]="свет";Статьи!R[-77]C[-1];ЕСЛИ(R[-84]C[-3]="система слежения Посейдон";Статьи!R[-76]C[-1];ЕСЛИ(R[-84]C[-3]="мед.осмотр водителей";Статьи!R[-75]C[-1];ЕСЛИ(R[-84]C[-3]="мониторинг с-мы пожарной безопасности Координата";Статьи!R[-74]C[-1];ЕСЛИ(R[-84]C[-3]="услуги по ТО системы охранной сигнализации в мастерских";Статьи!R[-73]C[-1];ЕСЛИ(R[-84]C[-3]="тревожные кнопки ООО Фортуна-С";Статьи!R[-72]C[-1];ЕСЛИ(R[-84]C[-3]="услуги связи";12;ЕСЛИ(R[-84]C[-3]="уборка и вывоз снега";Статьи!R[-70]C[-1];ЕСЛИ(R[-84]C[-3]="вывоз, утилизация ТБО";Статьи!R[-69]C[-1];ЕСЛИ(R[-84]C[-3]="обновление справочно-информационных баз (Консультант)";Статьи!R[-68]C[-1];ЕСЛИ(R[-84]C[-3]="учебные";Статьи!R[-67]C[-1];ЕСЛИ(R[-84]C[-3]="питание";Статьи!R[-66]C[-1];ЕСЛИ(R[-84]C[-3]="питание детей в лагере";Статьи!R[-65]C[-1];ЕСЛИ(R[-84]C[-3]="услуги автотранспорта";Статьи!R[-64]C[-1];ЕСЛИ(R[-84]C[-3]="промывка и опресовка системы";Статьи!R[-63]C[-1];ЕСЛИ(R[-84]C[-3]="ремонт туалетов";Статьи!R[-62]C[-1];ЕСЛИ(R[-84]C[-3]="работы по ремонту теплицы";Статьи!R[-61]C[-1];ЕСЛИ(R[-84]C[-3]="поверка приборов";Статьи!R[-60]C[-1];ЕСЛИ(R[-84]C[-3]="текущий ремонт";Статьи!R[-59]C[-1];ЕСЛИ(R[-84]C[-3]="работы по договорам ГПХ";Статьи!R[-58]C[-1];ЕСЛИ(R[-84]C[-3]="противопожарные мероприятия (замена обшивки нового корпуса школы по предписанию пожарных)";Статьи!R[-57]C[-1];ЕСЛИ(R[-84]C[-3]="охрана";Статьи!R[-56]C[-1];ЕСЛИ(R[-84]C[-3]="пожарная сигнализация ООО Кашалот";Статьи!R[-55]C[-1];ЕСЛИ(R[-84]C[-3]="услуги по подписке";Статьи!R[-54]C[-1];ЕСЛИ(R[-84]C[-3]="Доступная среда";Статьи!R[-53]C[-1];ЕСЛИ(R[-84]C[-3]="Заливка катка";Статьи!R[-52]C[-1];ЕСЛИ(R[-84]C[-3]="обновление справочно-информационных баз (СофЛайтТрейд)";Статьи!R[-52]C[-1];ЕСЛИ(R[-84]C[-3]="объявления";Статьи!R[-50]C[-1];ЕСЛИ(R[-84]C[-3]="оформление к праздникам";Статьи!R[-49]C[-1];ЕСЛИ(R[-84]C[-3]="электронная система образования";Статьи!R[-48]C[-1];ЕСЛИ(R[-84]C[-3]="повышение квалификации";Статьи!R[-47]C[-1];ЕСЛИ(R[-84]C[-3]="СОУТ (мастерские)";Статьи!R[-46]C[-1];ЕСЛИ(R[-84]C[-3]="услуги по замеру изоляции сопротивления Энергия";Статьи!R[-45]C[-1];ЕСЛИ(R[-84]C[-3]="Медосмотр работников";Статьи!R[-44]C[-1];ЕСЛИ(R[-84]C[-3]="Утилизация ЭкоСтар Технолоджи";Статьи!R[-43]C[-1];ЕСЛИ(R[-84]C[-3]="Загрязнение среды";Статьи!R[-42]C[-1];ЕСЛИ(R[-84]C[-3]="Медикаменты";Статьи!R[-41]C[-1];ЕСЛИ(R[-84]C[-3]="Топливо";Статьи!R[-40]C[-1];ЕСЛИ(R[-84]C[-3]="Автомасла";Статьи!R[-39]C[-1];ЕСЛИ(R[-84]C[-3]="Запчасти";Статьи!R[-38]C[-1];ЕСЛИ(R[-84]C[-3]="Спецодежда";Статьи!R[-37]C[-1];ЕСЛИ(R[-84]C[-3]="Техосмотр";Статьи!R[-36]C[-1];ЕСЛИ(R[-84]C[-3]="Автострахование";Статьи!R[-35]C[-1];))))))))))))))))))))))))))))))))))))))))))))))))

В данный момент имеется документ, в который заносятся:
- все договора/счета и т.д.
- статьи по расходам
- суммы расходов и т.д.
Сейчас заполнение документа происходит след. образом:
1. Если появилась новая статья расходов - добавляем в вкладке "Статьи" новую статью, общую выделенную сумму денег на эту статью
2. Расширяем диапазон в в присвоенном имени статей
3. Добавляем вручную в эту длиннющую строчку (функцию "ЕСЛИ") нашу новую статью.
Изначально хотел сделать так:
- Есть список статей с суммами на каждую статью.
- Есть список контрагентов
- Есть счета/договора, с указанием в них статьи расходов
Действия при занесении данных в документ:
1. Заносим статью в отдельной вкладке (если она новая), сумму денег, выделенную на эту статью
2. В основной таблице заносим данные о контрагенте, статью, по которой будет списаны деньги, сумму по договору/счету
3. В определенный момент, нужно просмотреть остаток денег, по той или иной статье.
Для этого мы фильтруем список по статье, а внизу отфильтрованного списка должен был выйти итог:
1. Сколько было выделено на статью (по которой была произведена фильтрация)
2. Сколько затрачено
3. Сколько осталось
На практике же этого добиться не удалось, т.к.:
Пытался сделать функцию ЕСЛИ с несколькими условиями, что-то типа такого:
- Если в строке R3C2 есть статья "Вода" - то в графе "Выделено" ставить сумму 1.500.000 (например)
Дело это не заработало, т.к. привязка идет к определенной ячейке (R3C2), после фильтрации - чуда не происходит.
Пробовал указывать диапазон всего столбца (в пределах таблицы)- эффект тот же, не работает.
Костылем вышел текущий вариант:
1. Заносим статью в вкладке "Статьи"
2. В основной таблице (верхний левый угол) выбираем добавленную статью
3. Фильтруем статью 4. В ячейке "Выделено" (выше приведен пример функции) добавляем новую статью
Можно ли как-то сократить вид данной функции ? Или вообще, как-нибудь по другому это дело оформить
Заранее извиняюсь, я новичок в этой области
К сообщению приложен файл: ___2019__.xlsx (23.1 Kb)


Сообщение отредактировал scryde2015 - Вторник, 29.01.2019, 06:46
 
Ответить
СообщениеЗдравствуйте. Крик души... Как можно оптимизировать (сократить) данную строчку (в функции "ЕСЛИ")
Код
=ЕСЛИ(R[-84]C[-3]="хоз.материалы, инвентарь, канц.элект.товары";Статьи!R[-83]C[-1];ЕСЛИ(R[-84]C[-3]="дератизация";Статьи!R[-81]C[-1];ЕСЛИ(R[-84]C[-3]="учетно-бланочная и бланочная продукция";Статьи!R[-80]C[-1];ЕСЛИ(R[-84]C[-3]="вода";Статьи!R[-79]C[-1];ЕСЛИ(R[-84]C[-3]="тепло";Статьи!R[-78]C[-1];ЕСЛИ(R[-84]C[-3]="свет";Статьи!R[-77]C[-1];ЕСЛИ(R[-84]C[-3]="система слежения Посейдон";Статьи!R[-76]C[-1];ЕСЛИ(R[-84]C[-3]="мед.осмотр водителей";Статьи!R[-75]C[-1];ЕСЛИ(R[-84]C[-3]="мониторинг с-мы пожарной безопасности Координата";Статьи!R[-74]C[-1];ЕСЛИ(R[-84]C[-3]="услуги по ТО системы охранной сигнализации в мастерских";Статьи!R[-73]C[-1];ЕСЛИ(R[-84]C[-3]="тревожные кнопки ООО Фортуна-С";Статьи!R[-72]C[-1];ЕСЛИ(R[-84]C[-3]="услуги связи";12;ЕСЛИ(R[-84]C[-3]="уборка и вывоз снега";Статьи!R[-70]C[-1];ЕСЛИ(R[-84]C[-3]="вывоз, утилизация ТБО";Статьи!R[-69]C[-1];ЕСЛИ(R[-84]C[-3]="обновление справочно-информационных баз (Консультант)";Статьи!R[-68]C[-1];ЕСЛИ(R[-84]C[-3]="учебные";Статьи!R[-67]C[-1];ЕСЛИ(R[-84]C[-3]="питание";Статьи!R[-66]C[-1];ЕСЛИ(R[-84]C[-3]="питание детей в лагере";Статьи!R[-65]C[-1];ЕСЛИ(R[-84]C[-3]="услуги автотранспорта";Статьи!R[-64]C[-1];ЕСЛИ(R[-84]C[-3]="промывка и опресовка системы";Статьи!R[-63]C[-1];ЕСЛИ(R[-84]C[-3]="ремонт туалетов";Статьи!R[-62]C[-1];ЕСЛИ(R[-84]C[-3]="работы по ремонту теплицы";Статьи!R[-61]C[-1];ЕСЛИ(R[-84]C[-3]="поверка приборов";Статьи!R[-60]C[-1];ЕСЛИ(R[-84]C[-3]="текущий ремонт";Статьи!R[-59]C[-1];ЕСЛИ(R[-84]C[-3]="работы по договорам ГПХ";Статьи!R[-58]C[-1];ЕСЛИ(R[-84]C[-3]="противопожарные мероприятия (замена обшивки нового корпуса школы по предписанию пожарных)";Статьи!R[-57]C[-1];ЕСЛИ(R[-84]C[-3]="охрана";Статьи!R[-56]C[-1];ЕСЛИ(R[-84]C[-3]="пожарная сигнализация ООО Кашалот";Статьи!R[-55]C[-1];ЕСЛИ(R[-84]C[-3]="услуги по подписке";Статьи!R[-54]C[-1];ЕСЛИ(R[-84]C[-3]="Доступная среда";Статьи!R[-53]C[-1];ЕСЛИ(R[-84]C[-3]="Заливка катка";Статьи!R[-52]C[-1];ЕСЛИ(R[-84]C[-3]="обновление справочно-информационных баз (СофЛайтТрейд)";Статьи!R[-52]C[-1];ЕСЛИ(R[-84]C[-3]="объявления";Статьи!R[-50]C[-1];ЕСЛИ(R[-84]C[-3]="оформление к праздникам";Статьи!R[-49]C[-1];ЕСЛИ(R[-84]C[-3]="электронная система образования";Статьи!R[-48]C[-1];ЕСЛИ(R[-84]C[-3]="повышение квалификации";Статьи!R[-47]C[-1];ЕСЛИ(R[-84]C[-3]="СОУТ (мастерские)";Статьи!R[-46]C[-1];ЕСЛИ(R[-84]C[-3]="услуги по замеру изоляции сопротивления Энергия";Статьи!R[-45]C[-1];ЕСЛИ(R[-84]C[-3]="Медосмотр работников";Статьи!R[-44]C[-1];ЕСЛИ(R[-84]C[-3]="Утилизация ЭкоСтар Технолоджи";Статьи!R[-43]C[-1];ЕСЛИ(R[-84]C[-3]="Загрязнение среды";Статьи!R[-42]C[-1];ЕСЛИ(R[-84]C[-3]="Медикаменты";Статьи!R[-41]C[-1];ЕСЛИ(R[-84]C[-3]="Топливо";Статьи!R[-40]C[-1];ЕСЛИ(R[-84]C[-3]="Автомасла";Статьи!R[-39]C[-1];ЕСЛИ(R[-84]C[-3]="Запчасти";Статьи!R[-38]C[-1];ЕСЛИ(R[-84]C[-3]="Спецодежда";Статьи!R[-37]C[-1];ЕСЛИ(R[-84]C[-3]="Техосмотр";Статьи!R[-36]C[-1];ЕСЛИ(R[-84]C[-3]="Автострахование";Статьи!R[-35]C[-1];))))))))))))))))))))))))))))))))))))))))))))))))

В данный момент имеется документ, в который заносятся:
- все договора/счета и т.д.
- статьи по расходам
- суммы расходов и т.д.
Сейчас заполнение документа происходит след. образом:
1. Если появилась новая статья расходов - добавляем в вкладке "Статьи" новую статью, общую выделенную сумму денег на эту статью
2. Расширяем диапазон в в присвоенном имени статей
3. Добавляем вручную в эту длиннющую строчку (функцию "ЕСЛИ") нашу новую статью.
Изначально хотел сделать так:
- Есть список статей с суммами на каждую статью.
- Есть список контрагентов
- Есть счета/договора, с указанием в них статьи расходов
Действия при занесении данных в документ:
1. Заносим статью в отдельной вкладке (если она новая), сумму денег, выделенную на эту статью
2. В основной таблице заносим данные о контрагенте, статью, по которой будет списаны деньги, сумму по договору/счету
3. В определенный момент, нужно просмотреть остаток денег, по той или иной статье.
Для этого мы фильтруем список по статье, а внизу отфильтрованного списка должен был выйти итог:
1. Сколько было выделено на статью (по которой была произведена фильтрация)
2. Сколько затрачено
3. Сколько осталось
На практике же этого добиться не удалось, т.к.:
Пытался сделать функцию ЕСЛИ с несколькими условиями, что-то типа такого:
- Если в строке R3C2 есть статья "Вода" - то в графе "Выделено" ставить сумму 1.500.000 (например)
Дело это не заработало, т.к. привязка идет к определенной ячейке (R3C2), после фильтрации - чуда не происходит.
Пробовал указывать диапазон всего столбца (в пределах таблицы)- эффект тот же, не работает.
Костылем вышел текущий вариант:
1. Заносим статью в вкладке "Статьи"
2. В основной таблице (верхний левый угол) выбираем добавленную статью
3. Фильтруем статью 4. В ячейке "Выделено" (выше приведен пример функции) добавляем новую статью
Можно ли как-то сократить вид данной функции ? Или вообще, как-нибудь по другому это дело оформить
Заранее извиняюсь, я новичок в этой области

Автор - scryde2015
Дата добавления - 29.01.2019 в 06:32
sboy Дата: Вторник, 29.01.2019, 09:29 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
ЕСЛИ() не очень подходит для такого рода операций.
Код
=ВПР(A1;Статьи!B2:C62;2;)
К сообщению приложен файл: _2019_.xlsx (22.6 Kb)


Яндекс: 410016850021169
 
Ответить
СообщениеДобрый день.
ЕСЛИ() не очень подходит для такого рода операций.
Код
=ВПР(A1;Статьи!B2:C62;2;)

Автор - sboy
Дата добавления - 29.01.2019 в 09:29
krosav4ig Дата: Вторник, 29.01.2019, 11:26 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
что-то показалось мне что ТС нужна формула типа
Код
=СУММПРОИЗВ(ЗНАК(СЧЁТЕСЛИ(Реестр!B3:B83;Статьи!B2:B62));Статьи!C2:C62)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщениечто-то показалось мне что ТС нужна формула типа
Код
=СУММПРОИЗВ(ЗНАК(СЧЁТЕСЛИ(Реестр!B3:B83;Статьи!B2:B62));Статьи!C2:C62)

Автор - krosav4ig
Дата добавления - 29.01.2019 в 11:26
scryde2015 Дата: Среда, 30.01.2019, 01:02 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 40% ±

Excel 2016
sboy, Здравствуйте, я наверное слишком сильно замудрил с описанием всего моего безобразия %)
Можно ли на Вашем примере сделать так, чтобы после фильтрации "Статьи расходов" (обычно выбирается 1 статья) - в поле "Выделено" автоматически выдавалось кол-во денег (из вкладки "Статьи"), выделенные на эту статью. (Т.е. нужно исключить действия с столбцом "А1")
Т.е., действия должны быть следующие:
1. Фильтруем статьи
2. В графе "Выделено" определяет какая статья стоит первой после фильтрации
3. В графе "Выделено" подтягиваются данные из вкладки "Статьи" соответствующей статьи (по которой был произведен фильтр)
Прикрепил обновленный вариант таблицы и скрин
К сообщению приложен файл: ___2019__1.xlsx (25.4 Kb) · 8839432.png (107.6 Kb)


Сообщение отредактировал scryde2015 - Среда, 30.01.2019, 03:23
 
Ответить
Сообщениеsboy, Здравствуйте, я наверное слишком сильно замудрил с описанием всего моего безобразия %)
Можно ли на Вашем примере сделать так, чтобы после фильтрации "Статьи расходов" (обычно выбирается 1 статья) - в поле "Выделено" автоматически выдавалось кол-во денег (из вкладки "Статьи"), выделенные на эту статью. (Т.е. нужно исключить действия с столбцом "А1")
Т.е., действия должны быть следующие:
1. Фильтруем статьи
2. В графе "Выделено" определяет какая статья стоит первой после фильтрации
3. В графе "Выделено" подтягиваются данные из вкладки "Статьи" соответствующей статьи (по которой был произведен фильтр)
Прикрепил обновленный вариант таблицы и скрин

Автор - scryde2015
Дата добавления - 30.01.2019 в 01:02
krosav4ig Дата: Среда, 30.01.2019, 03:48 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
scryde2015, держите сводную (PowerQuery+PowerPivot)
К сообщению приложен файл: 2019.7z.001 (99.8 Kb) · 2019.7z.002 (6.7 Kb)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Среда, 30.01.2019, 05:12
 
Ответить
Сообщениеscryde2015, держите сводную (PowerQuery+PowerPivot)

Автор - krosav4ig
Дата добавления - 30.01.2019 в 03:48
scryde2015 Дата: Среда, 30.01.2019, 04:35 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 40% ±

Excel 2016
krosav4ig, пишет "Архив поврежден"
Скачал. Правда вообще не представляю как это в дальнейшем редактировать в случае изменений :(


Сообщение отредактировал scryde2015 - Среда, 30.01.2019, 05:05
 
Ответить
Сообщениеkrosav4ig, пишет "Архив поврежден"
Скачал. Правда вообще не представляю как это в дальнейшем редактировать в случае изменений :(

Автор - scryde2015
Дата добавления - 30.01.2019 в 04:35
krosav4ig Дата: Среда, 30.01.2019, 05:13 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
scryde2015, Заменил файлы, на всяк случай, хотя вроде нормально открываются


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщениеscryde2015, Заменил файлы, на всяк случай, хотя вроде нормально открываются

Автор - krosav4ig
Дата добавления - 30.01.2019 в 05:13
scryde2015 Дата: Среда, 30.01.2019, 05:53 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 40% ±

Excel 2016
krosav4ig, спасибо Вам конечно большое за такое творение, но моих мозгов не хватит, если мне скажут "вот сюда нужно добавить еще вот это, это и это :blahblah:"
Хотелось бы получить что-то типа простой формулы, с которой можно будет в дальнейшем работать, понимая что я делаю, откуда растут ноги и т.д.
 
Ответить
Сообщениеkrosav4ig, спасибо Вам конечно большое за такое творение, но моих мозгов не хватит, если мне скажут "вот сюда нужно добавить еще вот это, это и это :blahblah:"
Хотелось бы получить что-то типа простой формулы, с которой можно будет в дальнейшем работать, понимая что я делаю, откуда растут ноги и т.д.

Автор - scryde2015
Дата добавления - 30.01.2019 в 05:53
krosav4ig Дата: Среда, 30.01.2019, 08:43 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
Повесил срез на таблицу Реестр (справа от таблицы), добавил UDF[vba]
Код
Public Function СрезВыбор(sName As String) As Variant
    Dim oSi As SlicerItem, i&, arr() As Variant
    On Error Resume Next
    Application.Volatile
    With ThisWorkbook.SlicerCaches(sName)
        For Each oSi In .SlicerItems
            If oSi.Selected Then
                ReDim Preserve arr(i)
                arr(i) = oSi.Value
                i = i + 1
            End If
        Next
    End With
    СрезВыбор = arr()
End Function
[/vba]
формула
Код
=СУММПРОИЗВ(ВПР(Т(ИНДЕКС(+СрезВыбор("Срез_Статья_расходов");));Статьи;2;))
возвращает сумму значений из таблицы Статьи по всем критериям фильтра столбца Статья расходов
если без среза и UDF, то массивная формула
Код
=СУММ(ЕСЛИОШИБКА((ЧАСТОТА(СТРОКА(Реестр)-МИН(СТРОКА(Реестр)-1);ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ(Реестр[Статья расходов];СТРОКА(Реестр)-МИН(СТРОКА(Реестр));;1))*ПОИСКПОЗ(Реестр[Статья расходов];Реестр[Статья расходов];))>0)*ВПР(Т(ИНДЕКС(+Реестр[Статья расходов];));Статьи;2;);))
собственно, в этой формуле можно заменить ссылки на умные таблицы ссылками на диапазоны
К сообщению приложен файл: 0066352.001 (99.8 Kb) · 7376559.002 (25.5 Kb)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
СообщениеПовесил срез на таблицу Реестр (справа от таблицы), добавил UDF[vba]
Код
Public Function СрезВыбор(sName As String) As Variant
    Dim oSi As SlicerItem, i&, arr() As Variant
    On Error Resume Next
    Application.Volatile
    With ThisWorkbook.SlicerCaches(sName)
        For Each oSi In .SlicerItems
            If oSi.Selected Then
                ReDim Preserve arr(i)
                arr(i) = oSi.Value
                i = i + 1
            End If
        Next
    End With
    СрезВыбор = arr()
End Function
[/vba]
формула
Код
=СУММПРОИЗВ(ВПР(Т(ИНДЕКС(+СрезВыбор("Срез_Статья_расходов");));Статьи;2;))
возвращает сумму значений из таблицы Статьи по всем критериям фильтра столбца Статья расходов
если без среза и UDF, то массивная формула
Код
=СУММ(ЕСЛИОШИБКА((ЧАСТОТА(СТРОКА(Реестр)-МИН(СТРОКА(Реестр)-1);ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ(Реестр[Статья расходов];СТРОКА(Реестр)-МИН(СТРОКА(Реестр));;1))*ПОИСКПОЗ(Реестр[Статья расходов];Реестр[Статья расходов];))>0)*ВПР(Т(ИНДЕКС(+Реестр[Статья расходов];));Статьи;2;);))
собственно, в этой формуле можно заменить ссылки на умные таблицы ссылками на диапазоны

Автор - krosav4ig
Дата добавления - 30.01.2019 в 08:43
scryde2015 Дата: Четверг, 31.01.2019, 01:39 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 40% ±

Excel 2016
krosav4ig, наверное я делаю что-то не так
1. Скачал эти 2 файла
2. Пытаюсь распаковать/открыть (через WinRAR) 0066352.001 - пишет архив поврежден.


Сообщение отредактировал scryde2015 - Четверг, 31.01.2019, 06:54
 
Ответить
Сообщениеkrosav4ig, наверное я делаю что-то не так
1. Скачал эти 2 файла
2. Пытаюсь распаковать/открыть (через WinRAR) 0066352.001 - пишет архив поврежден.

Автор - scryde2015
Дата добавления - 31.01.2019 в 01:39
Светлый Дата: Четверг, 31.01.2019, 08:01 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
пишет архив поврежден

Сделайте одинаковое имя у обоих файлов. Расширение оставьте как есть.


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Четверг, 31.01.2019, 08:02
 
Ответить
Сообщение
пишет архив поврежден

Сделайте одинаковое имя у обоих файлов. Расширение оставьте как есть.

Автор - Светлый
Дата добавления - 31.01.2019 в 08:01
scryde2015 Дата: Четверг, 31.01.2019, 09:14 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 40% ±

Excel 2016
Светлый, получилось )
krosav4ig, Спасибо Вам огромное. Я даже представить не мог, что в экселе такое возможно реализовать yes
 
Ответить
СообщениеСветлый, получилось )
krosav4ig, Спасибо Вам огромное. Я даже представить не мог, что в экселе такое возможно реализовать yes

Автор - scryde2015
Дата добавления - 31.01.2019 в 09:14
scryde2015 Дата: Четверг, 11.04.2019, 03:54 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 40% ±

Excel 2016
Здравствуйте, уважаемые форумчане !
Активно пользуемся данным произведением искусства !
Не могу сообразить как сделать следующее:
На листе "Статьи" в столбец "Сумма по договорам" необходимо добавить информацию из листа "Сводная" (столбцы: п.4 + п.5 + аукцион и т.п.) соответствующих статей затрат
Т.е. в листе "Статьи" необходимо сделать поиск по наименованию статьи затрат, сопоставить это наименование в листе "Сводная", сложить п.4 п.5 аукц между собой и подставить полученное значение в лист "Статьи" - "Сумма по договорам".
Помогите, люди добрые :(

П.С. не получается сжать файл до 100 кб, опубликовал в облаке https://yadi.sk/i/Xs_ByfyxWma5Jg
 
Ответить
СообщениеЗдравствуйте, уважаемые форумчане !
Активно пользуемся данным произведением искусства !
Не могу сообразить как сделать следующее:
На листе "Статьи" в столбец "Сумма по договорам" необходимо добавить информацию из листа "Сводная" (столбцы: п.4 + п.5 + аукцион и т.п.) соответствующих статей затрат
Т.е. в листе "Статьи" необходимо сделать поиск по наименованию статьи затрат, сопоставить это наименование в листе "Сводная", сложить п.4 п.5 аукц между собой и подставить полученное значение в лист "Статьи" - "Сумма по договорам".
Помогите, люди добрые :(

П.С. не получается сжать файл до 100 кб, опубликовал в облаке https://yadi.sk/i/Xs_ByfyxWma5Jg

Автор - scryde2015
Дата добавления - 11.04.2019 в 03:54
китин Дата: Четверг, 11.04.2019, 07:48 | Сообщение № 14
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
scryde2015, как ваш вопрос относится к теме "Оптимизация функции ЕСЛИ с несколькими условиями"????? Правильно никак. Создайте новую тему. Эта тема закрыта


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениеscryde2015, как ваш вопрос относится к теме "Оптимизация функции ЕСЛИ с несколькими условиями"????? Правильно никак. Создайте новую тему. Эта тема закрыта

Автор - китин
Дата добавления - 11.04.2019 в 07:48
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Оптимизация функции ЕСЛИ с несколькими условиями (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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