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

Вход

Регистрация

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

 

= Мир MS Excel/поиск значения согласно дате с переходом на другой лист - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » поиск значения согласно дате с переходом на другой лист (Формулы/Formulas)
поиск значения согласно дате с переходом на другой лист
lebensvoll Дата: Четверг, 30.08.2018, 09:18 | Сообщение № 1
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
Доброе утро, многоуважаемые форумчане!!!
Прошу Вас помощи не могу совладать с решением...
Имеется файл разбитый на ЛИСТЫ месяца (в данном примере лист Июнь и Июль). На Листе Июнь есть два периода (указывается оператором в ячейках S2 T2 и S3 T3).
Так вот сейчас интересует период (S3 T3) вынуждены переходить при расчетах на другой лист (Июнь) и там найти значение из столбца G согласно указанной дате 2.07.2018.
Расчет произвожу в ячейке W3 формула состоит из нескольких частей:
Код
=ЕСЛИОШИБКА((СУММЕСЛИМН($N$7:$N$68;ДВССЫЛ($P$1&"!$R$7:$R$68");">="&$S$3;ДВССЫЛ($P$1&"!$R$7:$R$68");"<="&$T$3)+ЕСЛИ(МЕСЯЦ(T3)<>МЕСЯЦ(S3);СУММЕСЛИМН(ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"!$N$7:$N$68");ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"!$R$7:$R$68");">="&$S$3;ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"!$R$7:$R$68");"<="&$T$3)))/(V3-U3+ЕСЛИ(МЕСЯЦ(T3)=МЕСЯЦ(S3);G7;ИНДЕКС(ДВССЫЛ(ТЕКСТ(Z7+1;"ММММ")&"!G7:G68");ДЕНЬ(T3))));)

1. Кусок формулы считает все верно и ответ равен 5058
Код
СУММЕСЛИМН($N$7:$N$68;ДВССЫЛ($P$1&"!$R$7:$R$68");">="&$S$3;ДВССЫЛ($P$1&"!$R$7:$R$68");"<="&$T$3)

2. Кусок формулы считает также все верно и она понимает что нужно найти еще значения на другом листе и просуммировать их, равно 774
Код
ЕСЛИ(МЕСЯЦ(T3)<>МЕСЯЦ(S3);СУММЕСЛИМН(ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"!$N$7:$N$68");ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"!$R$7:$R$68");">="&$S$3;ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"!$R$7:$R$68");"<="&$T$3))

далее прежде чем произвести деление мы должны еще выбрать значения (тут тоже проблем нет).
А вот найти значение по столбцу G согласно указанной дате (а это нужно перейти на другой лист) ответ не верен (((
3. Ответ равен 200 (согласно формулы :'( а это значение вообще с листа Июнь. А нужно то перейти на лист Июль и взять значение равное 278 (G10))
Код
ЕСЛИ(МЕСЯЦ(T3)=МЕСЯЦ(S3);ИНДЕКС(G7:G68;ПОИСКПОЗ(T3;A7:A68;0));ИНДЕКС(ДВССЫЛ(ТЕКСТ(Z7+1;"ММММ")&"!G7:G68");ДЕНЬ(T3)))


Прошу Вас помогите с решением как это можно прописать!?
К сообщению приложен файл: 9955632.xlsx (42.7 Kb)


Кто бы ты ни был, мир в твоих руках
 
Ответить
СообщениеДоброе утро, многоуважаемые форумчане!!!
Прошу Вас помощи не могу совладать с решением...
Имеется файл разбитый на ЛИСТЫ месяца (в данном примере лист Июнь и Июль). На Листе Июнь есть два периода (указывается оператором в ячейках S2 T2 и S3 T3).
Так вот сейчас интересует период (S3 T3) вынуждены переходить при расчетах на другой лист (Июнь) и там найти значение из столбца G согласно указанной дате 2.07.2018.
Расчет произвожу в ячейке W3 формула состоит из нескольких частей:
Код
=ЕСЛИОШИБКА((СУММЕСЛИМН($N$7:$N$68;ДВССЫЛ($P$1&"!$R$7:$R$68");">="&$S$3;ДВССЫЛ($P$1&"!$R$7:$R$68");"<="&$T$3)+ЕСЛИ(МЕСЯЦ(T3)<>МЕСЯЦ(S3);СУММЕСЛИМН(ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"!$N$7:$N$68");ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"!$R$7:$R$68");">="&$S$3;ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"!$R$7:$R$68");"<="&$T$3)))/(V3-U3+ЕСЛИ(МЕСЯЦ(T3)=МЕСЯЦ(S3);G7;ИНДЕКС(ДВССЫЛ(ТЕКСТ(Z7+1;"ММММ")&"!G7:G68");ДЕНЬ(T3))));)

1. Кусок формулы считает все верно и ответ равен 5058
Код
СУММЕСЛИМН($N$7:$N$68;ДВССЫЛ($P$1&"!$R$7:$R$68");">="&$S$3;ДВССЫЛ($P$1&"!$R$7:$R$68");"<="&$T$3)

2. Кусок формулы считает также все верно и она понимает что нужно найти еще значения на другом листе и просуммировать их, равно 774
Код
ЕСЛИ(МЕСЯЦ(T3)<>МЕСЯЦ(S3);СУММЕСЛИМН(ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"!$N$7:$N$68");ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"!$R$7:$R$68");">="&$S$3;ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"!$R$7:$R$68");"<="&$T$3))

далее прежде чем произвести деление мы должны еще выбрать значения (тут тоже проблем нет).
А вот найти значение по столбцу G согласно указанной дате (а это нужно перейти на другой лист) ответ не верен (((
3. Ответ равен 200 (согласно формулы :'( а это значение вообще с листа Июнь. А нужно то перейти на лист Июль и взять значение равное 278 (G10))
Код
ЕСЛИ(МЕСЯЦ(T3)=МЕСЯЦ(S3);ИНДЕКС(G7:G68;ПОИСКПОЗ(T3;A7:A68;0));ИНДЕКС(ДВССЫЛ(ТЕКСТ(Z7+1;"ММММ")&"!G7:G68");ДЕНЬ(T3)))


Прошу Вас помогите с решением как это можно прописать!?

Автор - lebensvoll
Дата добавления - 30.08.2018 в 09:18
lebensvoll Дата: Четверг, 30.08.2018, 10:04 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
Дополняю 3-ю часть (веду работу только по ней:
Код
ЕСЛИ(МЕСЯЦ(T3)=МЕСЯЦ(S3);ИНДЕКС(G7:G68;ПОИСКПОЗ(T3;A7:A68;0));ИНДЕКС(ДВССЫЛ(ТЕКСТ(Z7+1;"ММММ")&"!G7:G68");[u]ПОИСКПОЗ(ДЕНЬ(T3);A7:A68;0))[/u])

ОТВЕТ вообще #НД
просто жесть


Кто бы ты ни был, мир в твоих руках
 
Ответить
СообщениеДополняю 3-ю часть (веду работу только по ней:
Код
ЕСЛИ(МЕСЯЦ(T3)=МЕСЯЦ(S3);ИНДЕКС(G7:G68;ПОИСКПОЗ(T3;A7:A68;0));ИНДЕКС(ДВССЫЛ(ТЕКСТ(Z7+1;"ММММ")&"!G7:G68");[u]ПОИСКПОЗ(ДЕНЬ(T3);A7:A68;0))[/u])

ОТВЕТ вообще #НД
просто жесть

Автор - lebensvoll
Дата добавления - 30.08.2018 в 10:04
_Boroda_ Дата: Четверг, 30.08.2018, 10:24 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Александр, я Вам уже неоднократно писал в предыдущих Ваших точно таких же вопросах - не +1, а +31. И в других файлах, при общении в личке, то же самое было. Но Вы почему-то упорно эту единицу ставите.
Какой смысл к первому числу месяца прибавлять единицу, если нам нужен следующий месяц?
Но даже так - эта формула была написана для совершенно другого файла, где даты идут подряд. Ее нельзя просто перенести в этот (пусть и поменяв диапазоны). Нужно менять и саму формулу


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеАлександр, я Вам уже неоднократно писал в предыдущих Ваших точно таких же вопросах - не +1, а +31. И в других файлах, при общении в личке, то же самое было. Но Вы почему-то упорно эту единицу ставите.
Какой смысл к первому числу месяца прибавлять единицу, если нам нужен следующий месяц?
Но даже так - эта формула была написана для совершенно другого файла, где даты идут подряд. Ее нельзя просто перенести в этот (пусть и поменяв диапазоны). Нужно менять и саму формулу

Автор - _Boroda_
Дата добавления - 30.08.2018 в 10:24
lebensvoll Дата: Четверг, 30.08.2018, 10:46 | Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
_Boroda_,
Александр, как я понимаю почему +1, потому как к дате прибавить месяц (а получается что я прибавляю лишь один день %) )
Цитата
Но даже так - эта формула была написана для совершенно другого файла, где даты идут подряд. Ее нельзя просто перенести в этот (пусть и поменяв диапазоны). Нужно менять и саму формулу

Да верно сказано, но если какие то формулы срабатываются и верен ответ (при переносе формул с других файлов в этот). То значится все верно, единственное править приходится меняя диапазоны.
Возможно (да скорее всего) я уверен что мое мнение ошибочно.
В виду этого я обратился за помощью потому как просто устал с этим перебором и подбором (((

Хотя есть и плюсы, выявляю ошибки )))) а также учусь одновременно с Вашей помощью

Я поправил формулу:
Код
ЕСЛИ(МЕСЯЦ(T3)=МЕСЯЦ(S3);G7;ИНДЕКС(ДВССЫЛ(ТЕКСТ(КОНМЕСЯЦА(Z7;0)-T1;"ММММ")&"!G7:G68");ДЕНЬ(T3))

Но не могу понять почему же он ответ берет 371 с ячейки G8 а не с ячейки G10 (с листа Июль)


Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Четверг, 30.08.2018, 10:53
 
Ответить
Сообщение_Boroda_,
Александр, как я понимаю почему +1, потому как к дате прибавить месяц (а получается что я прибавляю лишь один день %) )
Цитата
Но даже так - эта формула была написана для совершенно другого файла, где даты идут подряд. Ее нельзя просто перенести в этот (пусть и поменяв диапазоны). Нужно менять и саму формулу

Да верно сказано, но если какие то формулы срабатываются и верен ответ (при переносе формул с других файлов в этот). То значится все верно, единственное править приходится меняя диапазоны.
Возможно (да скорее всего) я уверен что мое мнение ошибочно.
В виду этого я обратился за помощью потому как просто устал с этим перебором и подбором (((

Хотя есть и плюсы, выявляю ошибки )))) а также учусь одновременно с Вашей помощью

Я поправил формулу:
Код
ЕСЛИ(МЕСЯЦ(T3)=МЕСЯЦ(S3);G7;ИНДЕКС(ДВССЫЛ(ТЕКСТ(КОНМЕСЯЦА(Z7;0)-T1;"ММММ")&"!G7:G68");ДЕНЬ(T3))

Но не могу понять почему же он ответ берет 371 с ячейки G8 а не с ячейки G10 (с листа Июль)

Автор - lebensvoll
Дата добавления - 30.08.2018 в 10:46
_Boroda_ Дата: Четверг, 30.08.2018, 11:00 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Потому, что Т3 - второе число. ДЕНЬ = 2. Вторая ячейка диапазона G7:G68 - это ячейка G8
И разве нужно из G10, а не из G9?
Код
ДЕНЬ(T3)*2-1


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПотому, что Т3 - второе число. ДЕНЬ = 2. Вторая ячейка диапазона G7:G68 - это ячейка G8
И разве нужно из G10, а не из G9?
Код
ДЕНЬ(T3)*2-1

Автор - _Boroda_
Дата добавления - 30.08.2018 в 11:00
lebensvoll Дата: Четверг, 30.08.2018, 11:19 | Сообщение № 6
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
_Boroda_,
Александр, вот именно в чем тут проблема так это в ОДНОЙ ДАТЕ (как бы два полудня, примерно с 9:00 по 18:00 и с 18:00 по 24:00 "примерно")
в связи с этим и нужно чтоб нашлось значение из столбца G с листа Июль
[img][/img]
Тогда получается что минус 1 не нужно ставить, и ответ верен 278
Код
ДЕНЬ(T3)*2

Но, вопрос!?
А если будет указан другой период (к примеру 4.07.2018) то тоже все верно 284....
Цитата
Ее нельзя просто перенести в этот (пусть и поменяв диапазоны). Нужно менять и саму формулу

А вы говорили...
Да вы ГЕНИИ уже все помогли и ответ и правка всегда на поверхности, но она очень туго доходит до моего подсознания иногда

СПАСИБО ВАМ АЛЕКСАНДР!!!!


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщение_Boroda_,
Александр, вот именно в чем тут проблема так это в ОДНОЙ ДАТЕ (как бы два полудня, примерно с 9:00 по 18:00 и с 18:00 по 24:00 "примерно")
в связи с этим и нужно чтоб нашлось значение из столбца G с листа Июль
[img][/img]
Тогда получается что минус 1 не нужно ставить, и ответ верен 278
Код
ДЕНЬ(T3)*2

Но, вопрос!?
А если будет указан другой период (к примеру 4.07.2018) то тоже все верно 284....
Цитата
Ее нельзя просто перенести в этот (пусть и поменяв диапазоны). Нужно менять и саму формулу

А вы говорили...
Да вы ГЕНИИ уже все помогли и ответ и правка всегда на поверхности, но она очень туго доходит до моего подсознания иногда

СПАСИБО ВАМ АЛЕКСАНДР!!!!

Автор - lebensvoll
Дата добавления - 30.08.2018 в 11:19
lebensvoll Дата: Четверг, 30.08.2018, 13:03 | Сообщение № 7
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
_Boroda_,
Александр!!!
Начинаю применять эту формулу в ячейку W3 "циклическая ссылка" :'(
начинаю разбивать формулу на куски все верно рассчитывает. Начинаю формулы слепливать в единую :'(
ААААААААААААААААА
Даже если я и делаю промежуточные расчёты слева в ячейках а потом слепливаю ответ из полученных данных в ячейку W3 то вновь "циклическая ссылка" :'(
Жаль что не могу целый файл с формулами выложить ((((
Как же произвести расчет!?
Помогите люди добрые


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщение_Boroda_,
Александр!!!
Начинаю применять эту формулу в ячейку W3 "циклическая ссылка" :'(
начинаю разбивать формулу на куски все верно рассчитывает. Начинаю формулы слепливать в единую :'(
ААААААААААААААААА
Даже если я и делаю промежуточные расчёты слева в ячейках а потом слепливаю ответ из полученных данных в ячейку W3 то вновь "циклическая ссылка" :'(
Жаль что не могу целый файл с формулами выложить ((((
Как же произвести расчет!?
Помогите люди добрые

Автор - lebensvoll
Дата добавления - 30.08.2018 в 13:03
_Boroda_ Дата: Четверг, 30.08.2018, 13:13 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А я Вам об этом еще вчера в личке писал
Цитата
но тогда получается циклическая ссылка. Нужно другие формулы переписывать.
Там у Вас разбираться много, а я сейчас не могу, занят


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА я Вам об этом еще вчера в личке писал
Цитата
но тогда получается циклическая ссылка. Нужно другие формулы переписывать.
Там у Вас разбираться много, а я сейчас не могу, занят

Автор - _Boroda_
Дата добавления - 30.08.2018 в 13:13
lebensvoll Дата: Четверг, 30.08.2018, 13:25 | Сообщение № 9
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
_Boroda_,
Я думал пока Вы заняты, смогут другие помочь ((((
Но я скорее всего доходит до меня почему
В столбце О должно цепляться значение которое мы сейчас пытаемся рассчитать ((((
Как меня достал уже этот файл ((((


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

Автор - lebensvoll
Дата добавления - 30.08.2018 в 13:25
Мир MS Excel » Вопросы и решения » Вопросы по Excel » поиск значения согласно дате с переходом на другой лист (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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