Количество ячеек с датами из заданного интервала

[kompilainenn]

Есть столбец с датами (формат ячеек ДАТА ессно). В отдельной ячейке нужно посчитать, сколько ячеек с датами попали в заданный интервал. Первая и последняя дата в вопросе должны быть учтены при подсчете, то есть "включая начальную и конечную даты".

[rami]

Есть столбец с датами (формат ячеек ДАТА ессно). В отдельной ячейке нужно посчитать, сколько ячеек с датами попали в заданный интервал. Первая и последняя дата в вопросе должны быть учтены при подсчете, то есть "включая начальную и конечную даты".

Для твоего примера подойдёт формула:

Код Выделить Развернуть

=DATEDIF(A3,A13,"d")+1

Функция DATEDIF есть только в LibreOffice, в Apache OpenOffice её нет.

[kompilainenn]

хм... rami, это в столбце в примере даты идут одна за другой, на самом деле (некорректный пример видимо да) дата в вопросе может быть какой угодно (и начальная и конечная) и вовсе не обязательно она будет присутствовать в списке в столбце А! А сами даты в столбце А могут дублироваться по нескольку раз

[rami]

хм... rami, это в столбце в примере даты идут одна за другой, на самом деле (некорректный пример видимо да) дата в вопросе может быть какой угодно (и начальная и конечная) и вовсе не обязательно она будет присутствовать в списке в столбце А! А сами даты в столбце А могут дублироваться по нескольку раз

Ну, так сформулируй вопрос как надо. Сделай так: в столбце A список дат и правильный ответ, в столбце B другой список дат и соответствующий ответ и т.д. для разных случаев

[rami]

Тогда наверно так:

Код Выделить Развернуть

=COUNTIFS(A1:A16,">=" & "19/08/15",A1:A16,"<=" & "29/08/15")

[Алексей2015]

Пожалуйста, помогите. В моем случае не даты, но нечто подобное. Нужно срочно сделать работу. Имеется большая таблица с данными: возраст, пол. Нужно посмотреть распределение по возрастным интервалам до 1 г, 1-3 года, 4-6 лет, 7-17 лет и по полу: м-ж. Использовал функцию countifs. Проблема в том, что возраст в таблицу внесен по разному: 6м, 6 м, 6 мес, 6мес, 1г, 1 г, 1 г 3 м, 1г3м, 1 г 3 мес и т.д.  Как правильно задать в этом случае критерий отбора? Или совсем безнадежно?

[JohnSUN]

Сложно. Особенно если таблица действительно ОЧЕНЬ большая. Фрагмент таблицы для примера можно увидеть? Не всю, строк двадцать-тридцать, но с разными вариантами написания возрастов... Скорее всего придётся писать макрос. Впрочем, возможно удастся обойтись фильтром и одними только формулами. Смотреть надо...

[Алексей2015]

2г   м
9мес   ж
6 мес   ж
3г   м
11 мес   ж
3г   ж
9 лет   м
2г   ж
15 лет   м
2 г 11 мес   ж
3 г 10 мес   ж
1 г 10 мес   м
14 лет   ж
4 мес   ж
11 лет   ж
8 мес   м
11 лет   м
14 лет   м
12 лет   м
9л   ж
2г   ж
6 мес   ж
13 лет   м
3г5мес   ж
В основном два типа записей.

[JohnSUN]

Ну, я поступил следующим образом.
1. Выделил колонку Возраст, выбрал в меню Данные - Фильтр - Стандартный фильтр..., задал условие "Возраст = не пусто", в дополнительных параметрах попросил "Без повторений" и результат скопировал в новое место. Из 24 вариантов получилось 18.
2. Потом проделал самую скучную часть работы - рядом с каждым из значений вписал возраст в месяцах. Именно этот кусок я планировал реализовать макросом, но для небольшого объёма данных это можно сделать и вручную. Получил таблицу

Возраст	Месяцев
1 г 10 мес	22
11 лет	132
11 мес	11
12 лет	144
13 лет	156
14 лет	168
15 лет	180
2 г 11 мес	35
2г	24
3 г 10 мес	46
3г	36
3г5мес	41
4 мес	4
6 мес	6
8 мес	8
9 лет	108
9л	108
9мес	9

Эта таблица у меня находится по адресу $F$2:$G$19 (этот адрес будет использован в формуле)
3. Дальше уже проще. Дополняю таблицу данных колонкой Категория, в которую вписываю формулу

Код Выделить Развернуть

=CHOOSE(MATCH(VLOOKUP(A2;$F$2:$G$19;2;1);{0;12;36;72;204};1);"До 1 года";"От 1 до 3 лет";"От 4 до 7 лет";"Старше 7 лет")
Растягиваю формулу до конца таблицы и получаю таблицу в таком виде

Возраст	Пол	Категория
2г	м	От 1 до 3 лет
9мес	ж	До 1 года
...	...	...
6 мес	ж	До 1 года
13 лет	м	Старше 7 лет
3г5мес	ж	От 4 до 7 лет

4. Осталось натравить на получившиеся данные конструктор сводных таблиц (меню Данные - Сводная таблица - Создать) и получить окончательный ответ

Категория	ж	м	Итог
До 1 года	5	1	6
От 1 до 3 лет	3	2	5
От 4 до 7 лет	3	1	4
Старше 7 лет	3	6	9
Результат	14	10	24

[Алексей2015]

Огромное спасибо, Владислав. Без Вашей помощи, хоть вручную считай , а времени нет.

[JohnSUN]

А макрос для "перевода" текстовых возрастов в количество месяцев оказался не очень-то и сложным.
Эта функция хорошо справилась со всеми комбинациями из примера.

Код Выделить Развернуть

Function getMonthes(sourceTxt As String) As Integer
Const Digits = "0123456789"
Dim dig1&, dig2&, i&
Dim tS$, tC&
dig1=0
dig2=0
tS = Trim(sourceTxt)
REM Пропустить не цифры - возможны какие-то знаки до первого числа
i = 1
Do While (i <= Len(tS)) And (InStr(Digits,Mid(tS, i, 1))=0)
i = i + 1
Loop
REM Собираем из идущих подряд цифр первое число
Do While (i <= Len(tS)) And (InStr(Digits,Mid(tS, i, 1))>0)
dig1 = dig1 * 10 + Val(Mid(tS, i, 1))
i = i + 1
Loop
REM Пропустить пробелы (если есть)
Do While (i <= Len(tS)) And (Mid(tS, i, 1)=" ")
i = i + 1
Loop
REM Не на букву ли "М" (мес) натолкнулись?
REM На латинскую M тоже проверяем
If (UCase(Mid(tS, i, 1))="М") Or (UCase(Mid(tS, i, 1))="M") Then
getMonthes = dig1
Exit Function
EndIf
REM Пропустить все не цифры
Do While (i <= Len(tS)) And (InStr(Digits,Mid(tS, i, 1))=0)
i = i + 1
Loop
REM Собираем из идущих подряд цифр второе число
Do While (i <= Len(tS)) And (InStr(Digits,Mid(tS, i, 1))>0)
dig2 = dig2 * 10 + Val(Mid(tS, i, 1))
i = i + 1
Loop
REM Первое число (годы) переводим в месяцы и добавляем второе число
getMonthes = dig1 * 12 + dig2
End FunctionХоть задача и решена, но макрос пусть полежит здесь - авось, ещё кому сгодится

[rami]

Можно сделать проще:
1. Выделить данные возраста и с помощью найти и заменить см. снимок Найти и заменить.png
найти:

Код Выделить Развернуть

([:digit:]*)( *)(г|лет|л)(.*)
заменить на:

Код Выделить Развернуть

$1
2. В дополнительном столбце группы пишем формулу:

Код Выделить Развернуть

=IF(ISNUMBER(A2),IF(A2>3,IF(A2>6,"от семи до семнадцати","от четырёх до семи"),"от года до четырёх"),"до года")и растягиваем до низу см. снимок Результат.png
3. Создаём сводную таблицу по столбцам группы и пол

[Алексей2015]

Прошу прощения, при попытке вставить формулу на последнем этапе =IF(ISNUMBER(A2),IF(A2>3,IF(A2>6,"от семи до семнадцати","от четырёх до семи"),"от года до четырёх"),"до года") ругается - ошибка 501

[rami]

Прошу прощения, при попытке вставить формулу на последнем этапе =IF(ISNUMBER(A2),IF(A2>3,IF(A2>6,"от семи до семнадцати","от четырёх до семи"),"от года до четырёх"),"до года") ругается - ошибка 501

Для русской локали нужно запятые заменить на ; (точку с запятой) вот так:

Код Выделить Развернуть

=IF(ISNUMBER(A2);IF(A2>3;IF(A2>6;"от семи до семнадцати";"от четырёх до семи");"от года до четырёх");"до года")

[JohnSUN]

В мою предыдущую формулу вкралась досадная ошибка - массив {0;12;36;72;204} делит возраста на категории 0-1-3-6-17, а не 0-1-4-7-17, как требовалось в условии задачи. Сорри!
Окончательный вариант решения - в приложенном файле