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

Вход

Регистрация

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

 

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

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула поиска соответствующего значения из диапозона (Формулы/Formulas)
Формула поиска соответствующего значения из диапозона
Zarina Дата: Вторник, 05.01.2016, 11:03 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте, уважаемые эксперты!
Нужна помощь в составлении формулы, которая бы искала степень "критичности" "значений", вставляемых нами в зеленом диапазоне (=Проблемы!$B$2:$B$4) на соответствие значениям из желтого диапазона (=Критичность!$B$2:$D$4), подставляя значения в соответствующий столбец (=Проблемы!C:C), как показано в файле примера.
Заранее спасибо!
К сообщению приложен файл: 2325144.xlsx(9Kb)
 
Ответить
СообщениеЗдравствуйте, уважаемые эксперты!
Нужна помощь в составлении формулы, которая бы искала степень "критичности" "значений", вставляемых нами в зеленом диапазоне (=Проблемы!$B$2:$B$4) на соответствие значениям из желтого диапазона (=Критичность!$B$2:$D$4), подставляя значения в соответствующий столбец (=Проблемы!C:C), как показано в файле примера.
Заранее спасибо!

Автор - Zarina
Дата добавления - 05.01.2016 в 11:03
китин Дата: Вторник, 05.01.2016, 11:14 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3723
Репутация: 587 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
может так пойдет?
Код
=ПРОСМОТР(B2;ИНДЕКС(Критичность!$B$2:$D$4;ПОИСКПОЗ(Проблемы!$A2;Критичность!$A$2:$A$4;0););{"Незначительно";"Существенно";"Критично"})

переделал данные на листе Критичность
К сообщению приложен файл: 23456.xlsx(10Kb)


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538


Сообщение отредактировал китин - Вторник, 05.01.2016, 11:17
 
Ответить
Сообщениеможет так пойдет?
Код
=ПРОСМОТР(B2;ИНДЕКС(Критичность!$B$2:$D$4;ПОИСКПОЗ(Проблемы!$A2;Критичность!$A$2:$A$4;0););{"Незначительно";"Существенно";"Критично"})

переделал данные на листе Критичность

Автор - китин
Дата добавления - 05.01.2016 в 11:14
buchlotnik Дата: Вторник, 05.01.2016, 11:17 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 2207
Репутация: 659 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
По вашему примеру так %)
Код
=ЕСЛИ(B2<(--ПОДСТАВИТЬ(Критичность!B2;"<";""));"Незначительно";ЕСЛИ(B2>=(--ПОДСТАВИТЬ(Критичность!D2;">";""));"Критично";"Существенно"))
но я бы посоветовал изменить таблицу китичности, тогда так:
Код
=ПРОСМОТР(B2;Критичность!F2:H2;Критичность!$F$1:$H$1)
так вроде лучше B)
К сообщению приложен файл: 3578892.xlsx(10Kb)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
СообщениеПо вашему примеру так %)
Код
=ЕСЛИ(B2<(--ПОДСТАВИТЬ(Критичность!B2;"<";""));"Незначительно";ЕСЛИ(B2>=(--ПОДСТАВИТЬ(Критичность!D2;">";""));"Критично";"Существенно"))
но я бы посоветовал изменить таблицу китичности, тогда так:
Код
=ПРОСМОТР(B2;Критичность!F2:H2;Критичность!$F$1:$H$1)
так вроде лучше B)

Автор - buchlotnik
Дата добавления - 05.01.2016 в 11:17
Zarina Дата: Вторник, 05.01.2016, 11:28 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
китин, не совсем так. Данные на листе "критичность" требуется указывать в диапазоне "между", например от -3 до 3, от 5 до 25 (немного не поняла как это указать для вашей формулы).
Выдаваемое значение формула должна забирать из (=Критичность!$B$1:$D$1), так как там значения могут быть оперативно заменены.
 
Ответить
Сообщениекитин, не совсем так. Данные на листе "критичность" требуется указывать в диапазоне "между", например от -3 до 3, от 5 до 25 (немного не поняла как это указать для вашей формулы).
Выдаваемое значение формула должна забирать из (=Критичность!$B$1:$D$1), так как там значения могут быть оперативно заменены.

Автор - Zarina
Дата добавления - 05.01.2016 в 11:28
buchlotnik Дата: Вторник, 05.01.2016, 11:33 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 2207
Репутация: 659 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
Цитата
от -3 до 3, от 5 до 25
а как в этом случае поступать с диапазоном от 3 до 5 и как итенрпретировать значение более 25?
Цитата
как это указать для вашей формулы

Например, ваше <5 потом >5;<10 потом >10 мы с Игорем, не сговариваясь, превратили в 0 потом 5 потом 10, т.е. указали НИЖНИЕ границы диапазонов - врядли у вас есть верхняя граница для критичной проблемы (как впрочем и нижняя для некритичной)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru


Сообщение отредактировал buchlotnik - Вторник, 05.01.2016, 11:39
 
Ответить
Сообщение
Цитата
от -3 до 3, от 5 до 25
а как в этом случае поступать с диапазоном от 3 до 5 и как итенрпретировать значение более 25?
Цитата
как это указать для вашей формулы

Например, ваше <5 потом >5;<10 потом >10 мы с Игорем, не сговариваясь, превратили в 0 потом 5 потом 10, т.е. указали НИЖНИЕ границы диапазонов - врядли у вас есть верхняя граница для критичной проблемы (как впрочем и нижняя для некритичной)

Автор - buchlotnik
Дата добавления - 05.01.2016 в 11:33
китин Дата: Вторник, 05.01.2016, 11:41 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3723
Репутация: 587 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
Zarina, а вы мой файл то смотрели? игрались с ним? там на листе критичность те же цифры, что и у вас только знаки <,> убраны. три столбца.от0 до 5.от 5 до 10 и >10.


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
СообщениеZarina, а вы мой файл то смотрели? игрались с ним? там на листе критичность те же цифры, что и у вас только знаки <,> убраны. три столбца.от0 до 5.от 5 до 10 и >10.

Автор - китин
Дата добавления - 05.01.2016 в 11:41
Zarina Дата: Вторник, 05.01.2016, 11:50 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
buchlotnik, по первой формуле некорректно воспринимает отрицательные значения. Например, "-1" - это по условиям <5, а формула выдает Н/Д, жалуясь на значение.
Также нужен интервал значений, который бы указывался в условиях (на странице "Критичность"). Возможно, не в трех колонках, если сложно все условия включить в одну ячейку.
 
Ответить
Сообщениеbuchlotnik, по первой формуле некорректно воспринимает отрицательные значения. Например, "-1" - это по условиям <5, а формула выдает Н/Д, жалуясь на значение.
Также нужен интервал значений, который бы указывался в условиях (на странице "Критичность"). Возможно, не в трех колонках, если сложно все условия включить в одну ячейку.

Автор - Zarina
Дата добавления - 05.01.2016 в 11:50
buchlotnik Дата: Вторник, 05.01.2016, 11:53 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 2207
Репутация: 659 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
Zarina, покажите в файле, что у Вас не работает (первая формула не может Н/Д выдавать, разве что вторая - но тогда точно файл показывайте)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru


Сообщение отредактировал buchlotnik - Вторник, 05.01.2016, 11:58
 
Ответить
СообщениеZarina, покажите в файле, что у Вас не работает (первая формула не может Н/Д выдавать, разве что вторая - но тогда точно файл показывайте)

Автор - buchlotnik
Дата добавления - 05.01.2016 в 11:53
gling Дата: Вторник, 05.01.2016, 12:00 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1399
Репутация: 342 ±
Замечаний: 0% ±

2010
Здравствуйте. Для сравнения отрицательных чисел в таблице соответствий замените 0 на -9^9. А если хотите от -3 до 5, то замените 0 на -3.


Сообщение отредактировал gling - Вторник, 05.01.2016, 12:07
 
Ответить
СообщениеЗдравствуйте. Для сравнения отрицательных чисел в таблице соответствий замените 0 на -9^9. А если хотите от -3 до 5, то замените 0 на -3.

Автор - gling
Дата добавления - 05.01.2016 в 12:00
китин Дата: Вторник, 05.01.2016, 12:03 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3723
Репутация: 587 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
некорректно воспринимает отрицательные значения
ну так поставьте в первом столбце вместо 0 наименьшее возможное значение критичности.и будет у вас интервал от -10 до 5(между первым и вторым столбцом),от5 до 10 (между вторым и третьим столбцом) и все что больше 10 .вот вам и ваше <5 потом >5;<10 потом >10


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщение
некорректно воспринимает отрицательные значения
ну так поставьте в первом столбце вместо 0 наименьшее возможное значение критичности.и будет у вас интервал от -10 до 5(между первым и вторым столбцом),от5 до 10 (между вторым и третьим столбцом) и все что больше 10 .вот вам и ваше <5 потом >5;<10 потом >10

Автор - китин
Дата добавления - 05.01.2016 в 12:03
Zarina Дата: Вторник, 05.01.2016, 12:07 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
buchlotnik,
врядли у вас есть верхняя граница для критичной проблемы

Да, вы правы, выходит, что нужно только две границы - отделяющие нижний от среднего диапазона, и средний от верхнего.
Тогда:
превратили в 0 потом 5 потом 10

значение 0 было лишним и выдавало ошибку при отрицательных значениях
 
Ответить
Сообщениеbuchlotnik,
врядли у вас есть верхняя граница для критичной проблемы

Да, вы правы, выходит, что нужно только две границы - отделяющие нижний от среднего диапазона, и средний от верхнего.
Тогда:
превратили в 0 потом 5 потом 10

значение 0 было лишним и выдавало ошибку при отрицательных значениях

Автор - Zarina
Дата добавления - 05.01.2016 в 12:07
Zarina Дата: Вторник, 05.01.2016, 12:10 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
покажите в файле, что у Вас не работает
К сообщению приложен файл: 0436909.xlsx(10Kb)
 
Ответить
Сообщение
покажите в файле, что у Вас не работает

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

Excel 2010
ну так поставьте в первом столбце вместо 0 наименьшее возможное значение критичности.и будет у вас интервал от -10 до 5(между первым и вторым столбцом),от5 до 10 (между вторым и третьим столбцом) и все что больше 10 .вот вам и ваше <5 потом >5;<10 потом >10

Да, это решило проблему, вначале просто не поняла откуда взялся 0.
 
Ответить
Сообщение
ну так поставьте в первом столбце вместо 0 наименьшее возможное значение критичности.и будет у вас интервал от -10 до 5(между первым и вторым столбцом),от5 до 10 (между вторым и третьим столбцом) и все что больше 10 .вот вам и ваше <5 потом >5;<10 потом >10

Да, это решило проблему, вначале просто не поняла откуда взялся 0.

Автор - Zarina
Дата добавления - 05.01.2016 в 12:18
buchlotnik Дата: Вторник, 05.01.2016, 12:22 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 2207
Репутация: 659 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
ну правильно, меняем на -9^9 (или любой другой Ваш нижний предел) и пашет B) Кстати, через ЕСЛИ() можно и без нижнего предела обойтись
Код
=ЕСЛИ(B2<Критичность!K2;"Незначительно";ЕСЛИ(B2>Критичность!L2;"Критично";"Существенно"))
К сообщению приложен файл: 0436909-1-.xlsx(10Kb)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru


Сообщение отредактировал buchlotnik - Вторник, 05.01.2016, 12:28
 
Ответить
Сообщениену правильно, меняем на -9^9 (или любой другой Ваш нижний предел) и пашет B) Кстати, через ЕСЛИ() можно и без нижнего предела обойтись
Код
=ЕСЛИ(B2<Критичность!K2;"Незначительно";ЕСЛИ(B2>Критичность!L2;"Критично";"Существенно"))

Автор - buchlotnik
Дата добавления - 05.01.2016 в 12:22
Zarina Дата: Вторник, 05.01.2016, 12:26 | Сообщение № 15
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
При указании значения в первом столбце (минимальный диапазон) все замечательно работает у обоих экспертов. Вначале не поняла, зачем нужен первый и откуда взялся первый столбец.

Всем большое спасибо!!
Формула оказалась простой, это я вас немного запутала.
Тему можно считать закрытой.
 
Ответить
СообщениеПри указании значения в первом столбце (минимальный диапазон) все замечательно работает у обоих экспертов. Вначале не поняла, зачем нужен первый и откуда взялся первый столбец.

Всем большое спасибо!!
Формула оказалась простой, это я вас немного запутала.
Тему можно считать закрытой.

Автор - Zarina
Дата добавления - 05.01.2016 в 12:26
Udik Дата: Вторник, 05.01.2016, 12:37 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1298
Репутация: 161 ±
Замечаний: 0% ±

Excel 2013
если нижний предел >-100 (или подправить во второй табличке), то так можно
Код
=ИНДЕКС(Критичность!$B$1:$D$1;;ПОИСКПОЗ(B2;Критичность!B2:D2;1))


кстати, разрыв границы - очепятка, или так может быть?
К сообщению приложен файл: 9614414.xlsx(12Kb) · 5002351.jpg(37Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Вторник, 05.01.2016, 12:46
 
Ответить
Сообщениеесли нижний предел >-100 (или подправить во второй табличке), то так можно
Код
=ИНДЕКС(Критичность!$B$1:$D$1;;ПОИСКПОЗ(B2;Критичность!B2:D2;1))


кстати, разрыв границы - очепятка, или так может быть?

Автор - Udik
Дата добавления - 05.01.2016 в 12:37
Zarina Дата: Вторник, 05.01.2016, 17:18 | Сообщение № 17
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
разрыв границы - очепятка, или так может быть?

Это опечатка.
Спасибо, ваш вариант тоже замечательно работает.
 
Ответить
Сообщение
разрыв границы - очепятка, или так может быть?

Это опечатка.
Спасибо, ваш вариант тоже замечательно работает.

Автор - Zarina
Дата добавления - 05.01.2016 в 17:18
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула поиска соответствующего значения из диапозона (Формулы/Formulas)
Страница 1 из 11
Поиск:

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