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

Вход

Регистрация

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

 

= Мир MS Excel/"Укоротить" формулу. - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » "Укоротить" формулу. (необходима помощь.)
"Укоротить" формулу.
SkyPro Дата: Воскресенье, 28.04.2013, 16:29 | Сообщение № 1
Группа: Друзья
Ранг: Старожил
Сообщений: 1206
Репутация: 255 ±
Замечаний: 0% ±

2010
Добрый день, уважаемые.
"накатал" формулу, но получилась она запредельной длинны.
Можно ли её как-нибудь укоротить, что бы она не потеряла свой функционал?
Код
=ЕСЛИОШИБКА(ЕСЛИ(И(A2>=ИНДЕКС($F$2:$F$12;ПОИСКПОЗ(A2;$F$2:$F$12;1);1);A2<=ИНДЕКС($G$2:$G$12;ПОИСКПОЗ(A2;$G$2:$G$12;1)+1;1);LOG(ЕСЛИОШИБКА(ЕСЛИ(И(A2>=ИНДЕКС($F$2:$F$12;ПОИСКПОЗ(A2;$F$2:$F$12;1);1);A2<=ИНДЕКС($G$2:$G$12;ПОИСКПОЗ(A2;$G$2:$G$12;1)+1;1));B2-ИНДЕКС($F$2:$H$12;ПОИСКПОЗ(A2;$F$2:$F$12;1);3);"");"")));B2-ИНДЕКС($F$2:$H$12;ПОИСКПОЗ(A2;$F$2:$F$12;1);3);"");"")


Формула находит диапазон, в который попадает значение А2, и вычисляет разницу В2 и соответствующего значения из таблицы F2:H12.

наглядно в вложении.

Заранее спасибо за помощь smile
К сообщению приложен файл: primer.xlsx (10.6 Kb)

skypro1111@gmail.com
 
Ответить
СообщениеДобрый день, уважаемые.
"накатал" формулу, но получилась она запредельной длинны.
Можно ли её как-нибудь укоротить, что бы она не потеряла свой функционал?
Код
=ЕСЛИОШИБКА(ЕСЛИ(И(A2>=ИНДЕКС($F$2:$F$12;ПОИСКПОЗ(A2;$F$2:$F$12;1);1);A2<=ИНДЕКС($G$2:$G$12;ПОИСКПОЗ(A2;$G$2:$G$12;1)+1;1);LOG(ЕСЛИОШИБКА(ЕСЛИ(И(A2>=ИНДЕКС($F$2:$F$12;ПОИСКПОЗ(A2;$F$2:$F$12;1);1);A2<=ИНДЕКС($G$2:$G$12;ПОИСКПОЗ(A2;$G$2:$G$12;1)+1;1));B2-ИНДЕКС($F$2:$H$12;ПОИСКПОЗ(A2;$F$2:$F$12;1);3);"");"")));B2-ИНДЕКС($F$2:$H$12;ПОИСКПОЗ(A2;$F$2:$F$12;1);3);"");"")


Формула находит диапазон, в который попадает значение А2, и вычисляет разницу В2 и соответствующего значения из таблицы F2:H12.

наглядно в вложении.

Заранее спасибо за помощь smile
Автор - SkyPro
Дата добавления - 28.04.2013 в 16:29
Serge_007 Дата: Воскресенье, 28.04.2013, 16:42 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Код
=B2-ВПР(A2;F$2:H$12;3)


С проверкой на ошибку:
для всех версий:
Код
=ЕСЛИ(ЕНД(ВПР(A2;F$2:H$12;3));"";B2-ВПР(A2;F$2:H$12;3))

для версий начиная с 2007:
Код
=ЕСЛИОШИБКА(B2-ВПР(A2;F$2:H$12;3);"")

Плюс - скрытие значения ошибки форматированием (см. вложение)
К сообщению приложен файл: SkyPro.xls (35.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Код
=B2-ВПР(A2;F$2:H$12;3)


С проверкой на ошибку:
для всех версий:
Код
=ЕСЛИ(ЕНД(ВПР(A2;F$2:H$12;3));"";B2-ВПР(A2;F$2:H$12;3))

для версий начиная с 2007:
Код
=ЕСЛИОШИБКА(B2-ВПР(A2;F$2:H$12;3);"")

Плюс - скрытие значения ошибки форматированием (см. вложение)
Автор - Serge_007
Дата добавления - 28.04.2013 в 16:42
SkyPro Дата: Воскресенье, 28.04.2013, 16:52 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1206
Репутация: 255 ±
Замечаний: 0% ±

2010
biggrin

Спасибо, огромное.
Как-то даже не подумал, что впр поможет..

ЗЫ:
А как отсеять отрицательные значения? Я использовал ЕСЛИОШИБКА(LOG(...))

skypro1111@gmail.com

Сообщение отредактировал SkyPro - Воскресенье, 28.04.2013, 16:57
 
Ответить
Сообщениеbiggrin

Спасибо, огромное.
Как-то даже не подумал, что впр поможет..

ЗЫ:
А как отсеять отрицательные значения? Я использовал ЕСЛИОШИБКА(LOG(...))
Автор - SkyPro
Дата добавления - 28.04.2013 в 16:52
Serge_007 Дата: Воскресенье, 28.04.2013, 17:02 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Нарисуйте в примере в каких случаях возникает проблема с отрицательными значениями

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеНарисуйте в примере в каких случаях возникает проблема с отрицательными значениями
Автор - Serge_007
Дата добавления - 28.04.2013 в 17:02
SkyPro Дата: Воскресенье, 28.04.2013, 17:08 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1206
Репутация: 255 ±
Замечаний: 0% ±

2010
Проблемы нет smile
просто нужно отсеять отрицательные значения (не отображать)

Справился таким образом:
Код
=ЕСЛИОШИБКА(ЕСЛИ(И(K2-ВПР(H2;$T$2:$V$12;3);LOG(K2-ВПР(H2;$T$2:$V$12;3)));K2-ВПР(H2;$T$2:$V$12;3);"");"")
К сообщению приложен файл: 8587848.xlsx (12.1 Kb)

skypro1111@gmail.com
 
Ответить
СообщениеПроблемы нет smile
просто нужно отсеять отрицательные значения (не отображать)

Справился таким образом:
Код
=ЕСЛИОШИБКА(ЕСЛИ(И(K2-ВПР(H2;$T$2:$V$12;3);LOG(K2-ВПР(H2;$T$2:$V$12;3)));K2-ВПР(H2;$T$2:$V$12;3);"");"")
Автор - SkyPro
Дата добавления - 28.04.2013 в 17:08
Pelena Дата: Воскресенье, 28.04.2013, 17:17 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 19407
Репутация: 4556 ±
Замечаний: ±

Excel 365 & Mac Excel
Сергей, по-моему, граничные значения (6:00, 7:00) неверно считаются.
Можно так скорректировать
Код
=B2-ВПР(A2+1/2880;$F$2:$H$12;3)


SkyPro, чтобы скрыть отрицательные значения, можно установить пользовательский формат [мм];;

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеСергей, по-моему, граничные значения (6:00, 7:00) неверно считаются.
Можно так скорректировать
Код
=B2-ВПР(A2+1/2880;$F$2:$H$12;3)


SkyPro, чтобы скрыть отрицательные значения, можно установить пользовательский формат [мм];;
Автор - Pelena
Дата добавления - 28.04.2013 в 17:17
Serge_007 Дата: Воскресенье, 28.04.2013, 17:18 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (SkyPro)
нужно отсеять отрицательные значения (не отображать)

Код
=ЕСЛИ(ВПР(A2;F$2:H$12;3)<B2;B2-ВПР(A2;F$2:H$12;3);"")

Или как Елена предложила - форматом

Цитата (Pelena)
по-моему, граничные значения (6:00, 7:00) неверно считаются

Что не так?

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (SkyPro)
нужно отсеять отрицательные значения (не отображать)

Код
=ЕСЛИ(ВПР(A2;F$2:H$12;3)<B2;B2-ВПР(A2;F$2:H$12;3);"")

Или как Елена предложила - форматом

Цитата (Pelena)
по-моему, граничные значения (6:00, 7:00) неверно считаются

Что не так?
Автор - Serge_007
Дата добавления - 28.04.2013 в 17:18
Pelena Дата: Воскресенье, 28.04.2013, 17:25 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 19407
Репутация: 4556 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата (Serge_007)
Что не так?

Для значения 7:00 берется 30, а надо 55

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата (Serge_007)
Что не так?

Для значения 7:00 берется 30, а надо 55
Автор - Pelena
Дата добавления - 28.04.2013 в 17:25
Serge_007 Дата: Воскресенье, 28.04.2013, 17:36 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (Pelena)
Для значения 7:00 берется 30, а надо 55

Интересно! Для 4:00, 5:00, 6:00 - правильно, а для 7:00 и 8:00 - нет

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (Pelena)
Для значения 7:00 берется 30, а надо 55

Интересно! Для 4:00, 5:00, 6:00 - правильно, а для 7:00 и 8:00 - нет
Автор - Serge_007
Дата добавления - 28.04.2013 в 17:36
SkyPro Дата: Воскресенье, 28.04.2013, 17:43 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1206
Репутация: 255 ±
Замечаний: 0% ±

2010
А если "протянуть", то правильно отображается.
К сообщению приложен файл: 9696185.xlsx (10.7 Kb)

skypro1111@gmail.com

Сообщение отредактировал SkyPro - Воскресенье, 28.04.2013, 17:51
 
Ответить
СообщениеА если "протянуть", то правильно отображается.
Автор - SkyPro
Дата добавления - 28.04.2013 в 17:43
Serge_007 Дата: Воскресенье, 28.04.2013, 17:51 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (Pelena)
Для значения 7:00 берется 30, а надо 55
Хотя как посмотреть
К сообщению приложен файл: SkyPro_2.xls (32.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (Pelena)
Для значения 7:00 берется 30, а надо 55
Хотя как посмотреть
Автор - Serge_007
Дата добавления - 28.04.2013 в 17:51
Pelena Дата: Воскресенье, 28.04.2013, 18:33 | Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 19407
Репутация: 4556 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата (Serge_007)
Хотя как посмотреть

Ни-че-го не понимаю, в чём прикол-то?

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата (Serge_007)
Хотя как посмотреть

Ни-че-го не понимаю, в чём прикол-то?
Автор - Pelena
Дата добавления - 28.04.2013 в 18:33
SkyPro Дата: Воскресенье, 28.04.2013, 18:42 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1206
Репутация: 255 ±
Замечаний: 0% ±

2010
У меня тоже глюки были какие-то smile
Пересоздал весь файл и заработало.

skypro1111@gmail.com
 
Ответить
СообщениеУ меня тоже глюки были какие-то smile
Пересоздал весь файл и заработало.
Автор - SkyPro
Дата добавления - 28.04.2013 в 18:42
SkyPro Дата: Понедельник, 29.04.2013, 13:09 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1206
Репутация: 255 ±
Замечаний: 0% ±

2010
Все-таки ВПР с аргументом "истина" работает криво.

skypro1111@gmail.com
 
Ответить
СообщениеВсе-таки ВПР с аргументом "истина" работает криво.
Автор - SkyPro
Дата добавления - 29.04.2013 в 13:09
Serge_007 Дата: Понедельник, 29.04.2013, 13:39 | Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Это из-за проблем с округлением
Что бы считало правильно - прибавьте к искомому ничтожно малую величину, например 1E-15:
Код
=ВПР(A2+1E-15;F$2:H$12;3)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеЭто из-за проблем с округлением
Что бы считало правильно - прибавьте к искомому ничтожно малую величину, например 1E-15:
Код
=ВПР(A2+1E-15;F$2:H$12;3)
Автор - Serge_007
Дата добавления - 29.04.2013 в 13:39
M73568 Дата: Понедельник, 29.04.2013, 13:43 | Сообщение № 16
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 197
Репутация: 46 ±
Замечаний: 0% ±

2007-2013
Цитата (SkyPro)
Все-таки ВПР с аргументом "истина" работает криво.

Может потому что - цитата из справки
Цитата
Значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке, иначе функция ВПР может возвратить неправильный результат.
 
Ответить
Сообщение
Цитата (SkyPro)
Все-таки ВПР с аргументом "истина" работает криво.

Может потому что - цитата из справки
Цитата
Значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке, иначе функция ВПР может возвратить неправильный результат.
Автор - M73568
Дата добавления - 29.04.2013 в 13:43
Serge_007 Дата: Понедельник, 29.04.2013, 13:44 | Сообщение № 17
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (M73568)
Значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке
Они так и расположены

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (M73568)
Значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке
Они так и расположены
Автор - Serge_007
Дата добавления - 29.04.2013 в 13:44
SkyPro Дата: Понедельник, 29.04.2013, 14:35 | Сообщение № 18
Группа: Друзья
Ранг: Старожил
Сообщений: 1206
Репутация: 255 ±
Замечаний: 0% ±

2010
В оригинальной таблице, искомое значение получается от разницы двух других ячеек. И, как не странно, но заменив искомое значение на разницу - формула заработала правильно.
Код
=ВПР(G2-F2;T$2:V$12;3)

skypro1111@gmail.com
 
Ответить
СообщениеВ оригинальной таблице, искомое значение получается от разницы двух других ячеек. И, как не странно, но заменив искомое значение на разницу - формула заработала правильно.
Код
=ВПР(G2-F2;T$2:V$12;3)
Автор - SkyPro
Дата добавления - 29.04.2013 в 14:35
Serge_007 Дата: Понедельник, 29.04.2013, 14:52 | Сообщение № 19
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (SkyPro)
как не странно, но заменив искомое значение на разницу - формула заработала правильно
Вероятно двойное округление даёт искомое значение на доли секунды большее чем просматриваемое

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (SkyPro)
как не странно, но заменив искомое значение на разницу - формула заработала правильно
Вероятно двойное округление даёт искомое значение на доли секунды большее чем просматриваемое
Автор - Serge_007
Дата добавления - 29.04.2013 в 14:52
Мир MS Excel » Вопросы и решения » Вопросы по Excel » "Укоротить" формулу. (необходима помощь.)
  • Страница 1 из 1
  • 1
Поиск:

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