Формула вывода названия услуги по нескольким критериям

Дата: Четверг, 16.03.2017, 13:30 | Сообщение № 1

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

Ранг: Участник

Сообщений: 57

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

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

Excel 2k3;2k7;2k10;2k13;2k19

Здравствуйте,

Помогите пожалуйста. Есть книга Excel по начислению коммунальных
С листами Ведомости по начислению (с '01.2017' по '12.2017'), 'Счет', 'Тариф'
Как сделать изменение названия услуги в Счете, так чтобы если оплата услуги производится по счетчику (столбцы "V" и "W" листов с '01.2017' по '12.2017', листы 'с '02.2017' по '12.2017 удалил для уменьшения размера файла)

Первый критерий: Оперативный месяц (ячейка I1)
Второй критерий: Наличие счетчика для услуги (колонка "V" и "W" для электричества и воды соответственно, ведомости по начислению за оперативный месяц)

Например если в Ведомости по начислению указано что оплата электричества по счетчику то название услуги в счете выводилось бы как "Электроснабжение (со счетчиком)" а если без счетчика то -- "Электроснабжение (без счетчика)"

К сообщению приложен файл: 2017-ru-2.xls (83.0 Kb)

Дата: Четверг, 16.03.2017, 13:53 | Сообщение № 2

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 237

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

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

Excel 2010

У Петрова в первой строке для электричества стоит "Да", а во второй и третьей - нет. В этом какой-то смысл? На какое значение орентироваться тогда?
Или банальная неточность занесения данных?

Дата: Четверг, 16.03.2017, 14:00 | Сообщение № 3

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

Ранг: Участник

Сообщений: 57

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

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

Excel 2k3;2k7;2k10;2k13;2k19

Один человек одна строчка для петрова это строчка №10
2 другие строчки это два разных человека

sboy

Дата: Четверг, 16.03.2017, 14:20 | Сообщение № 4

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

Ранг: Участник клуба

Сообщений: 2566

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

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

Excel 2010

Добрый день.
С этим не совсем понятно

Цитата Shakal07, 16.03.2017 в 14:00, в сообщении № 3 ()

Один человек одна строчка для петрова это строчка №10

Вот формула(ориентируется на строку №10)

Код

=ЕСЛИ(ДВССЫЛ("0"&G1&"."&H1&"!V10")="да";"Электроснабжение (со счетчиком)";"Электроснабжение (без счетчика)")

Яндекс: 410016850021169

Сообщение отредактировал sboy - Четверг, 16.03.2017, 14:20

Дата: Четверг, 16.03.2017, 14:23 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 237

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

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

Excel 2010

Для электроснабжения:

Код

="Электроснабжение"&ЕСЛИОШИБКА(ВПР(ПРОПИСН(ИНДЕКС(ДВССЫЛ("'"&ТЕКСТ(МЕСЯЦ($I$1);"00")&"."&ТЕКСТ(ГОД($I$1);"0000")&"'!V:V");ПОИСКПОЗ(ИНДЕКС(Жители!B:B;ПОИСКПОЗ($D6;Список_жильців_ПІБ;0));ДВССЫЛ("'"&ТЕКСТ(МЕСЯЦ($I$1);"00")&"."&ТЕКСТ(ГОД($I$1);"0000")&"'!B:B");0)));{"да";" (со счетчиком)":"нет";" (без счетчика)"};2;0);" наличие счетчика неизвестно")

Для водоснабжения:

Код

="Водоснабжения и водоотведения"&ЕСЛИОШИБКА(ВПР(ПРОПИСН(ИНДЕКС(ДВССЫЛ("'"&ТЕКСТ(МЕСЯЦ($I$1);"00")&"."&ТЕКСТ(ГОД($I$1);"0000")&"'!W:W");ПОИСКПОЗ(ИНДЕКС(Жители!B:B;ПОИСКПОЗ($D6;Список_жильців_ПІБ;0));ДВССЫЛ("'"&ТЕКСТ(МЕСЯЦ($I$1);"00")&"."&ТЕКСТ(ГОД($I$1);"0000")&"'!B:B");0)));{"да";" (со счетчиком)":"нет";" (без счетчика)"};2;0);" наличие счетчика неизвестно")

Файл не могу приложить - почему-то больше 100кб получается при сохранении. Но скопировать, надеюсь, не проблема?

Сообщение отредактировал Perfect2You - Четверг, 16.03.2017, 14:23

Дата: Четверг, 16.03.2017, 14:40 | Сообщение № 6

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

Ранг: Участник

Сообщений: 57

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

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

Excel 2k3;2k7;2k10;2k13;2k19

Спасибо, за вариант

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

Возможно задание описал не совсем корректно, есть лист "Счет"
Например, рассмотрим ячейку 'A16'
надо сменить название услуги таким образ в ячейке значение изменялось в зависимости того что выбрано в ячейках I1, D6
Например в ячейке I1 выбран "Январь", а в ячейке D6 - "Иванов Иван Иванович"
тогда по первой ячейке выбрать лист в данном случае это '01.2017'
по второй ячейке из листа 'Жители' найти сокращенное имя жителя из колонки 'B'
и иже по сокращенному имени в листе '01.2017' найти человека у которого в колонке на январь стоит значение "Да"
И в ячейку 'A16' листа "Счет" вывести значение "Электроснабжение (со счетчиком)" если там значение "Нет" вывести значение "Электроснабжение (без счетчика)"

понимаю задачка не простая

sboy

Дата: Четверг, 16.03.2017, 14:45 | Сообщение № 7

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

Ранг: Участник клуба

Сообщений: 2566

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

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

Excel 2010

Цитата Shakal07, 16.03.2017 в 14:40, в сообщении № 6 ()

по сокращенному имени в листе '01.2017' найти человека у которого в колонке на январь стоит значение "Да"

Вот этого нет в файле-примере

Яндекс: 410016850021169

Дата: Четверг, 16.03.2017, 14:58 | Сообщение № 8

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 237

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

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

Excel 2010

Мой вариант, вроде, этого не боится!
За основу взял формулы, которые у Вас уже были (если они работали верно, мои тоже сработают), дополнил оболочкой. На всякий случай подстраховался от неверных исходных данных (и не "да", и не "нет").

Дата: Четверг, 16.03.2017, 15:00 | Сообщение № 9

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

Ранг: Участник

Сообщений: 57

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

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

Excel 2k3;2k7;2k10;2k13;2k19

Perfect2You, Спасибо за формулу но у меня выдает ошибку

попробуйте удалить из файла скрытые листы 'Рекв' и 'Ведом'

возможно это уменьшит размер файла

Сообщение отредактировал Shakal07 - Четверг, 16.03.2017, 15:01

Дата: Четверг, 16.03.2017, 15:08 | Сообщение № 10

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 237

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

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

Excel 2010

Понятно.
В Вашей версии EXCEL еще нет функции ЕСЛИОШИБКА и на грани уровень вложенности формул, что не дает использовать ЕСЛИ(ЕОШИБКА. Поэтому придется просто убрать страховку от некорректных данных, остается:
Для электроснабжения:

Код

="Электроснабжение"&ВПР(ПРОПИСН(ИНДЕКС(ДВССЫЛ("'"&ТЕКСТ(МЕСЯЦ($I$1);"00")&"."&ТЕКСТ(ГОД($I$1);"0000")&"'!V:V");ПОИСКПОЗ(ИНДЕКС(Жители!B:B;ПОИСКПОЗ($D6;Список_жильців_ПІБ;0));ДВССЫЛ("'"&ТЕКСТ(МЕСЯЦ($I$1);"00")&"."&ТЕКСТ(ГОД($I$1);"0000")&"'!B:B");0)));{"да";" (со счетчиком)":"нет";" (без счетчика)"};2;0)

Для водоснабжения:

Код

="Водоснабжения и водоотведения"&ВПР(ПРОПИСН(ИНДЕКС(ДВССЫЛ("'"&ТЕКСТ(МЕСЯЦ($I$1);"00")&"."&ТЕКСТ(ГОД($I$1);"0000")&"'!W:W");ПОИСКПОЗ(ИНДЕКС(Жители!B:B;ПОИСКПОЗ($D6;Список_жильців_ПІБ;0));ДВССЫЛ("'"&ТЕКСТ(МЕСЯЦ($I$1);"00")&"."&ТЕКСТ(ГОД($I$1);"0000")&"'!B:B");0)));{"да";" (со счетчиком)":"нет";" (без счетчика)"};2;0)

Дата: Четверг, 16.03.2017, 15:23 | Сообщение № 11

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

Ранг: Участник

Сообщений: 57

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

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

Excel 2k3;2k7;2k10;2k13;2k19

Perfect2You,

Спасибо, работает осталось подправить формулы для колонки начислено за услугу