Выбрать наибольшую разницу от нуля. - Мир MS Excel

Регистрация

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

= Мир MS Excel/Выбрать наибольшую разницу от нуля. - Мир MS Excel

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбрать наибольшую разницу от нуля. (Формулы/Formulas)

Выбрать наибольшую разницу от нуля.

Дата: Пятница, 16.11.2018, 04:59 | Сообщение № 1

Группа: Проверенные

Ранг: Обитатель

Сообщений: 370

Репутация: 7 ±

Замечаний: 40% ±

Excel 2007

Вечер добрый. Помогите пожалуйста.

Есть максимальное и минимальное число, необходимо вывести из этих двух чисел то которое имеет наибольшую разницу от нуля. Желательно одной формулой, соединить =макс() и =мин() прописать диапазон и вывести то число которое имеет наибольшую разницу от нуля.

К сообщению приложен файл: raznicaotnulja.xlsx (9.2 Kb)

Сообщение отредактировал stalber - Пятница, 16.11.2018, 05:00

Ответить

Дата: Пятница, 16.11.2018, 05:05 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 989 ±

Замечаний: 0% ±

Excel 2007,2010,2013

Здравствуйте
Массивная формула
^{Код
=ИНДЕКС(C4:C22;ПОИСКПОЗ(МАКС(ABS(C4:C22));ABS(C4:C22);))}

К сообщению приложен файл: 0885874.xlsx (9.4 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Пятница, 16.11.2018, 05:06

Ответить

Дата: Пятница, 16.11.2018, 06:03 | Сообщение № 3

Группа: Проверенные

Ранг: Обитатель

Сообщений: 370

Репутация: 7 ±

Замечаний: 40% ±

Excel 2007

Ещё одно дополнительное условие понадобилось - вывод по формуле числа, и что то не получается теперь вывести, будьте добры помогите.

К сообщению приложен файл: 0885874osh.xlsx (9.5 Kb)

Ответить

Дата: Пятница, 16.11.2018, 06:13 | Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Здравствуйте!

Код

=ЕСЛИ(ABS(МАКС(C4:C28))>ABS(МИН(C4:C28));МАКС(C4:C28);МИН(C4:C28))

* И массивная (Ctrl+Shift+Enter):

Код

=СРЗНАЧ(ЕСЛИ(МАКС(C4:C28*{1;-1})*{1;-1}=C4:C28;C4:C28))

**Или так:

Код

=СРЗНАЧ(ЕСЛИ(МИН(C4:C28*{1;-1})*{1;-1}=C4:C28;C4:C28))

***Но формула даёт ошибку, если макс=-мин
****Так лучше. И без ошибок:

Код

=МИН(ЕСЛИ(МИН(C4:C28*{1;-1})=C4:C28*{1;-1};C4:C28))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Пятница, 16.11.2018, 10:55

Ответить

Дата: Пятница, 16.11.2018, 16:15 | Сообщение № 5

Группа: Проверенные

Ранг: Обитатель

Сообщений: 370

Репутация: 7 ±

Замечаний: 40% ±

Excel 2007

Благодарю: использовал данную формулу:

Код

=ЕСЛИ(ABS(МАКС(C4:C28))>ABS(МИН(C4:C28));МАКС(C4:C28);МИН(C4:C28))

Понадобился ещё аналог данной формулы, чтобы формула рассчитывала из последних (самых нижних в столбце) 10 заполненных ячеек.

Ответить

Дата: Пятница, 16.11.2018, 17:18 | Сообщение № 6

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Если массив больше 10 чисел, то массивная формула:

Код

=МИН(ЕСЛИ(МИН(СМЕЩ(C1;МАКС((C4:C99<>0)*СТРОКА(3:98));;-10)*{1;-1})=СМЕЩ(C1;МАКС((C4:C99<>0)*СТРОКА(3:98));;-10)*{1;-1};СМЕЩ(C1;МАКС((C4:C99<>0)*СТРОКА(3:98));;-10)))

Программировать проще, чем писать стихи.

Ответить

Дата: Пятница, 16.11.2018, 17:29 | Сообщение № 7

Группа: Проверенные

Ранг: Обитатель

Сообщений: 370

Репутация: 7 ±

Замечаний: 40% ±

Excel 2007

Не получается чего то.
Вопрос, интересно просто, а почему диапазон в формуле иногда C4:C99 а иногда 3:98

К сообщению приложен файл: 0885874osh2.xlsx (9.4 Kb)

Ответить

Дата: Пятница, 16.11.2018, 17:40 | Сообщение № 8

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Формула массивная (Ctrl+Shift+Enter).

Цитата stalber, 16.11.2018 в 17:29, в сообщении № 7 ()

а иногда 3:98

чтобы не вычитать 1.

Программировать проще, чем писать стихи.

Ответить

Дата: Пятница, 16.11.2018, 18:20 | Сообщение № 9

Группа: Проверенные

Ранг: Обитатель

Сообщений: 370

Репутация: 7 ±

Замечаний: 40% ±

Excel 2007

я нажимал ctrl shift enter, посмотрите мой последний прикреплённый пример

Ответить

Дата: Пятница, 16.11.2018, 18:57 | Сообщение № 10

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Там у Вас формулы появились ="". Чтобы моя формула работала, надо <>0 заменить на <>"".

Программировать проще, чем писать стихи.

Ответить

Дата: Пятница, 16.11.2018, 20:37 | Сообщение № 11

Группа: Проверенные

Ранг: Обитатель

Сообщений: 370

Репутация: 7 ±

Замечаний: 40% ±

Excel 2007

Извиняюсь не смог предвидеть данную ситуацию заранее. Получилось что данные постепенно появляются и так как сначала данных меньше 10, то формула не выдаёт данные, она начинает работать когда данных от 10 и более. Необходимо что при меньших данных тоже выдавался результат.

К сообщению приложен файл: 0885874osh3.xlsx (9.8 Kb)

Ответить

Дата: Пятница, 16.11.2018, 21:11 | Сообщение № 12

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 989 ±

Замечаний: 0% ±

Excel 2007,2010,2013

Для G3 массивная формула

Код

=ПРОСМОТР(9^9;ВПР(Ч(ИНДЕКС(МАКС(ABS(ИНДЕКС(G:G;МАКС(СТРОКА(G4);ПРОСМОТР(2;1/G4:G99;СТРОКА(G4:G99))-9)):ИНДЕКС(G:G;ПРОСМОТР(2;1/G4:G99;СТРОКА(G4:G99)))))*{1;-1};));G4:G99;1;))

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Суббота, 17.11.2018, 01:29

Ответить

Дата: Пятница, 16.11.2018, 21:36 | Сообщение № 13

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Подкорректировал формулу на количество чисел меньше 10:

Код

=МИН(ЕСЛИ(МИН(СМЕЩ(C1;МАКС((C4:C99<>0)*СТРОКА(3:98);9);;-10)*{1;-1})=СМЕЩ(C1;МАКС((C4:C99<>0)*СТРОКА(3:98);9);;-10)*{1;-1};СМЕЩ(C1;МАКС((C4:C99<>0)*СТРОКА(3:98);9);;-10)))

*Правильнее будет:

Код

=МИН(ЕСЛИ(МИН(СМЕЩ(C4;МАКС((C4:C99<>0)*СТРОКА(3:98);9);;-10)*{1;-1})=СМЕЩ(C4;МАКС((C4:C99<>0)*СТРОКА(3:98);9);;-10)*{1;-1};СМЕЩ(C4;МАКС((C4:C99<>0)*СТРОКА(3:98);9);;-10)))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Пятница, 16.11.2018, 22:18

Ответить

Дата: Пятница, 16.11.2018, 22:51 | Сообщение № 14

Группа: Проверенные

Ранг: Обитатель

Сообщений: 370

Репутация: 7 ±

Замечаний: 40% ±

Excel 2007

Благодарю за помощь.

krosav4ig ваша формула ищет данные не в последних 10.
Светлый что то не получилось всё равно, я сократил стёр несколько последних данных чтобы вышло меньше 10 всего данных и формула перестала работать.

К сообщению приложен файл: 0885874osh5.xlsx (10.5 Kb)

Ответить

Дата: Пятница, 16.11.2018, 23:16 | Сообщение № 15

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

На основе формулы krosav4ig построил ещё одну (массивная):

Код

=ПРОСМОТР(9^9;ВПР(Ч(ИНДЕКС(МАКС(ABS(Ч(+СМЕЩ(C4;МАКС((C4:C99<>"")*СТРОКА(3:98);9);;-10))))*{1;-1};));C4:C99;1;))

*И ещё одна:

Код

=МИН(ЕСЛИ(МАКС(ABS(Ч(+СМЕЩ(C4;МАКС((C4:C99<>"")*СТРОКА(3:98);9);;-10))))*{1;-1}=C4:C99;C4:C99))

**А для всего диапазона:

Код

=МИН(ЕСЛИ(МИН(C4:C99;-Ч(+C4:C99))*{1;-1}=C4:C99;C4:C99))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Суббота, 17.11.2018, 00:45

Ответить

Дата: Суббота, 17.11.2018, 01:18 | Сообщение № 16

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 989 ±

Замечаний: 0% ±

Excel 2007,2010,2013

Цитата stalber, 16.11.2018 в 22:51, в сообщении № 14 ()

krosav4ig ваша формула ищет данные не в последних 10.

Исправил свою формулу в 12 посте (вместо -10 должно быть -9)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Ответить

Дата: Суббота, 17.11.2018, 01:26 | Сообщение № 17

Группа: Проверенные

Ранг: Обитатель

Сообщений: 370

Репутация: 7 ±

Замечаний: 40% ±

Excel 2007

Светлый - Почему то не последние 10, а последние 7 данных считается. Это как то связано с первыми пустыми строками наверное.
+Хотел увеличить за место 10 последних поставить последние 100 данных, и формула перестала вообще работать. Я изменил -10 на -100, но видимо что то ещё нужно поменять.

krosav4ig а что нужно в вашей формуле поменять чтобы работало в последних 100 данных? за место 10 ?

Код

=ПРОСМОТР(9^9;ВПР(Ч(ИНДЕКС(МАКС(ABS(ИНДЕКС(G:G;МАКС(СТРОКА(G4);ПРОСМОТР(2;1/G4:G99;СТРОКА(G4:G99))-9)):ИНДЕКС(G:G;ПРОСМОТР(2;1/G4:G99;СТРОКА(G4:G99)))))*{1;-1};));G4:G99;1;))

К сообщению приложен файл: 0885874osh6.xlsx (10.9 Kb)

Сообщение отредактировал stalber - Суббота, 17.11.2018, 01:34

Ответить

Дата: Суббота, 17.11.2018, 01:38 | Сообщение № 18

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 989 ±

Замечаний: 0% ±

Excel 2007,2010,2013

Цитата stalber, 17.11.2018 в 01:26, в сообщении № 17 ()

чтобы работало в 100 данных? за место 10

вместо -9 написать -99
и, соответственно, вместо G4:G99 указать диапазон, охватывающий все данные
и формулу лучше переписать так, ибо, если будут дробные числа будет работать неверно

Код

=ПРОСМОТР(9^9;ВПР(Ч(ИНДЕКС(МАКС(ABS(ИНДЕКС(G:G;МАКС(СТРОКА(G4);ПРОСМОТР(9^9;G4:G99;СТРОКА(G4:G99))-9)):ИНДЕКС(G:G;ПРОСМОТР(9^9;G4:G99;СТРОКА(G4:G99)))))*{1;-1};));G4:G99;1;))

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Суббота, 17.11.2018, 01:44

Ответить

Дата: Суббота, 17.11.2018, 01:44 | Сообщение № 19

Группа: Проверенные

Ранг: Обитатель

Сообщений: 370

Репутация: 7 ±

Замечаний: 40% ±

Excel 2007

Спасибо!

Использую данную формулу от krosav4ig для последних 100 данных:

Код

=ПРОСМОТР(9^9;ВПР(Ч(ИНДЕКС(МАКС(ABS(ИНДЕКС(G:G;МАКС(СТРОКА(G4);ПРОСМОТР(2;1/G4:G9999;СТРОКА(G4:G9999))-99)):ИНДЕКС(G:G;ПРОСМОТР(2;1/G4:G9999;СТРОКА(G4:G9999)))))*{1;-1};));G4:G9999;1;))

Использую данную формулу от Светлый для всего диапазона:

Код

=МИН(ЕСЛИ(МИН(C4:C9999;-Ч(+C4:C9999))*{1;-1}=C4:C9999;C4:C9999))

Сообщение отредактировал stalber - Суббота, 17.11.2018, 01:44

Ответить

Дата: Суббота, 17.11.2018, 10:48 | Сообщение № 20

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Невнимательно исправлял формулу. Сейчас для 10:

Код

=МИН(ЕСЛИ(МАКС(ABS(Ч(+СМЕЩ(C1;МАКС((C4:C9999<>"")*СТРОКА(3:9998);12);;-10))))*{1;-1}=C4:C9999;C4:C9999))

И для 100:

Код

=МИН(ЕСЛИ(МАКС(ABS(Ч(+СМЕЩ(C1;МАКС((C4:C9999<>"")*СТРОКА(3:9998);102);;-100))))*{1;-1}=C4:C9999;C4:C9999))

Программировать проще, чем писать стихи.

Ответить

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбрать наибольшую разницу от нуля. (Формулы/Formulas)

Страница 1 из 1
1

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