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

Вход

Регистрация

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

 

= Мир MS Excel/Несколько условий в if (аналог Select Case в VBA) - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Готовые решения » Несколько условий в if (аналог Select Case в VBA) (Excel)
Несколько условий в if (аналог Select Case в VBA)
PATRI0T Дата: Понедельник, 03.08.2020, 15:04 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 57
Репутация: 3 ±
Замечаний: 0% ±

Excel 2013
Почему-то нигде такого не встречал, а сам давно пользуюсь.
Поэтому вот, представляю на суд общественности мое решение.
==========================================

Везде, и в рунете, и на англ. сайтах я видел, что народ советует для нескольких условий формулы с вложенными "ЕСЛИ".
Но увы, когда условий больше 3х это превращается в ужасную кашу со скобками.
Например, вот формула, где, в зависимости от вхождения числа в какой-то диапазон, возвращается число:
Доступно только для пользователей
Сразу видно, что тут сложно легко запутаться в скобочках. Еще сложнее добавить еще одно условие, и при этом ничего не сломать

Я же вместо этого предпочитаю склейку из формул.
Например, вот, та же формула, но моим способом:

[vba]
Код
=ЕСЛИ(B2<0,9;0;"")
&ЕСЛИ(И(B2>=0,9;B2<1);0,1;"")
&ЕСЛИ(B2=1;0,2;"")
&ЕСЛИ(B2>1;0,3;"")
[/vba]

Это набор из нескольких формул "ЕСЛИ", каждая из которых возвращает число, если условие истинно, или пустую строку, если ложь.
Все возвращенные строки соединяются в одну с помощью символа & (объединение строк).

Получается, если мы правильно написали условия, у нас будет соединение пустых строк и одного числа => остается одно это число.
Нужно помнить, что полученное таким способом число будет сохранено в текстовом формате, и мы не сможем его исползовать в арифметических формулах. Чтобы этого избежать, я оборачиваю всю конструкцию в =Знач(). Эта формула преобразует текст в число.

Вот еще более сложный пример.
Нужно получить Строку на основании входных данных.
[vba]
Код
=ЕСЛИ([@[Тип Заказа]]="Столы";"Столы";"")
&ЕСЛИ([@[Тип Заказа]]="Фасад";"Фасад";"")
&ЕСЛИ([@[Тип Заказа]]="Детали";"Детали";"")
&ЕСЛИ([@[Тип Заказа]]="Отделка";"Отделка";"")
&ЕСЛИ([@[Тип Заказа]]="Рекламация";"Рекламация";"")
&ЕСЛИ(И(
[@[Тип Заказа]]="Кухни";
НЕ(ЕСЛИ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат,МДФ"));0;1)));
"КухниМассив";"")
&ЕСЛИ(И(
[@[Тип Заказа]]="Мебель";
НЕ(ЕСЛИ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат"));0;1)));
"МебельМассив";"")
&ЕСЛИ(И(
ЕСЛИ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат"));0;1));
"КухниПластик";"")
&ЕСЛИ(И(
[@[Тип Заказа]]="Кухни";
[@Материал]="МДФ");
"КухниМДФ";"")
[/vba]
Разумеется, главный минус такого решения, - можно ошибиться в условиях и получить склеенный текст, если две или больше формул окажутся одновременно истинными. Как раз, рожая эту формулу, я около 20 минут потратил на её отладку.
но я боюсь даже представить, как бы выглядела формула, если ее писать стандартным подходом с вложенными "ЕСЛИ".

PS. Говорите, что думаете по этому поводу, предлагайте свои варианты. Но я за все время использования (около 3хлет) не нашел недостатков у такого подхода.
PPS. Простите, без форматирования. форматтеру почему-то не нравились переводы строки в формулах, а без них все ужасно выглядит.


Сообщение отредактировал PATRI0T - Понедельник, 03.08.2020, 17:07
 
Ответить
СообщениеПочему-то нигде такого не встречал, а сам давно пользуюсь.
Поэтому вот, представляю на суд общественности мое решение.
==========================================

Везде, и в рунете, и на англ. сайтах я видел, что народ советует для нескольких условий формулы с вложенными "ЕСЛИ".
Но увы, когда условий больше 3х это превращается в ужасную кашу со скобками.
Например, вот формула, где, в зависимости от вхождения числа в какой-то диапазон, возвращается число:
Доступно только для пользователей
Сразу видно, что тут сложно легко запутаться в скобочках. Еще сложнее добавить еще одно условие, и при этом ничего не сломать

Я же вместо этого предпочитаю склейку из формул.
Например, вот, та же формула, но моим способом:

[vba]
Код
=ЕСЛИ(B2<0,9;0;"")
&ЕСЛИ(И(B2>=0,9;B2<1);0,1;"")
&ЕСЛИ(B2=1;0,2;"")
&ЕСЛИ(B2>1;0,3;"")
[/vba]

Это набор из нескольких формул "ЕСЛИ", каждая из которых возвращает число, если условие истинно, или пустую строку, если ложь.
Все возвращенные строки соединяются в одну с помощью символа & (объединение строк).

Получается, если мы правильно написали условия, у нас будет соединение пустых строк и одного числа => остается одно это число.
Нужно помнить, что полученное таким способом число будет сохранено в текстовом формате, и мы не сможем его исползовать в арифметических формулах. Чтобы этого избежать, я оборачиваю всю конструкцию в =Знач(). Эта формула преобразует текст в число.

Вот еще более сложный пример.
Нужно получить Строку на основании входных данных.
[vba]
Код
=ЕСЛИ([@[Тип Заказа]]="Столы";"Столы";"")
&ЕСЛИ([@[Тип Заказа]]="Фасад";"Фасад";"")
&ЕСЛИ([@[Тип Заказа]]="Детали";"Детали";"")
&ЕСЛИ([@[Тип Заказа]]="Отделка";"Отделка";"")
&ЕСЛИ([@[Тип Заказа]]="Рекламация";"Рекламация";"")
&ЕСЛИ(И(
[@[Тип Заказа]]="Кухни";
НЕ(ЕСЛИ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат,МДФ"));0;1)));
"КухниМассив";"")
&ЕСЛИ(И(
[@[Тип Заказа]]="Мебель";
НЕ(ЕСЛИ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат"));0;1)));
"МебельМассив";"")
&ЕСЛИ(И(
ЕСЛИ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат"));0;1));
"КухниПластик";"")
&ЕСЛИ(И(
[@[Тип Заказа]]="Кухни";
[@Материал]="МДФ");
"КухниМДФ";"")
[/vba]
Разумеется, главный минус такого решения, - можно ошибиться в условиях и получить склеенный текст, если две или больше формул окажутся одновременно истинными. Как раз, рожая эту формулу, я около 20 минут потратил на её отладку.
но я боюсь даже представить, как бы выглядела формула, если ее писать стандартным подходом с вложенными "ЕСЛИ".

PS. Говорите, что думаете по этому поводу, предлагайте свои варианты. Но я за все время использования (около 3хлет) не нашел недостатков у такого подхода.
PPS. Простите, без форматирования. форматтеру почему-то не нравились переводы строки в формулах, а без них все ужасно выглядит.

Автор - PATRI0T
Дата добавления - 03.08.2020 в 15:04
китин Дата: Понедельник, 03.08.2020, 15:44 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 6319
Репутация: 970 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
PATRI0T, формулы надо не под спойлер прятать, а оформлять тэгами. Не первый же день на форуме
исправляйте


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
СообщениеPATRI0T, формулы надо не под спойлер прятать, а оформлять тэгами. Не первый же день на форуме
исправляйте

Автор - китин
Дата добавления - 03.08.2020 в 15:44
Hugo Дата: Понедельник, 03.08.2020, 16:14 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3131
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
[vba]
Код
=ЕСЛИ(B2<0,9;0;
  ЕСЛИ(B2<0,95;0,1;
  ЕСЛИ(B2=1;0,2;
                    0,3
                    )))
[/vba]
как по мне так вполне так удобно.
Только тут пробелы поплыли, у меня вполне всё ровно, ща скрин сделаю.
Вот: https://u.to/f6M9GQ
Хотя конечно если делать сложнее - то с амперсандом удобно получается, спасибо.


excel@nxt.ru
webmoney: R418926282008 Z422237915069


Сообщение отредактировал Hugo - Понедельник, 03.08.2020, 16:21
 
Ответить
Сообщение[vba]
Код
=ЕСЛИ(B2<0,9;0;
  ЕСЛИ(B2<0,95;0,1;
  ЕСЛИ(B2=1;0,2;
                    0,3
                    )))
[/vba]
как по мне так вполне так удобно.
Только тут пробелы поплыли, у меня вполне всё ровно, ща скрин сделаю.
Вот: https://u.to/f6M9GQ
Хотя конечно если делать сложнее - то с амперсандом удобно получается, спасибо.

Автор - Hugo
Дата добавления - 03.08.2020 в 16:14
Pelena Дата: Понедельник, 03.08.2020, 17:53 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 16779
Репутация: 3621 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Это такая визуальная замена новых функций ЕСЛИМН() и ПЕРЕКЛЮЧ(), которые доступны при подписке на офис 365


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЭто такая визуальная замена новых функций ЕСЛИМН() и ПЕРЕКЛЮЧ(), которые доступны при подписке на офис 365

Автор - Pelena
Дата добавления - 03.08.2020 в 17:53
Gustav Дата: Вторник, 04.08.2020, 13:51 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1826
Репутация: 726 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
&ЕСЛИ(И(
[@[Тип Заказа]]="Мебель";
НЕ(ЕСЛИ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат"));0;1)));
"МебельМассив";"")
&ЕСЛИ(И(
ЕСЛИ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат"));0;1));
"КухниПластик";"")


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

Смотрите, фрагмент:
Код
НЕ(ЕСЛИ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат,МДФ"));0;1))

, суть которого (когда, наконец, не с ходу разберешься - а думать о возможном сопровождении формулы не Вами нужно всегда!) - "Материал НЕ ВХОДИТ в Ряд значений", может быть заменен явно более прозрачным фрагментом:
Код
ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат,МДФ"))


Соответственно, при обратной ситуации, когда "Материал ВХОДИТ в Ряд значений" фрагмент:
Код
ЕСЛИ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат"));0;1)

можно заменить отрицанием предыдущей замены:
Код
НЕ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат,МДФ")))


Либо еще более наглядно, когда функция ЕЧИСЛО становится синонимом "ВХОДИТ в Ряд":
Код
ЕЧИСЛО(ПОИСК([@Материал];"Пластик,Ламинат,МДФ"))

И, наоборот, "НЕ ВХОДИТ в Ряд" - причем особенно наглядно именно наличие частицы НЕ - и в формуле, и в сути:
Код
НЕ(ЕЧИСЛО(ПОИСК([@Материал];"Пластик,Ламинат,МДФ")))


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
&ЕСЛИ(И(
[@[Тип Заказа]]="Мебель";
НЕ(ЕСЛИ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат"));0;1)));
"МебельМассив";"")
&ЕСЛИ(И(
ЕСЛИ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат"));0;1));
"КухниПластик";"")


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

Смотрите, фрагмент:
Код
НЕ(ЕСЛИ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат,МДФ"));0;1))

, суть которого (когда, наконец, не с ходу разберешься - а думать о возможном сопровождении формулы не Вами нужно всегда!) - "Материал НЕ ВХОДИТ в Ряд значений", может быть заменен явно более прозрачным фрагментом:
Код
ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат,МДФ"))


Соответственно, при обратной ситуации, когда "Материал ВХОДИТ в Ряд значений" фрагмент:
Код
ЕСЛИ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат"));0;1)

можно заменить отрицанием предыдущей замены:
Код
НЕ(ЕОШ(ПОИСК([@Материал];"Пластик,Ламинат,МДФ")))


Либо еще более наглядно, когда функция ЕЧИСЛО становится синонимом "ВХОДИТ в Ряд":
Код
ЕЧИСЛО(ПОИСК([@Материал];"Пластик,Ламинат,МДФ"))

И, наоборот, "НЕ ВХОДИТ в Ряд" - причем особенно наглядно именно наличие частицы НЕ - и в формуле, и в сути:
Код
НЕ(ЕЧИСЛО(ПОИСК([@Материал];"Пластик,Ламинат,МДФ")))

Автор - Gustav
Дата добавления - 04.08.2020 в 13:51
Gustav Дата: Вторник, 04.08.2020, 14:02 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1826
Репутация: 726 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
ЕСЛИ([@[Тип Заказа]]="Столы";"Столы";"")
&ЕСЛИ([@[Тип Заказа]]="Фасад";"Фасад";"")
&ЕСЛИ([@[Тип Заказа]]="Детали";"Детали";"")
&ЕСЛИ([@[Тип Заказа]]="Отделка";"Отделка";"")
&ЕСЛИ([@[Тип Заказа]]="Рекламация";"Рекламация";"")

Ну, а здесь можно:
Код
ЕСЛИ(ЕНД(ПОИСКПОЗ([@[Тип Заказа]];{"Столы";"Фасад";"Детали";"Отделка";"Рекламация"};));"";[@[Тип Заказа]])

либо еще короче:
Код
ЕСЛИОШИБКА(ВПР([@[Тип Заказа]];{"Столы":"Фасад":"Детали":"Отделка":"Рекламация"};1;);"")


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
ЕСЛИ([@[Тип Заказа]]="Столы";"Столы";"")
&ЕСЛИ([@[Тип Заказа]]="Фасад";"Фасад";"")
&ЕСЛИ([@[Тип Заказа]]="Детали";"Детали";"")
&ЕСЛИ([@[Тип Заказа]]="Отделка";"Отделка";"")
&ЕСЛИ([@[Тип Заказа]]="Рекламация";"Рекламация";"")

Ну, а здесь можно:
Код
ЕСЛИ(ЕНД(ПОИСКПОЗ([@[Тип Заказа]];{"Столы";"Фасад";"Детали";"Отделка";"Рекламация"};));"";[@[Тип Заказа]])

либо еще короче:
Код
ЕСЛИОШИБКА(ВПР([@[Тип Заказа]];{"Столы":"Фасад":"Детали":"Отделка":"Рекламация"};1;);"")

Автор - Gustav
Дата добавления - 04.08.2020 в 14:02
PATRI0T Дата: Четверг, 06.08.2020, 12:18 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 57
Репутация: 3 ±
Замечаний: 0% ±

Excel 2013
О, спасибо за советы. узнал для себя пару новых формул и приемов (ЕНД, массив на лету, применение формулы ЕЧИСЛО)

 
Ответить
СообщениеО, спасибо за советы. узнал для себя пару новых формул и приемов (ЕНД, массив на лету, применение формулы ЕЧИСЛО)


Автор - PATRI0T
Дата добавления - 06.08.2020 в 12:18
laya Дата: Понедельник, 14.09.2020, 19:13 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
полезно.
 
Ответить
Сообщениеполезно.

Автор - laya
Дата добавления - 14.09.2020 в 19:13
Мир MS Excel » Вопросы и решения » Готовые решения » Несколько условий в if (аналог Select Case в VBA) (Excel)
  • Страница 1 из 1
  • 1
Поиск:

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