Профессиональные приемы работы в Microsoft Excel

Пожалуйста, войдите или зарегистрируйтесь.


Расширенный поиск  

Новости:

Прикрепить к сообщению можно только файлы xls, gif, jpg, rar, zip,7z, bas, frm, cls, doc размером до 150 Кб.

Автор Тема: Наибольшее значение, условие  (Прочитано 787 раз)

0 Пользователей и 1 Гость просматривают эту тему.

Виталий Поликарпов

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 9

Подскажите, как вывести значение, если числа равны?

Т8=ИНДЕКС(B4:Q59;ПОИСКПОЗ(T10;Q4:Q59;0);1)
U8=ИНДЕКС(B4:Q59;ПОИСКПОЗ(U10;Q4:Q59;0);1)
V8=ИНДЕКС(B4:Q59;ПОИСКПОЗ(V10;Q4:Q59;0);1)
----------------------------------------------------------
T10=НАИБОЛЬШИЙ($Q$4:Q$59;СТРОКА(B1))
U10=НАИБОЛЬШИЙ($Q$4:Q$59;СТРОКА(B2))
V10=НАИБОЛЬШИЙ($Q$4:Q$59;СТРОКА(B3))
---------------------------------------------------------
Как видно на картинке, ячейки Q7 и Q8 равны. Как при одинаковых числах вывести мне значение ячейки B8?
« Последнее редактирование: 24.05.2017, 11:32:16 от Виталий Поликарпов »
Записан

iron priest

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +34/-0
  • Оффлайн Оффлайн
  • Сообщений: 766
  • почётный флудераст
Re: Наибольшее значение, условие
« Ответ #1 : 23.05.2017, 20:37:16 »

почему именно q8? чем она особенная?
Записан

Виталий Поликарпов

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 9
Re: Наибольшее значение, условие
« Ответ #2 : 23.05.2017, 20:43:12 »

почему именно q8? чем она особенная?
Эта таблица лидеров турнира по теннису.
в ячейках Q7 и Q8 - сейчас одинаковое кол-во очков, 28.
Т.е получается что у обоих игроков сейчас равное кол-во очков.
А мне надо вывести имя второго игрока в таблицу на второе место, а у меня дублируется имя первого игрока, потому что берется первое наибольшее значение.
Записан

vikttur

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +46/-0
  • Оффлайн Оффлайн
  • Сообщений: 912
Re: Наибольшее значение, условие
« Ответ #3 : 23.05.2017, 21:07:49 »

Цитировать
Как видно на картинке...
Видно, но формулу в картинке писать не получится.
Записан

iron priest

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +34/-0
  • Оффлайн Оффлайн
  • Сообщений: 766
  • почётный флудераст
Re: Наибольшее значение, условие
« Ответ #4 : 23.05.2017, 21:32:07 »

вывести имя второго игрока в таблицу на второе место, а у меня дублируется имя первого игрока, потому что берется первое наибольшее значение.
если будет 3 или 5 таких позиций, то какую строку выводить?
« Последнее редактирование: 23.05.2017, 21:58:15 от vikttur »
Записан

Виталий Поликарпов

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 9
Re: Наибольшее значение, условие
« Ответ #5 : 23.05.2017, 21:35:49 »

Не жмите бездумно на кнопку цитирования [МОДЕРАТОР]

Цитировать
если будет 3 или 5 таких позиций, то какую строку выводить?
По порядку первые три начиная с верхней.

Вот полная таблица.
« Последнее редактирование: 23.05.2017, 21:57:15 от vikttur »
Записан

zs5

  • Постоялец
  • ***
  • Уважение: +4/-0
  • Оффлайн Оффлайн
  • Сообщений: 160
Re: Наибольшее значение, условие
« Ответ #6 : 24.05.2017, 07:53:15 »

1 место
=НАИБОЛЬШИЙ(Q7:Q10;1)2 место
=ЕСЛИ(СЧЁТЕСЛИ(Q7:Q10;НАИБОЛЬШИЙ(Q7:Q10;1))>1;НАИБОЛЬШИЙ(СМЕЩ(Q7;ПОИСКПОЗ(НАИБОЛЬШИЙ(Q7:Q10;1);Q7:Q10;0);):Q10;1))3 место
=ЕСЛИ(СЧЁТЕСЛИ(Q7:Q10;НАИБОЛЬШИЙ(Q7:Q10;1))>2;НАИБОЛЬШИЙ(СМЕЩ(Q7;ПОИСКПОЗ(НАИБОЛЬШИЙ(Q7:Q10;1);Q7:Q10;0)+ПОИСКПОЗ(НАИБОЛЬШИЙ(Q7:Q10;1);СМЕЩ(Q7;ПОИСКПОЗ(НАИБОЛЬШИЙ(Q7:Q10;1);Q7:Q10;0);):Q10;0);):Q10;1))
Записан

Виталий Поликарпов

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 9
Re: Наибольшее значение, условие
« Ответ #7 : 24.05.2017, 10:14:46 »

Не нужно цитировать без необходимости и копировать целыми сообщениями [МОДЕРАТОР]

Ваша формула ищет число, а мне нужно вывести текст из колонки B. напротив этого числа.
И кстати в формуле 3место - выдает ЛОЖЬ.
« Последнее редактирование: 24.05.2017, 10:45:49 от vikttur »
Записан

zs5

  • Постоялец
  • ***
  • Уважение: +4/-0
  • Оффлайн Оффлайн
  • Сообщений: 160
Re: Наибольшее значение, условие
« Ответ #8 : 24.05.2017, 10:57:20 »

И кстати в формуле 3место - выдает ЛОЖЬ.
Значит только 2 одинаковых значения.
1 место
=ИНДЕКС(B7:B10;ПОИСКПОЗ(НАИБОЛЬШИЙ(Q7:Q10;1);Q7:Q10;0);1)2 место
=ИНДЕКС(СМЕЩ(Q7;ПОИСКПОЗ(НАИБОЛЬШИЙ(Q7:Q10;1);Q7:Q10;0)-1;0):B10;ПОИСКПОЗ(НАИБОЛЬШИЙ(Q7:Q10;1);Q7:Q10;0);1)3 место
=ИНДЕКС(СМЕЩ(Q7;ПОИСКПОЗ(НАИБОЛЬШИЙ(Q7:Q10;1);Q7:Q10;0)+ПОИСКПОЗ(НАИБОЛЬШИЙ(Q7:Q10;1);СМЕЩ(Q7;ПОИСКПОЗ(НАИБОЛЬШИЙ(Q7:Q10;1);Q7:Q10;0);):Q10;0)-1;0):B10;ПОИСКПОЗ(НАИБОЛЬШИЙ(Q7:Q10;1);Q7:Q10;0);1)когда 3 одинаковых значения
Записан

vikttur

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +46/-0
  • Оффлайн Оффлайн
  • Сообщений: 912
Re: Наибольшее значение, условие
« Ответ #9 : 24.05.2017, 10:59:28 »

Виталий Поликарпов, Вы на замечания будете обращать внимание?

Ряд наибольших:
=НАИБОЛЬШИЙ($Q$4:Q$59;СТОЛБЕЦ(A1))Фамилии (формула массива, вводится тремя клавишами):
=ИНДЕКС($B$4:$B$59;НАИБОЛЬШИЙ(ЕСЛИ($Q$4:$Q$59=28;СТРОКА($Q$4:$Q$59)-3);СЧЁТЕСЛИ($T$10:T10;T10)))Перед вводом второй формулы нужно разъединить ячейки. ФМ в объединенные не вводится.

Цитировать
в формуле 3место - выдает ЛОЖЬ.
Цитировать
Значит только 2 одинаковых значения.
Вы неправильно поняли. Показывать нужно три лучших результата, не обязательно одинаковые
« Последнее редактирование: 24.05.2017, 12:39:06 от vikttur »
Записан

Виталий Поликарпов

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 9
Re: Наибольшее значение, условие
« Ответ #10 : 24.05.2017, 11:31:09 »

Виталий Поликарпов, Вы на замечания будете обращать внимание?
I'm sorry.
-----------------
Три лучших результата, я вывожу с помощью:
=НАИБОЛЬШИЙ($Q$4:Q$59;СТРОКА(B1))
=НАИБОЛЬШИЙ($Q$4:Q$59;СТРОКА(B2))
=НАИБОЛЬШИЙ($Q$4:Q$59;СТРОКА(B3))
Ваша формула =ИНДЕКС($B$4:$B$59;НАИБОЛЬШИЙ(ЕСЛИ($Q$4:$Q$59=28;СТРОКА($Q$4:$Q$59)-3);СЧЁТЕСЛИ($T$10:T10;T10)))
Выводит ИСТИНА, и еще момент ЕСЛИ($Q$4:$Q$59=28... - там не всегда будет значение 28. оно меняется...
Может я не так обьясняю??? Если открыть таблицу. то на 2 месте, сейчас должно быть имя "Кирилл Эл-ль"
Записан

zs5

  • Постоялец
  • ***
  • Уважение: +4/-0
  • Оффлайн Оффлайн
  • Сообщений: 160
Re: Наибольшее значение, условие
« Ответ #11 : 24.05.2017, 11:43:54 »

Цитировать
Вы неправильно поняли. Показывать нужно три лучших результата, не обязательно одинаковые
Предлагались условия:
=ЕСЛИ(СЧЁТЕСЛИ(Q7:Q10;НАИБОЛЬШИЙ(Q7:Q10;1))>1=ЕСЛИ(СЧЁТЕСЛИ(Q7:Q10;НАИБОЛЬШИЙ(Q7:Q10;1))>2
Записан

Виталий Поликарпов

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 9
Re: Наибольшее значение, условие
« Ответ #12 : 24.05.2017, 12:03:11 »

2 zs5
См. полную таблицу. Твои решения не работают.
Записан

vikttur

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +46/-0
  • Оффлайн Оффлайн
  • Сообщений: 912
Re: Наибольшее значение, условие
« Ответ #13 : 24.05.2017, 12:36:22 »

Цитировать
Ваша формула... Выводит ИСТИНА
Читайте внимательно:
Цитировать
формула массива, вводится тремя клавишами
Ctrl+Shift+Enter

Цитировать
Три лучших результата, я вывожу с помощью...
А я Вам показал, как сделать протягиваемую формулу. В Вашем же варианте вместо СТРОКА() достаточно написать номер (1, 2 или 3)
Записан

zs5

  • Постоялец
  • ***
  • Уважение: +4/-0
  • Оффлайн Оффлайн
  • Сообщений: 160
Re: Наибольшее значение, условие
« Ответ #14 : 24.05.2017, 13:01:00 »

решения не работают.
[/quote
формула работает
Записан

Виталий Поликарпов

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 9
Re: Наибольшее значение, условие
« Ответ #15 : 24.05.2017, 23:04:23 »

Ну где же работает, когда нет. Ну может вы имеете ввиду при текущих значениях??? тогда. да, Но если игроки получили еще по 5 очков, то формула не работает. у меня выводит #ЧИСЛО!
Либо я что-то не так делаю.., делал все по инструкции, разьеденил ячейки и в ставил как массив.
Записан

vikttur

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +46/-0
  • Оффлайн Оффлайн
  • Сообщений: 912
Re: Наибольшее значение, условие
« Ответ #16 : 24.05.2017, 23:14:04 »

Где же не работает? Работает. Давайте еще сообщений по 5 напишем... Покажите, ведь так легче понять, в чем ошибка, и не нужно гадать.
Записан

Виталий Поликарпов

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 9
Re: Наибольшее значение, условие
« Ответ #17 : 25.05.2017, 00:10:27 »

Вот так сейчас у меня.
Записан

vikttur

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +46/-0
  • Оффлайн Оффлайн
  • Сообщений: 912
Re: Наибольшее значение, условие
« Ответ #18 : 25.05.2017, 09:41:30 »

Вот, теперь ошибка видна. Появилась при копировании/вставке в сообщение, в моем файле рабочая.
Вместо константы (28) нужно указать ссылку на ячейку ниже, на T10.
Записан

zs5

  • Постоялец
  • ***
  • Уважение: +4/-0
  • Оффлайн Оффлайн
  • Сообщений: 160
Re: Наибольшее значение, условие
« Ответ #19 : 25.05.2017, 10:24:29 »

=ЕСЛИ(СЧЁТЕСЛИ(Q4:Q59;НАИБОЛЬШИЙ(Q4:Q59;1))>1;СМЕЩ(B4;ПОИСКПОЗ(НАИБОЛЬШИЙ(Q4:Q59;1);Q4:Q59;0);0);ИНДЕКС(B4:B59;ПОИСКПОЗ(НАИБОЛЬШИЙ(Q4:Q59;2);Q4:Q59;0);1))
Записан

Виталий Поликарпов

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 9
Re: Наибольшее значение, условие
« Ответ #20 : 25.05.2017, 11:09:37 »

Вот, теперь ошибка видна. Появилась при копировании/вставке в сообщение, в моем файле рабочая.
Вместо константы (28) нужно указать ссылку на ячейку ниже, на T10.
Спасибо огромное!!! Все работает!!
Записан

IKor

  • Старожил
  • ****
  • Уважение: +49/-0
  • Оффлайн Оффлайн
  • Сообщений: 902
Re: Наибольшее значение, условие
« Ответ #21 : 25.05.2017, 18:36:40 »

Если еще актуально:

Обычно для того, чтобы отсортировать формулами таблицу такого вида, я предпочитаю добавлять к целевой переменной (обычно числовой) уникальный индекс (обычно на базе номера строки)
 => ЦелеваяПеременная + СТРОКА()/1000
 => ЦелеваяПеременная + (1000-СТРОКА())/1000
здесь 1000 - это число гарантированно большее числа сортируемых строк.

Во избежание путаницы вспомогательный уникальный индекс можно скрывать при помощи форматирования ячеек.
Записан

zs5

  • Постоялец
  • ***
  • Уважение: +4/-0
  • Оффлайн Оффлайн
  • Сообщений: 160
Re: Наибольшее значение, условие
« Ответ #22 : 26.05.2017, 08:01:22 »

Обычно для того, чтобы отсортировать формулами таблицу такого вида, я предпочитаю добавлять к целевой переменной
актуально: что считать "ЦелеваяПеременная" в актуальной задаче
Записан

IKor

  • Старожил
  • ****
  • Уважение: +49/-0
  • Оффлайн Оффлайн
  • Сообщений: 902
Re: Наибольшее значение, условие
« Ответ #23 : 30.05.2017, 16:46:04 »

актуально: что считать "ЦелеваяПеременная" в актуальной задаче
Если добавить "уникальный индекс на базе номера строки" к данным из столбца Q (см. первое сообщение в теме), то значения ячеек Q7 и Q8 станут отличаться друг от друга, что позволит не только функциям НАИБОЛЬШИЙ(;k)/НАИМЕНЬШИЙ(;k) выбирать оба результата, но и с помощью функции ПОИСКПОЗ(;;0) точно определять номера соответствующих строк.

Из личного опыта могу сказать, что для поиска номера строки вместо ПОИСКПОЗ() можно использовать комбинацию функций вида:
номер_строки = ОТБР(1000*(НАИБОЛЬШИЙ(;k)-ОТБР(НАИБОЛЬШИЙ(;k);0));0)
однако, зачастую результат вычисления оказывается некорректно округленным.

Поэтому я предпочитаю преобразовывать результаты функции НАИБОЛЬШИЙ() в текстовый формат; вырезать из него нужные номера строк; а затем преобразовывать их обратно в числовой формат...
Записан

zs5

  • Постоялец
  • ***
  • Уважение: +4/-0
  • Оффлайн Оффлайн
  • Сообщений: 160
Re: Наибольшее значение, условие
« Ответ #24 : 31.05.2017, 09:14:23 »

Поэтому я предпочитаю преобразовывать результаты функции НАИБОЛЬШИЙ() в текстовый формат; вырезать из него нужные номера строк; а затем преобразовывать их обратно в числовой формат...
Perche... Сложить значение с номером строки
« Последнее редактирование: 31.05.2017, 10:06:29 от zs5 »
Записан
 



Темы без ответов

06.09.2017 10:43 Solver VBA не решает гиперболическое уравнение, но при этом решает гармоническое 104
17.08.2017 12:15 Гиперссылка и фильтр одновременно макрос 222
13.06.2017 00:27 Сводная таблица: как не вручную отсортировать в опред. порядке (не Custom List) 623
23.05.2017 11:20 Копирование данных из одной таблицы в умную таблицу по условию 1081
18.05.2017 15:45 Не работает гиперссылка при копировании. 592
15.03.2017 15:45 автозамена картинок PowerPoint 835
13.03.2017 07:09 Использование базы КЛАДР в exel 1183
11.03.2017 13:43 Изменить нумерацию страниц 1059
10.03.2017 08:40 Как делать бекапы гугл таблицы? 900
18.02.2017 11:31 Изменить ввод данных помогите...из столбца в таблицу. 1132





Яндекс цитирования msexcel.ru Яндекс.Метрика

Страница сгенерирована за 0.14 секунд. Запросов: 71.