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

Вход

Регистрация

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

 

= Мир MS Excel/выборка данных из таблицы по ближайшему значению - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » выборка данных из таблицы по ближайшему значению (Формулы/Formulas)
выборка данных из таблицы по ближайшему значению
mettuon Дата: Среда, 29.04.2015, 13:23 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 56
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Можно ли сделать так чтобы, при некотором значении ячейки G14 или L14, из таблицы выбиралось ближайшее значение столбца "В" и выводилось среднее арифметическое значение столбца "С"???
К сообщению приложен файл: _____.xlsx (13.4 Kb)


Сообщение отредактировал mettuon - Среда, 29.04.2015, 13:23
 
Ответить
СообщениеМожно ли сделать так чтобы, при некотором значении ячейки G14 или L14, из таблицы выбиралось ближайшее значение столбца "В" и выводилось среднее арифметическое значение столбца "С"???
Автор - mettuon
Дата добавления - 29.04.2015 в 13:23
Nic70y Дата: Среда, 29.04.2015, 13:52 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 9133
Репутация: 2416 ±
Замечаний: 0% ±

Excel 2010
формула массива:
Код
=ОКРУГЛ(СРЗНАЧ(ИНДЕКС($C1:$C21;МИН(ЕСЛИ(ABS(G14-$B4:$B21)=МИН(ABS(G14-$B4:$B21));СТРОКА(B4:B21)))):ИНДЕКС($C1:$C21;МИН(ЕСЛИ(ABS(G14-$B4:$B21)=НАИМЕНЬШИЙ(ABS(G14-$B4:$B21);2);СТРОКА(B4:B21)))));0)
проверяйте, вдруг правильно.

а так наверное правильней:
Код
=ЕСЛИ(ЕНД(ВПР(G14;B$4:C$21;2;0));ОКРУГЛ(СРЗНАЧ(ИНДЕКС($C1:$C21;МИН(ЕСЛИ(ABS(G14-$B4:$B21)=МИН(ABS(G14-$B4:$B21));СТРОКА(B4:B21)))):ИНДЕКС($C1:$C21;МИН(ЕСЛИ(ABS(G14-$B4:$B21)=НАИМЕНЬШИЙ(ABS(G14-$B4:$B21);2);СТРОКА(B4:B21)))));0);ВПР(G14;B$4:C$21;2;0))
второй файл
К сообщению приложен файл: _Xl0000205.xls (26.0 Kb) · 5910275.xls (26.0 Kb)

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Среда, 29.04.2015, 13:59
 
Ответить
Сообщениеформула массива:
Код
=ОКРУГЛ(СРЗНАЧ(ИНДЕКС($C1:$C21;МИН(ЕСЛИ(ABS(G14-$B4:$B21)=МИН(ABS(G14-$B4:$B21));СТРОКА(B4:B21)))):ИНДЕКС($C1:$C21;МИН(ЕСЛИ(ABS(G14-$B4:$B21)=НАИМЕНЬШИЙ(ABS(G14-$B4:$B21);2);СТРОКА(B4:B21)))));0)
проверяйте, вдруг правильно.

а так наверное правильней:
Код
=ЕСЛИ(ЕНД(ВПР(G14;B$4:C$21;2;0));ОКРУГЛ(СРЗНАЧ(ИНДЕКС($C1:$C21;МИН(ЕСЛИ(ABS(G14-$B4:$B21)=МИН(ABS(G14-$B4:$B21));СТРОКА(B4:B21)))):ИНДЕКС($C1:$C21;МИН(ЕСЛИ(ABS(G14-$B4:$B21)=НАИМЕНЬШИЙ(ABS(G14-$B4:$B21);2);СТРОКА(B4:B21)))));0);ВПР(G14;B$4:C$21;2;0))
второй файл
Автор - Nic70y
Дата добавления - 29.04.2015 в 13:52
mettuon Дата: Среда, 29.04.2015, 14:04 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 56
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Nic70y, Спасибо большое.
Вопрос: А почему индекс начинается с С1 а не как таблица с С4?
 
Ответить
СообщениеNic70y, Спасибо большое.
Вопрос: А почему индекс начинается с С1 а не как таблица с С4?
Автор - mettuon
Дата добавления - 29.04.2015 в 14:04
Nic70y Дата: Среда, 29.04.2015, 14:10 | Сообщение № 4
Группа: Друзья
Ранг: Экселист
Сообщений: 9133
Репутация: 2416 ±
Замечаний: 0% ±

Excel 2010
Цитата mettuon, 29.04.2015 в 14:04, в сообщении № 3
С1 а не как таблица с С4?
т.к.
Цитата Nic70y, 29.04.2015 в 13:52, в сообщении № 2
СТРОКА(B4:B21)
выдаст № строки, а не порядковый номер строки в таблице.
можно так:
Код
=ЕСЛИ(ЕНД(ВПР(G14;B$4:C$21;2;0));ОКРУГЛ(СРЗНАЧ(ИНДЕКС($C4:$C21;МИН(ЕСЛИ(ABS(G14-$B4:$B21)=МИН(ABS(G14-$B4:$B21));СТРОКА(B1:B18)))):ИНДЕКС($C4:$C21;МИН(ЕСЛИ(ABS(G14-$B4:$B21)=НАИМЕНЬШИЙ(ABS(G14-$B4:$B21);2);СТРОКА(B1:B18)))));0);ВПР(G14;B$4:C$21;2;0))
исправлено

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Среда, 29.04.2015, 14:15
 
Ответить
Сообщение
Цитата mettuon, 29.04.2015 в 14:04, в сообщении № 3
С1 а не как таблица с С4?
т.к.
Цитата Nic70y, 29.04.2015 в 13:52, в сообщении № 2
СТРОКА(B4:B21)
выдаст № строки, а не порядковый номер строки в таблице.
можно так:
Код
=ЕСЛИ(ЕНД(ВПР(G14;B$4:C$21;2;0));ОКРУГЛ(СРЗНАЧ(ИНДЕКС($C4:$C21;МИН(ЕСЛИ(ABS(G14-$B4:$B21)=МИН(ABS(G14-$B4:$B21));СТРОКА(B1:B18)))):ИНДЕКС($C4:$C21;МИН(ЕСЛИ(ABS(G14-$B4:$B21)=НАИМЕНЬШИЙ(ABS(G14-$B4:$B21);2);СТРОКА(B1:B18)))));0);ВПР(G14;B$4:C$21;2;0))
исправлено
Автор - Nic70y
Дата добавления - 29.04.2015 в 14:10
mettuon Дата: Среда, 29.04.2015, 14:17 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 56
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
очень сложно и при переносе ячейки с ответом, перестает что либо работать(
если нажимаю на ячейку с ответом чтобы посмотреть формулу, также перестает работать выдает "!ЧИСЛО"
 
Ответить
Сообщениеочень сложно и при переносе ячейки с ответом, перестает что либо работать(
если нажимаю на ячейку с ответом чтобы посмотреть формулу, также перестает работать выдает "!ЧИСЛО"
Автор - mettuon
Дата добавления - 29.04.2015 в 14:17
Nic70y Дата: Среда, 29.04.2015, 14:20 | Сообщение № 6
Группа: Друзья
Ранг: Экселист
Сообщений: 9133
Репутация: 2416 ±
Замечаний: 0% ±

Excel 2010
т.к. это формула массива, которая вводиться не Enter, а Ctrl + Shift + Enter

ЮMoney 41001841029809
 
Ответить
Сообщениет.к. это формула массива, которая вводиться не Enter, а Ctrl + Shift + Enter
Автор - Nic70y
Дата добавления - 29.04.2015 в 14:20
Pelena Дата: Среда, 29.04.2015, 14:28 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 19517
Репутация: 4632 ±
Замечаний: ±

Excel 365 & Mac Excel
mettuon, а нельзя первый столбец весь упорядочить по возрастанию? Тогда и формула упростится
Код
=ЕСЛИОШИБКА(ВПР(G23;$B$4:$C$21;2;0);ОКРУГЛ(СРЗНАЧ(СМЕЩ($C$4:$C$5;ПОИСКПОЗ(G23;$B$4:$B$21)-1;));0))
К сообщению приложен файл: -4-.xlsx (13.9 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщениеmettuon, а нельзя первый столбец весь упорядочить по возрастанию? Тогда и формула упростится
Код
=ЕСЛИОШИБКА(ВПР(G23;$B$4:$C$21;2;0);ОКРУГЛ(СРЗНАЧ(СМЕЩ($C$4:$C$5;ПОИСКПОЗ(G23;$B$4:$B$21)-1;));0))
Автор - Pelena
Дата добавления - 29.04.2015 в 14:28
mettuon Дата: Среда, 29.04.2015, 15:44 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 56
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Pelena, спасибо отлично)
 
Ответить
СообщениеPelena, спасибо отлично)
Автор - mettuon
Дата добавления - 29.04.2015 в 15:44
Мир MS Excel » Вопросы и решения » Вопросы по Excel » выборка данных из таблицы по ближайшему значению (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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