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

Вход

Регистрация

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

 

= Мир MS Excel/Наименьшее и наибольшее значение с условием - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Наименьшее и наибольшее значение с условием
Наименьшее и наибольшее значение с условием
nlad Дата: Среда, 20.03.2013, 22:07 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Здравствуйте!
Подскажите пожалуйста, имеется столбец значений в формате ДАТА Время, как выбрать наименьшее значение, и наибольшее значение, с условием пропуска всех значений до 8 утра, до после 24 часов заставить взять значение до 8 утра следующего дня?

01.02.2013 8:51
01.02.2013 15:58
01.02.2013 16:05
01.02.2013 18:00
01.02.2013 20:21
02.02.2013 01:58
 
Ответить
СообщениеЗдравствуйте!
Подскажите пожалуйста, имеется столбец значений в формате ДАТА Время, как выбрать наименьшее значение, и наибольшее значение, с условием пропуска всех значений до 8 утра, до после 24 часов заставить взять значение до 8 утра следующего дня?

01.02.2013 8:51
01.02.2013 15:58
01.02.2013 16:05
01.02.2013 18:00
01.02.2013 20:21
02.02.2013 01:58
Автор - nlad
Дата добавления - 20.03.2013 в 22:07
Pelena Дата: Среда, 20.03.2013, 22:14 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19515
Репутация: 4632 ±
Замечаний: ±

Excel 365 & Mac Excel
Здравствуйте.
nlad, Вы быстрее получите ответ, если приложите файл с примером в формате xls: что есть и что надо получить

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
nlad, Вы быстрее получите ответ, если приложите файл с примером в формате xls: что есть и что надо получить
Автор - Pelena
Дата добавления - 20.03.2013 в 22:14
Michael_S Дата: Среда, 20.03.2013, 22:29 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 373 ±
Замечаний: 0% ±

Excel2016
Цитата (nlad)
с условием пропуска всех значений до 8 утра, до после 24 часов заставить взять значение до 8 утра следующего дня?

чет я не понял этой фразы. И чем больше вчитываюсь, тем не понятней... surprised
 
Ответить
Сообщение
Цитата (nlad)
с условием пропуска всех значений до 8 утра, до после 24 часов заставить взять значение до 8 утра следующего дня?

чет я не понял этой фразы. И чем больше вчитываюсь, тем не понятней... surprised
Автор - Michael_S
Дата добавления - 20.03.2013 в 22:29
AlexM Дата: Среда, 20.03.2013, 22:35 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Я понял так.
От 8 до 24 и после 24 до 8 следующего дня.
Надо найти Мин и Макс времени с 8 до 8 следующего дня.
Похоже ответ это первая и последняя строка времени из задания.

Решил, как понял задачу.
Столбец А с датами и временем
В В1 дата для которой ищем Мин и Макс
Фомула Мин
Код
=МИН(ИНДЕКС(($A$1:$A$8>=($B$1+"8:00"))*($A$1:$A$8<=(1+$B$1+"8:00"))*9^9+$A$1:$A$8;))

Формула Макс
Код
=МАКС(ИНДЕКС(($A$1:$A$8>=($B$1+"8:00"))*($A$1:$A$8<=(1+$B$1+"8:00"))*$A$1:$A$8;))
К сообщению приложен файл: nlad1.xls (13.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Четверг, 21.03.2013, 10:58
 
Ответить
СообщениеЯ понял так.
От 8 до 24 и после 24 до 8 следующего дня.
Надо найти Мин и Макс времени с 8 до 8 следующего дня.
Похоже ответ это первая и последняя строка времени из задания.

Решил, как понял задачу.
Столбец А с датами и временем
В В1 дата для которой ищем Мин и Макс
Фомула Мин
Код
=МИН(ИНДЕКС(($A$1:$A$8>=($B$1+"8:00"))*($A$1:$A$8<=(1+$B$1+"8:00"))*9^9+$A$1:$A$8;))

Формула Макс
Код
=МАКС(ИНДЕКС(($A$1:$A$8>=($B$1+"8:00"))*($A$1:$A$8<=(1+$B$1+"8:00"))*$A$1:$A$8;))
Автор - AlexM
Дата добавления - 20.03.2013 в 22:35
nlad Дата: Четверг, 21.03.2013, 23:41 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Добавил файл. Есть с пару десятков сотрудников, и куча информации на листе Выходы

AlexM
Постараюсь разобраться с вашими формулами. А что значит *9^9?
К сообщению приложен файл: TEST-.xlsm (23.0 Kb)
 
Ответить
СообщениеДобавил файл. Есть с пару десятков сотрудников, и куча информации на листе Выходы

AlexM
Постараюсь разобраться с вашими формулами. А что значит *9^9?
Автор - nlad
Дата добавления - 21.03.2013 в 23:41
AlexM Дата: Четверг, 21.03.2013, 23:51 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Цитата (nlad)
А что значит *9^9?

Это очень большое число 9 в степени 9
В формуле МАКС ненужные (не попадающие с 8 до 8 следующего дня) значения в массиве приравниваются к 0
В формуле МИН ненужные значения делаем очень большими 9^9
Тогда формулы МАКС и МИН находят правильные значения.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Цитата (nlad)
А что значит *9^9?

Это очень большое число 9 в степени 9
В формуле МАКС ненужные (не попадающие с 8 до 8 следующего дня) значения в массиве приравниваются к 0
В формуле МИН ненужные значения делаем очень большими 9^9
Тогда формулы МАКС и МИН находят правильные значения.
Автор - AlexM
Дата добавления - 21.03.2013 в 23:51
nlad Дата: Пятница, 22.03.2013, 21:45 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Попробовал сейчас использовать массив для МИН, не вышло, где у меня ошибка? Вставлял в ячейку B5

Цитата
=ЕСЛИОШИБКА(ИНДЕКС(Выходы!$C$2:$C$32065>=(Иванов!$C$3+"8:00")*(Выходы!$C$2:$C$32065<=(1+Иванов!$C$3+"8:00")*9^9+Выходы!$C$2:$C$32065;ПОИСКПОЗ($A$1&C$3&$A$5;Выходы!$D$2:$D$32065&Выходы!$A$2:$A$32065&Выходы!$B$2:$B$32065;0));"")
К сообщению приложен файл: TEST2.xlsx (16.3 Kb)


Сообщение отредактировал nlad - Пятница, 22.03.2013, 21:49
 
Ответить
СообщениеПопробовал сейчас использовать массив для МИН, не вышло, где у меня ошибка? Вставлял в ячейку B5

Цитата
=ЕСЛИОШИБКА(ИНДЕКС(Выходы!$C$2:$C$32065>=(Иванов!$C$3+"8:00")*(Выходы!$C$2:$C$32065<=(1+Иванов!$C$3+"8:00")*9^9+Выходы!$C$2:$C$32065;ПОИСКПОЗ($A$1&C$3&$A$5;Выходы!$D$2:$D$32065&Выходы!$A$2:$A$32065&Выходы!$B$2:$B$32065;0));"")
Автор - nlad
Дата добавления - 22.03.2013 в 21:45
AlexM Дата: Пятница, 22.03.2013, 22:38 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Приложите файл, как рекомендовано в правилах.
Ваши файлы мне посмотреть не удалось.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеПриложите файл, как рекомендовано в правилах.
Ваши файлы мне посмотреть не удалось.
Автор - AlexM
Дата добавления - 22.03.2013 в 22:38
nlad Дата: Суббота, 23.03.2013, 11:46 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Другие формулы в файле показывают почти правильные данные, осталось разобраться как корректно вставить это условие) Спасибо!
К сообщению приложен файл: TEST3.xls (41.5 Kb)
 
Ответить
СообщениеДругие формулы в файле показывают почти правильные данные, осталось разобраться как корректно вставить это условие) Спасибо!
Автор - nlad
Дата добавления - 23.03.2013 в 11:46
AlexM Дата: Суббота, 23.03.2013, 12:09 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Я не знаю что происходит при открытии ваших файлов.
Предыдущие конвертировались в 2003-ий, а при открытии подвисали при заполнении строк 5 и 6 с датами. Даты превращались в ошибку #ИМЯ. Файл ТЕСТ3 ведет себя точно так же.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеЯ не знаю что происходит при открытии ваших файлов.
Предыдущие конвертировались в 2003-ий, а при открытии подвисали при заполнении строк 5 и 6 с датами. Даты превращались в ошибку #ИМЯ. Файл ТЕСТ3 ведет себя точно так же.
Автор - AlexM
Дата добавления - 23.03.2013 в 12:09
Michael_S Дата: Суббота, 23.03.2013, 12:37 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 373 ±
Замечаний: 0% ±

Excel2016
nlad, строка
01.02.2013 - Выход - 02.02.2013 0:02 - Иванов
относится к выходам 01.02.2013 или 02.02.2013?


Сообщение отредактировал Michael_S - Суббота, 23.03.2013, 12:37
 
Ответить
Сообщениеnlad, строка
01.02.2013 - Выход - 02.02.2013 0:02 - Иванов
относится к выходам 01.02.2013 или 02.02.2013?
Автор - Michael_S
Дата добавления - 23.03.2013 в 12:37
nlad Дата: Суббота, 23.03.2013, 13:09 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Цитата (AlexM)
Предыдущие конвертировались в 2003-ий, а при открытии подвисали при заполнении строк 5 и 6 с датами.

Там формулы
Код
{=ЕСЛИОШИБКА(ИНДЕКС(Выходы!$C$2:$C$32067;ПОИСКПОЗ($A$1&D$3&$A$5;Выходы!$D$2:$D$32067&Выходы!$A$2:$A$32067&Выходы!$B$2:$B$32067;0));"")}


Цитата (Michael_S)
относится к выходам 01.02.2013 или 02.02.2013?

Вот с этим как раз проблема. Некоторые люди работают внутри дня, тогда можно использовать имеющиеся там формулы. А некоторые работают с переходом из одного дня в другой, но фактически это один рабочий день. Поэтому я и создал тему как можно выбрать значения с условием. Поэтому отвечая на ваш вопрос, выход 02.02.2013 0:02 относится к рабочему дню 1 февраля
 
Ответить
Сообщение
Цитата (AlexM)
Предыдущие конвертировались в 2003-ий, а при открытии подвисали при заполнении строк 5 и 6 с датами.

Там формулы
Код
{=ЕСЛИОШИБКА(ИНДЕКС(Выходы!$C$2:$C$32067;ПОИСКПОЗ($A$1&D$3&$A$5;Выходы!$D$2:$D$32067&Выходы!$A$2:$A$32067&Выходы!$B$2:$B$32067;0));"")}


Цитата (Michael_S)
относится к выходам 01.02.2013 или 02.02.2013?

Вот с этим как раз проблема. Некоторые люди работают внутри дня, тогда можно использовать имеющиеся там формулы. А некоторые работают с переходом из одного дня в другой, но фактически это один рабочий день. Поэтому я и создал тему как можно выбрать значения с условием. Поэтому отвечая на ваш вопрос, выход 02.02.2013 0:02 относится к рабочему дню 1 февраля
Автор - nlad
Дата добавления - 23.03.2013 в 13:09
Michael_S Дата: Суббота, 23.03.2013, 13:18 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 373 ±
Замечаний: 0% ±

Excel2016
Цитата (nlad)
выход 02.02.2013 0:02 относится к рабочему дню 1 февраля

Т.е., если рабочий день считается по дате первого столбца? даже если вход и выход глубоко за полночь?
 
Ответить
Сообщение
Цитата (nlad)
выход 02.02.2013 0:02 относится к рабочему дню 1 февраля

Т.е., если рабочий день считается по дате первого столбца? даже если вход и выход глубоко за полночь?
Автор - Michael_S
Дата добавления - 23.03.2013 в 13:18
nlad Дата: Суббота, 23.03.2013, 13:24 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Цитата (Michael_S)
считается по дате первого столбца? даже если вход и выход глубо


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


Возможно там моя ошибка. В первом столбце должно быть как в столбце C, просто дата за вычетом времени. Очень много значений входов и выходов, поэтому хотел максимально все отдать формулам.
Автор - nlad
Дата добавления - 23.03.2013 в 13:24
DV Дата: Суббота, 23.03.2013, 13:32 | Сообщение № 15
Группа: Друзья
Ранг: Обитатель
Сообщений: 286
Репутация: 194 ±
Замечаний: 0% ±

Excel 2010 RUS
Медленный вариант с ПРОСМОТР:
Код
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/((Выходы!$D$2:$D$32067=$A$1)*(ОТБР(Выходы!$C$2:$C$32067-8/24)=--C$3)*(Выходы!$B$2:$B$32067="Выход"));Выходы!$C$2:$C$32067);"")
К сообщению приложен файл: TEST3_2.xlsx (16.4 Kb)
 
Ответить
СообщениеМедленный вариант с ПРОСМОТР:
Код
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/((Выходы!$D$2:$D$32067=$A$1)*(ОТБР(Выходы!$C$2:$C$32067-8/24)=--C$3)*(Выходы!$B$2:$B$32067="Выход"));Выходы!$C$2:$C$32067);"")
Автор - DV
Дата добавления - 23.03.2013 в 13:32
nlad Дата: Суббота, 23.03.2013, 13:45 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Друг, работает! А можно ещё входы усложнить. Добавил вход и выход после полуночи, в результате выход корректен, а вход 2 февраля нет.
К сообщению приложен файл: 5387215.xlsx (16.5 Kb)
 
Ответить
СообщениеДруг, работает! А можно ещё входы усложнить. Добавил вход и выход после полуночи, в результате выход корректен, а вход 2 февраля нет.
Автор - nlad
Дата добавления - 23.03.2013 в 13:45
Michael_S Дата: Суббота, 23.03.2013, 13:50 | Сообщение № 17
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 373 ±
Замечаний: 0% ±

Excel2016
Пока делал файл, добавились условия. Формулы можно упростить, если убрать нули с помощью УФ и в параметрах страницы.

зы
Без первого столбцы формулы достаточно сложны, и не всегда посчитают правильно.

зы.зы
да, и по "Кол-во выходов из здания", в зависимости от требований, в формуле отнять 1, если не учитывать последний.
К сообщению приложен файл: TEST2_nlad.xlsx (17.1 Kb)


Сообщение отредактировал Michael_S - Суббота, 23.03.2013, 14:00
 
Ответить
СообщениеПока делал файл, добавились условия. Формулы можно упростить, если убрать нули с помощью УФ и в параметрах страницы.

зы
Без первого столбцы формулы достаточно сложны, и не всегда посчитают правильно.

зы.зы
да, и по "Кол-во выходов из здания", в зависимости от требований, в формуле отнять 1, если не учитывать последний.
Автор - Michael_S
Дата добавления - 23.03.2013 в 13:50
nlad Дата: Суббота, 23.03.2013, 14:12 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Michael_S
спасибо, красиво! я постараюсь разобраться сейчас
 
Ответить
СообщениеMichael_S
спасибо, красиво! я постараюсь разобраться сейчас
Автор - nlad
Дата добавления - 23.03.2013 в 14:12
Michael_S Дата: Суббота, 23.03.2013, 14:24 | Сообщение № 19
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 373 ±
Замечаний: 0% ±

Excel2016
Вариант с УФ и с последний выход не учитываем.
К сообщению приложен файл: TEST2_nlad_1.xlsx (17.0 Kb)
 
Ответить
СообщениеВариант с УФ и с последний выход не учитываем.
Автор - Michael_S
Дата добавления - 23.03.2013 в 14:24
nlad Дата: Воскресенье, 24.03.2013, 08:33 | Сообщение № 20
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Если напротив 02.02.2013 0:02 изменить значение в столбце А на '02.02.2013, показывает будто 02.02.2013 0:02 - последний выход 2 февраля(
К сообщению приложен файл: TEST2_nlad_1-1-.xlsx (17.0 Kb)
 
Ответить
СообщениеЕсли напротив 02.02.2013 0:02 изменить значение в столбце А на '02.02.2013, показывает будто 02.02.2013 0:02 - последний выход 2 февраля(
Автор - nlad
Дата добавления - 24.03.2013 в 08:33
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Наименьшее и наибольшее значение с условием
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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