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

Вход

Регистрация

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

 

= Мир MS Excel/Извлечение числа по условию - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Извлечение числа по условию (Формулы/Formulas)
Извлечение числа по условию
Shtein Дата: Вторник, 10.12.2019, 11:46 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
Добрый день всем.
Суть задачи: необходимо, чтобы формула в столбце Н подтягивала согласно числа в столбце I число из столбца А.
Условия такие:
1) если число в столбце I меньше 2000, то подтягивать 100
2) если число в столбце I в диапазоне 2000 - 3999, то подтягивать 200
3) если число в столбце I в диапазоне 4000 - 6999, то подтягивать 300
4) если число в столбце I в диапазоне 6000 - 10999, то подтягивать 400
5) если число в столбце I 11000 и больше, то подтягивать 500

я решил задачу путем вложенных формул ЕСЛИ
Код

=ЕСЛИ(I2<$B$2;$A$2;ЕСЛИ(I2<$B$3;$A$3;ЕСЛИ(I2<$B$4;$A$4;ЕСЛИ(I2<$B$5;$A$5; 500))))


Но мне кажется, это можно решить формулами СУММЕСЛИ или СУММЕСЛИМН. Конечно, есть наверняка много других способов, я думал, что эти формулы проще и удобнее всего.
К сообщению приложен файл: example_1.xlsx (13.1 Kb)

"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.

Сообщение отредактировал Shtein - Вторник, 10.12.2019, 12:13
 
Ответить
СообщениеДобрый день всем.
Суть задачи: необходимо, чтобы формула в столбце Н подтягивала согласно числа в столбце I число из столбца А.
Условия такие:
1) если число в столбце I меньше 2000, то подтягивать 100
2) если число в столбце I в диапазоне 2000 - 3999, то подтягивать 200
3) если число в столбце I в диапазоне 4000 - 6999, то подтягивать 300
4) если число в столбце I в диапазоне 6000 - 10999, то подтягивать 400
5) если число в столбце I 11000 и больше, то подтягивать 500

я решил задачу путем вложенных формул ЕСЛИ
Код

=ЕСЛИ(I2<$B$2;$A$2;ЕСЛИ(I2<$B$3;$A$3;ЕСЛИ(I2<$B$4;$A$4;ЕСЛИ(I2<$B$5;$A$5; 500))))


Но мне кажется, это можно решить формулами СУММЕСЛИ или СУММЕСЛИМН. Конечно, есть наверняка много других способов, я думал, что эти формулы проще и удобнее всего.
Автор - Shtein
Дата добавления - 10.12.2019 в 11:46
bmv98rus Дата: Вторник, 10.12.2019, 11:49 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Например
Код
=LOOKUP(I2;{0;2;4;6;11}*1000;{1;2;3;4;5}*100)

Код
LOOKUP(I2%;{0;2;4;6;11}*10;{1;2;3;4;5}*100)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 10.12.2019, 11:50
 
Ответить
СообщениеНапример
Код
=LOOKUP(I2;{0;2;4;6;11}*1000;{1;2;3;4;5}*100)

Код
LOOKUP(I2%;{0;2;4;6;11}*10;{1;2;3;4;5}*100)
Автор - bmv98rus
Дата добавления - 10.12.2019 в 11:49
bigor Дата: Вторник, 10.12.2019, 11:53 | Сообщение № 3
Группа: Проверенные
Ранг: Старожил
Сообщений: 1402
Репутация: 275 ±
Замечаний: 0% ±

нет
Можно так:

Код
=ВПР(I2;{0;100:2000;200:4000;300:7000;400:11000;500};2)
 
Ответить
СообщениеМожно так:

Код
=ВПР(I2;{0;100:2000;200:4000;300:7000;400:11000;500};2)
Автор - bigor
Дата добавления - 10.12.2019 в 11:53
_Boroda_ Дата: Вторник, 10.12.2019, 12:01 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 16956
Репутация: 6630 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Shtein, - Прочитайте Правила форума
- Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)

Помогающие, особенно лохматые, что, опять ругаться будем?
В темах с нарушениями не нужно отвечать (это для bigor, Михаил-то уже давно про это знает(

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

Помогающие, особенно лохматые, что, опять ругаться будем?
В темах с нарушениями не нужно отвечать (это для bigor, Михаил-то уже давно про это знает(
Автор - _Boroda_
Дата добавления - 10.12.2019 в 12:01
Shtein Дата: Вторник, 10.12.2019, 12:16 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
_Boroda_, извините, исправил.

bmv98rus, bigor - интересные решения, формулы массива так понимаю.
А СУММЕСЛИ или СУММЕСЛИМН нет такого решения? Я сейчас пробую что-то сделать еще в связке ИНДЕКС и ПОИСКПОЗ

"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.
 
Ответить
Сообщение_Boroda_, извините, исправил.

bmv98rus, bigor - интересные решения, формулы массива так понимаю.
А СУММЕСЛИ или СУММЕСЛИМН нет такого решения? Я сейчас пробую что-то сделать еще в связке ИНДЕКС и ПОИСКПОЗ
Автор - Shtein
Дата добавления - 10.12.2019 в 12:16
_Boroda_ Дата: Вторник, 10.12.2019, 12:18 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 16956
Репутация: 6630 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Еще варианты
Код
=ИНДЕКС(A$2:A$6;ПОИСКПОЗ(I2;Ч(+B$1:B$5)))
Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
Код
=(СЧЁТЕСЛИ(B$2:B$5;"<="&I2)+1)/1%
К сообщению приложен файл: example_219.xlsx (14.2 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕще варианты
Код
=ИНДЕКС(A$2:A$6;ПОИСКПОЗ(I2;Ч(+B$1:B$5)))
Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
Код
=(СЧЁТЕСЛИ(B$2:B$5;"<="&I2)+1)/1%
Автор - _Boroda_
Дата добавления - 10.12.2019 в 12:18
Shtein Дата: Вторник, 10.12.2019, 12:24 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
_Boroda_, спасибо
получается, решения такого плана задачи все крутится возле формул массива

"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.
 
Ответить
Сообщение_Boroda_, спасибо
получается, решения такого плана задачи все крутится возле формул массива
Автор - Shtein
Дата добавления - 10.12.2019 в 12:24
Nic70y Дата: Вторник, 10.12.2019, 12:33 | Сообщение № 8
Группа: Друзья
Ранг: Экселист
Сообщений: 9185
Репутация: 2448 ±
Замечаний: 0% ±

Excel 2010
Цитата Shtein, 10.12.2019 в 12:24, в сообщении № 7 ()
=ИНДЕКС(A$2:A$6;ПОИСКПОЗ(I2;Ч(+B$1:B$5)))
единственная формула массива в теме.
+ моя (тоже немассивная) для "извращенцев", только B6 надо стереть
Код
=НАИМЕНЬШИЙ(A$2:A$6;СЧЁТ(B$2:B$6)-СЧЁТЕСЛИ(B$2:B$6;">"&I2)+1)

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Вторник, 10.12.2019, 12:35
 
Ответить
Сообщение
Цитата Shtein, 10.12.2019 в 12:24, в сообщении № 7 ()
=ИНДЕКС(A$2:A$6;ПОИСКПОЗ(I2;Ч(+B$1:B$5)))
единственная формула массива в теме.
+ моя (тоже немассивная) для "извращенцев", только B6 надо стереть
Код
=НАИМЕНЬШИЙ(A$2:A$6;СЧЁТ(B$2:B$6)-СЧЁТЕСЛИ(B$2:B$6;">"&I2)+1)
Автор - Nic70y
Дата добавления - 10.12.2019 в 12:33
Shtein Дата: Вторник, 10.12.2019, 12:42 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
Nic70y, попробовал, для диапазона больше 11000 выдает #ЧИСЛО!, для остальных работает.

"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.
 
Ответить
СообщениеNic70y, попробовал, для диапазона больше 11000 выдает #ЧИСЛО!, для остальных работает.
Автор - Shtein
Дата добавления - 10.12.2019 в 12:42
bmv98rus Дата: Вторник, 10.12.2019, 12:45 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата _Boroda_, 10.12.2019 в 12:01, в сообщении № 4 ()
Михаил-то уже давно про это знает(
Так это, уже который месяц меня никто не замечает :-)

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата _Boroda_, 10.12.2019 в 12:01, в сообщении № 4 ()
Михаил-то уже давно про это знает(
Так это, уже который месяц меня никто не замечает :-)
Автор - bmv98rus
Дата добавления - 10.12.2019 в 12:45
Nic70y Дата: Вторник, 10.12.2019, 13:08 | Сообщение № 11
Группа: Друзья
Ранг: Экселист
Сообщений: 9185
Репутация: 2448 ±
Замечаний: 0% ±

Excel 2010
Цитата Shtein, 10.12.2019 в 12:42, в сообщении № 9 ()
попробовал
ни чё незнаю :)
К сообщению приложен файл: 9951527.xlsx (13.4 Kb)

ЮMoney 41001841029809
 
Ответить
Сообщение
Цитата Shtein, 10.12.2019 в 12:42, в сообщении № 9 ()
попробовал
ни чё незнаю :)
Автор - Nic70y
Дата добавления - 10.12.2019 в 13:08
_Boroda_ Дата: Вторник, 10.12.2019, 13:26 | Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 16956
Репутация: 6630 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
А если табличку чуть поправить, то совсем просто можно
Код
=ИНДЕКС(A$2:A$6;ПОИСКПОЗ(I2;B$1:B$5))
К сообщению приложен файл: example_2191.xlsx (13.8 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА если табличку чуть поправить, то совсем просто можно
Код
=ИНДЕКС(A$2:A$6;ПОИСКПОЗ(I2;B$1:B$5))
Автор - _Boroda_
Дата добавления - 10.12.2019 в 13:26
bmv98rus Дата: Вторник, 10.12.2019, 15:30 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Код
=MATCH(I2%;{0;2;4;6;11}*10;)/1%

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 10.12.2019, 15:34
 
Ответить
Сообщение
Код
=MATCH(I2%;{0;2;4;6;11}*10;)/1%
Автор - bmv98rus
Дата добавления - 10.12.2019 в 15:30
Shtein Дата: Среда, 11.12.2019, 10:30 | Сообщение № 14
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
Есть еще вариант с формулой ЕСЛИМН, правда на моем Excel 2016 ее нет, а вообще тоже удобный ваирант для решения такой задачки.

"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.

Сообщение отредактировал Shtein - Среда, 11.12.2019, 20:32
 
Ответить
СообщениеЕсть еще вариант с формулой ЕСЛИМН, правда на моем Excel 2016 ее нет, а вообще тоже удобный ваирант для решения такой задачки.
Автор - Shtein
Дата добавления - 11.12.2019 в 10:30
bmv98rus Дата: Среда, 11.12.2019, 10:46 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата Shtein, 11.12.2019 в 10:30, в сообщении № 14 ()
а вообще тоже удобный варант для решения такой задачки.
Есть еще вариант с формулой ЕСЛИМН, правда на моем Excel 2016 ее нет, а вообще тоже удобный варант для решения такой задачки.Shtein
ошибаетесь и хорошо что её нет :-)

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата Shtein, 11.12.2019 в 10:30, в сообщении № 14 ()
а вообще тоже удобный варант для решения такой задачки.
Есть еще вариант с формулой ЕСЛИМН, правда на моем Excel 2016 ее нет, а вообще тоже удобный варант для решения такой задачки.Shtein
ошибаетесь и хорошо что её нет :-)
Автор - bmv98rus
Дата добавления - 11.12.2019 в 10:46
Светлый Дата: Среда, 11.12.2019, 10:59 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1880
Репутация: 543 ±
Замечаний: 0% ±

Excel 2013, 2016
Ещё вариантик:
Код
=СУММ((I2%>={0;2;4;7;11}*10)/1%)

Программировать проще, чем писать стихи.
 
Ответить
СообщениеЕщё вариантик:
Код
=СУММ((I2%>={0;2;4;7;11}*10)/1%)
Автор - Светлый
Дата добавления - 11.12.2019 в 10:59
bmv98rus Дата: Среда, 11.12.2019, 23:28 | Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Светлый обратил внимание на мою ошибку в №13
Код
=MATCH(I2%;{0;2;4;6;11}*10)/1%

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеСветлый обратил внимание на мою ошибку в №13
Код
=MATCH(I2%;{0;2;4;6;11}*10)/1%
Автор - bmv98rus
Дата добавления - 11.12.2019 в 23:28
Shtein Дата: Четверг, 12.12.2019, 13:12 | Сообщение № 18
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
bmv98rus, не знаю, у меня работали оба ваши варианта)

"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.
 
Ответить
Сообщениеbmv98rus, не знаю, у меня работали оба ваши варианта)
Автор - Shtein
Дата добавления - 12.12.2019 в 13:12
bmv98rus Дата: Четверг, 12.12.2019, 13:27 | Сообщение № 19
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Shtein, это случайность, скорее всего вводили граничные значения, а не 3000 например.

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеShtein, это случайность, скорее всего вводили граничные значения, а не 3000 например.
Автор - bmv98rus
Дата добавления - 12.12.2019 в 13:27
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Извлечение числа по условию (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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