Расчет количеств рейсов в заданном диапазоне дат и частотах - Страница 3

Дата: Четверг, 11.10.2012, 18:23 | Сообщение № 41

Группа: Админы

Ранг: Участник клуба

Сообщений: 2851

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

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Quote (MCH)

Для файла с нормальными датами (как в файле Avia_RealDates.xlsx из 23 сообщения) - ~~115~~ 91

Вот же ж! Только хотел написать про свои 96...

Quote (_Boroda_)

Однако 236

ну ваще...

Александр, а у тебя вариант с нормальными датами сколько сейчас?

Дата: Пятница, 12.10.2012, 01:05 | Сообщение № 42

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

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

Сообщений: 391

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

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

Office 365

322

Дата: Пятница, 12.10.2012, 01:33 | Сообщение № 43

Группа: Админы

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

Сообщений: 2008

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

Замечаний: ±

Quote (_Boroda_)

Однако 236 Правда, зааадууумчиииваааяяя получилась.

Тормознутую, с использованием СТРОКА($1:$45000) уместил в 231
Если использовать СТРОКА(F:F), то тормозов еще больше, но размер - 219. и работает только в 2007+

Мне больше нравятся более быстрые формулы 271/287 (летучая, не летучая)

Дата: Пятница, 12.10.2012, 14:28 | Сообщение № 44

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

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

Сообщений: 391

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

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

Office 365

1.
Очень понравилось пересечение диапазонов у Michael_S с помощью пробела

Code

=СТРОКА((СМЕЩ($A$1;$T3-1;;$U3-$T3+1) СМЕЩ($A$1;W$19-1;;W$20-W$19+1)))

Надо будет развить эту тему.
2.
Никак не соображу, как превратить {41366;41573} в {41366;41367,41368;...41573}, используя ссылку на {41366;41573} один раз.
Т.е. без МИН({41366;41573}) и МАКС({41366;41573}).

Дата: Пятница, 12.10.2012, 14:44 | Сообщение № 45

Группа: Админы

Ранг: Участник клуба

Сообщений: 2851

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

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Quote (ZORRO2005)

1. Очень понравилось пересечение диапазонов у Michael_S с помощью пробела
=СТРОКА((СМЕЩ($A$1;$T3-1;;$U3-$T3+1) СМЕЩ($A$1;W$19-1;;W$20-W$19+1)))
Надо будет развить эту тему.

Ура! Ширим наши ряды!

http://www.excelworld.ru/forum/2-2110-23120-16-1343991145
http://www.excelworld.ru/forum/2-2110-23134-16-1343998807 (здесь про штурм)
http://www.excelworld.ru/forum/2-2110-23201-16-1344119475
http://www.excelworld.ru/forum/2-2110-23218-16-1344165632
http://www.excelworld.ru/forum/2-2110-23232-16-1344189074

И даешь мозговой штурм на тему, где это еще можно применить!

Дата: Пятница, 12.10.2012, 14:58 | Сообщение № 46

Группа: Админы

Ранг: Участник клуба

Сообщений: 2851

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

Замечаний: ±

начинал с Excel 4.0, видел 2.1

P.S.

Quote (ZORRO2005)

2. Никак не соображу, как превратить {41366;41573} в {41366;41367,41368;...41573}, используя ссылку на {41366;41573} один раз. Т.е. без МИН({41366;41573}) и МАКС({41366;41573}).

Главное, пунктом 1. практически сам себе ответил... wink

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

Группа: Админы

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

Сообщений: 2008

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

Замечаний: ±

Quote (Gustav)

Главное, пунктом 1. практически сам себе ответил...

не совсем так, за один вызов не получается, за два - можно:
[vba]

Code

=СТРОКА(ДВССЫЛ(МИН({41366;41573})&":"&МАКС({41366;41573})))

[/vba]

Сообщение отредактировал MCH - Пятница, 12.10.2012, 17:24

Дата: Пятница, 12.10.2012, 17:02 | Сообщение № 48

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

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

Сообщений: 391

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

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

Office 365

Quote (MCH)

не совсем так, за один вызов не получается, за два - можно:

За 2 я знаю как сделать.

Code

=СТРОКА(ДВССЫЛ(МИН({41366;41573})&":"&МАКС({41366;41573})))

А как за 1?
Пробовал через

Code

=СТРОКА(СМЕЩ(A1;;;{41366;41573}))*{-1;1}

, дальше МУМНОЖ, но НД мешает.
Все это касается данного примера и ячеек A3:B3.

Сообщение отредактировал ZORRO2005 - Пятница, 12.10.2012, 17:05

Дата: Пятница, 12.10.2012, 17:30 | Сообщение № 49

Группа: Админы

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

Сообщений: 2008

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

Замечаний: ±

можно попробовать так:
[vba]

Code

=ПРОСМОТР(СТРОКА($40000:$45000);{41366;41573}+{0;1};1/{1;0})*СТРОКА($40000:$45000)

[/vba]
Получим массив:
#Н/Д
…
#Н/Д
41366
41367
…
41572
41573
#ДЕЛ/0!
…
#ДЕЛ/0!

Но получается длинно и нужно потом еще ошибки обработать

Дата: Пятница, 12.10.2012, 21:33 | Сообщение № 50

Группа: Админы

Ранг: Участник клуба

Сообщений: 2851

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

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Quote (ZORRO2005)

За 2 я знаю как сделать.
А как за 1?

Как за 1 - я тоже НЕ знаю, но, может, обойтись вообще без минимаксов?

Дата: Суббота, 13.10.2012, 13:37 | Сообщение № 51

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

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

Сообщений: 391

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

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

Office 365

Миша, вектор понял. Спасибо.

Quote (Gustav)

Как за 1 - я тоже НЕ знаю, но, может, обойтись вообще без минимаксов?

Это мы увидим в понедельник.

Сообщение отредактировал ZORRO2005 - Суббота, 13.10.2012, 13:39

vikttur

Дата: Суббота, 13.10.2012, 22:54 | Сообщение № 52

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

Ранг: Участник клуба

Сообщений: 2941

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

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

У меня с первого захода с нормальными датами ~~142~~.
Позже с подсказкой Михаила - ~~108~~ ~~104~~ 96

Сообщение отредактировал vikttur - Воскресенье, 14.10.2012, 00:41

Дата: Понедельник, 15.10.2012, 10:33 | Сообщение № 53

Группа: Админы

Ранг: Участник клуба

Сообщений: 2851

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

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Понедельник. Вскрытие, обмен решениями. Вскрываюсь.

Для самого первого файла (из сообщения 1) с НЕнормальными датами - массивная, 286 знаков (ниже их чуть больше из-за добавленных в целях удобочитаемости переносов строк), для ячейки F3:
[vba]

Code

=СУММ(1-ЕОШ(НАЙТИ(ДЕНЬНЕД(СТРОКА(
ДВССЫЛ(--(F$2&"-"&ПСТР($A3;6;4))
&":"&ДАТАМЕС(F$2&"-"&ПСТР($A3;6;4);1)-1)
ДВССЫЛ(--ЗАМЕНИТЬ($A3;3;3;"."&ПОИСК(ПСТР($A3;4;2);"_AnEbArPrAyUnUlUgEpCtOvEc")/2&".")
&":"&--ЗАМЕНИТЬ($B3;3;3;"."&ПОИСК(ПСТР($B3;4;2);"_AnEbArPrAyUnUlUgEpCtOvEc")/2&"."))
);2);$C3)))

[/vba]
Пробел между двумя ДВССЫЛ обязателен (как признак пересечения диапазонов).

Для файла с нормальными датами (из сообщения 23) - массивная, 96 знаков (аналогично ниже их чуть больше), для ячейки F3:
[vba]

Code

=СУММ(1-ЕОШ(НАЙТИ(ДЕНЬНЕД(СТРОКА(
ДВССЫЛ(F$2&":"&ДАТАМЕС(F$2;1)-1)
ДВССЫЛ($A3&":"&$B3)
);2);$C3)))

[/vba]

Дата: Понедельник, 15.10.2012, 11:34 | Сообщение № 54

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

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

Сообщений: 391

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

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

Office 365

Gustav, не сочтите за придирку.

Quote (ZORRO2005)

Предлагаю ничего в файле не менять и период полетов может быть любой. Иначе это не мозговой штурм.

Главная сложность формулы (для меня) была в том , что период полетов может быть любой. Т.е. и год в столбцах A и B разный.

Дата: Понедельник, 15.10.2012, 11:43 | Сообщение № 55

Группа: Админы

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

Сообщений: 2008

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

Замечаний: ±

Quote (Gustav)

Понедельник. Вскрытие, обмен решениями. Вскрываюсь.

У меня что то похожее, только итоговый подсчет я сделал чуть короче
Про пробел тоже признаюсь - подсмотрел у Михаила С.
271:
[vba]

Code

=СЧЁТ(1/ПОИСК(ДЕНЬНЕД(СТРОКА(ДВССЫЛ(--ЗАМЕНИТЬ($A3;3;3;"."&ПОИСК(ПСТР($A3;4;2);" anebarprayunulugepctovec")/2&".")&":"&--ЗАМЕНИТЬ($B3;3;3;"."&ПОИСК(ПСТР($B3;4;2);" anebarprayunulugepctovec")/2&".")) ДВССЫЛ(--(F$2&ПРАВБ($A3;4))&":"&КОНМЕСЯЦА(F$2&ПРАВБ($B3;4);0)));2);$C3))

[/vba]
91:
[vba]

Code

=СЧЁТ(1/ПОИСК(ДЕНЬНЕД(СТРОКА(ДВССЫЛ($A3&":"&$B3) ДВССЫЛ(F$2&":"&КОНМЕСЯЦА(F$2;0)));2);$C3))

[/vba]

Quote (ZORRO2005)

Главная сложность формулы (для меня) была в том , что период полетов может быть любой. Т.е. и год в столбцах A и B разный

А как это решать, даже не знаю, к какому году привязывать месяцы написанные текстом?

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

Сообщение отредактировал MCH - Понедельник, 15.10.2012, 11:45

vikttur

Дата: Понедельник, 15.10.2012, 12:05 | Сообщение № 56

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

Ранг: Участник клуба

Сообщений: 2941

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

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

96, массивная:

Code

=СУММ(1-ЕОШ(ПОИСК(ДЕНЬНЕД(СТРОКА(ДВССЫЛ(F$2&":"&КОНМЕСЯЦА(F$2;0)) ДВССЫЛ($A3&":"&$B3));2);$C3)))

Дата: Понедельник, 15.10.2012, 12:05 | Сообщение № 57

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

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

Сообщений: 391

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

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

Office 365

Quote (MCH)

А как это решать, даже не знаю, к какому году привязывать месяцы написанные текстом?

Например:
Период: 02APR2010 - 26OCT2013
столбец F(апр)
29 дней в 2010, и по 30 дней 2011,2012 Итого:89 дней
F3:

Code

=СУММ(--НЕ(ЕОШ(1/Ч(ТЕКСТ(СТРОКА(ДВССЫЛ(--ЗАМЕНИТЬ($A3;3;3;"."&ПОИСКПОЗ(ПСТР($A3;3;3);ТЕКСТ(СТРОКА($1:$12)*30;"[$-FC]МММ");)&".")&":"&--ЗАМЕНИТЬ($B3;3;3;"."&ПОИСКПОЗ(ПСТР($B3;3;3);ТЕКСТ(СТРОКА($1:$12)*30;"[$-FC]МММ");)&".")));"МММДДД")=F$2&ПРОСМОТР(--ПСТР($C3;СТОЛБЕЦ($A:$L);1);СТРОКА($1:$7);ТЕКСТ(СТРОКА($2:$8);"ДДД"))))))

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

Сообщение отредактировал ZORRO2005 - Понедельник, 15.10.2012, 12:21

Дата: Понедельник, 15.10.2012, 12:28 | Сообщение № 58

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

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

Сообщений: 391

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

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

Office 365

Дату в A3 вначале преобразовал так:

Code

=МАКС((ТЕКСТ(СТРОКА($1:$99999);"[$-FC]ДДМММГГГГ")=A3)*СТРОКА($1:$99999))

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

Сообщение отредактировал ZORRO2005 - Понедельник, 15.10.2012, 12:29

Дата: Понедельник, 15.10.2012, 12:29 | Сообщение № 59

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

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

Сообщений: 286

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

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

Excel 2010 RUS

Привет всем.
До двух букв в месяце не додумался, а так примерно тоже.
Миш, а зачем ты грузишь формулу делением (..1/поиск...) ?
Первую точку в ЗАМЕНИТЬ можно поменять на минус
"...ЗАМЕНИТЬ($A3;3;3;-ПОИСК(ПСТР($A3;4;2);..."

Дата: Понедельник, 15.10.2012, 12:56 | Сообщение № 60

Группа: Админы

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

Сообщений: 2008

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

Замечаний: ±

Quote (DV)

Миш, а зачем ты грузишь формулу делением (..1/поиск...)

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

Quote (DV)

Первую точку в ЗАМЕНИТЬ можно поменять на минус

Тоже интересный ход

итого формула усохла до 263/89 знаков
[vba]

Code

=СЧЁТ(ПОИСК(ДЕНЬНЕД(СТРОКА(ДВССЫЛ(--ЗАМЕНИТЬ($A3;3;3;-ПОИСК(ПСТР($A3;4;2);" anebarprayunulugepctovec")/2&"-")&":"&--ЗАМЕНИТЬ($B3;3;3;-ПОИСК(ПСТР($B3;4;2);" anebarprayunulugepctovec")/2&"-")) ДВССЫЛ(--(F$2&ПРАВБ($A3;4))&":"&КОНМЕСЯЦА(F$2&ПРАВБ($B3;4);0)));2);$C3))

[/vba]

Сообщение отредактировал MCH - Понедельник, 15.10.2012, 13:09