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

Вход

Регистрация

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

 

= Мир MS Excel/Подсчет количества значений с учетом временного промежутка - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет количества значений с учетом временного промежутка (Формулы/Formulas)
Подсчет количества значений с учетом временного промежутка
Ikusya Дата: Четверг, 07.02.2019, 11:20 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Добрй день!

Уважаемые форумчане, подскажите, пожалуйста, как можно решить следующую проблему. В файле-примере есть ряд временных промежутков в формате ... г. ... мес. Мне нужно расчитать количество этих значений в зависимости от промежутка (сколько, напрмер, значений входит в интервал от года до трех, 3-5, 5-10 и тд.).

И, к сожалению, в моих формулах оказалось много погрешностей в расчетах, например: при вводе значения "3 г. 7 мес." значение считается и в нужном интервале от 3-5 лет, и дублируется в интервале больше 10 лет, а все значения больше 10 лет попадают под интервал с единицей (есть подозрение, что я допустила где-то ошибку с форматами, и Excel вместо 10 видит 1, или 0 г. 10 мес. тоже считывает как 0,1 и поэтому распределяет в другой интервал)

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

Буду очень благодарна, если получится объяснить, что не так сделала ^_^
К сообщению приложен файл: _2.xlsx(24.8 Kb)
 
Ответить
СообщениеДобрй день!

Уважаемые форумчане, подскажите, пожалуйста, как можно решить следующую проблему. В файле-примере есть ряд временных промежутков в формате ... г. ... мес. Мне нужно расчитать количество этих значений в зависимости от промежутка (сколько, напрмер, значений входит в интервал от года до трех, 3-5, 5-10 и тд.).

И, к сожалению, в моих формулах оказалось много погрешностей в расчетах, например: при вводе значения "3 г. 7 мес." значение считается и в нужном интервале от 3-5 лет, и дублируется в интервале больше 10 лет, а все значения больше 10 лет попадают под интервал с единицей (есть подозрение, что я допустила где-то ошибку с форматами, и Excel вместо 10 видит 1, или 0 г. 10 мес. тоже считывает как 0,1 и поэтому распределяет в другой интервал)

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

Буду очень благодарна, если получится объяснить, что не так сделала ^_^

Автор - Ikusya
Дата добавления - 07.02.2019 в 11:20
sboy Дата: Четверг, 07.02.2019, 11:41 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 722 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
Буду очень благодарна, если получится объяснить, что не так сделала

Вы пытаетесь сравнить текст арифметически. В этом случае "3г" больше чем "10г"
Как вариант примерно в днях посчитать.
Код
=ПСТР(A1;1;ПОИСК("г";A1)-2)*365+СЖПРОБЕЛЫ(ПСТР(A1;ПОИСК("г";A1)+3;2))*30,4
или разбить текст по столбцам и сравнивать года и месяца
К сообщению приложен файл: _2-8-.xlsx(26.3 Kb)


Яндекс: 410016850021169
 
Ответить
СообщениеДобрый день.
Буду очень благодарна, если получится объяснить, что не так сделала

Вы пытаетесь сравнить текст арифметически. В этом случае "3г" больше чем "10г"
Как вариант примерно в днях посчитать.
Код
=ПСТР(A1;1;ПОИСК("г";A1)-2)*365+СЖПРОБЕЛЫ(ПСТР(A1;ПОИСК("г";A1)+3;2))*30,4
или разбить текст по столбцам и сравнивать года и месяца

Автор - sboy
Дата добавления - 07.02.2019 в 11:41
_Boroda_ Дата: Четверг, 07.02.2019, 11:47 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15417
Репутация: 6032 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?

Для периодов типа "с-по"
Код
=СЧЁТ(1/(ЛЕВБ($A1:$A99;2)-ПСТР(G3;ПОИСК("y";G3)-3;2)<0))-СУММ($F4:F4)+СЧЁТЕСЛИ(A:A;--ПСТР(G3;ПОИСК("y";G3)-3;2)&" г. 0 мес.")
Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter

Для заключительного периода
Код
=СЧЁТЗ(A:A)-СУММ(G4:J4)


*До 100 лет
К сообщению приложен файл: _2-14-1.xlsx(24.4 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?

Для периодов типа "с-по"
Код
=СЧЁТ(1/(ЛЕВБ($A1:$A99;2)-ПСТР(G3;ПОИСК("y";G3)-3;2)<0))-СУММ($F4:F4)+СЧЁТЕСЛИ(A:A;--ПСТР(G3;ПОИСК("y";G3)-3;2)&" г. 0 мес.")
Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter

Для заключительного периода
Код
=СЧЁТЗ(A:A)-СУММ(G4:J4)


*До 100 лет

Автор - _Boroda_
Дата добавления - 07.02.2019 в 11:47
Ikusya Дата: Четверг, 07.02.2019, 12:46 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
sboy, огромное спасибо, формула работает отлично и синтаксис более чем понятен!

Мне просто сначала показалось, что за счет присвоения формата даты арифметически получится действия произвести, но теперь понимаю, что была не права) А решение оказалось намного ближе и проще, чем думалось)

А подскажите, пожалуйста, без создания дополнительного нового столбца вряд ли получится как-то объединить формулы? Это уже просто из любопытства для визуализации, чтобы оригинальный файл с намного бОльшим количеством данных не нагружать лишними столбцами
 
Ответить
Сообщениеsboy, огромное спасибо, формула работает отлично и синтаксис более чем понятен!

Мне просто сначала показалось, что за счет присвоения формата даты арифметически получится действия произвести, но теперь понимаю, что была не права) А решение оказалось намного ближе и проще, чем думалось)

А подскажите, пожалуйста, без создания дополнительного нового столбца вряд ли получится как-то объединить формулы? Это уже просто из любопытства для визуализации, чтобы оригинальный файл с намного бОльшим количеством данных не нагружать лишними столбцами

Автор - Ikusya
Дата добавления - 07.02.2019 в 12:46
_Boroda_ Дата: Четверг, 07.02.2019, 12:49 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15417
Репутация: 6032 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А подскажите, пожалуйста, без создания дополнительного нового столбца вряд ли получится как-то объединить формулы?

Я ж Вам так и сделал. Никаких допданных в файле нет, все только на том, что Вы предоставили


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
А подскажите, пожалуйста, без создания дополнительного нового столбца вряд ли получится как-то объединить формулы?

Я ж Вам так и сделал. Никаких допданных в файле нет, все только на том, что Вы предоставили

Автор - _Boroda_
Дата добавления - 07.02.2019 в 12:49
Ikusya Дата: Четверг, 07.02.2019, 12:59 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, да, спасибо, в целом это то, что нужно!

Единственное, для меня как не для совсем продвинутого пользователя сложноват к пониманию синтаксис. По отдельности со всеми функциями знакома и принцип вроде понимаю, но с большим количеством деталей уже сложнее. И в конечной таблице есть небольшие расхождения, т.е. вместо 1-8-4-21-21 в Вашем варианте должно быть 1-7-4-23-20

Если Вас не затруднит, не могли бы Вы поподробнее объяснить алгоритм расчета, пожалуйста? Мне наверное надо было уточнить, что с интерваласи 3-5, 5-10, например, значение 5 г. 0 мес. должно включаться в первый интервал, а во втором интервале уже строгое неравенство от 5 г. 0 мес. без включения

Но мне очень нравится Ваш вариант тем, что он обходится без каких-либо вспомогательных столбцов, это то, что нужно
 
Ответить
Сообщение_Boroda_, да, спасибо, в целом это то, что нужно!

Единственное, для меня как не для совсем продвинутого пользователя сложноват к пониманию синтаксис. По отдельности со всеми функциями знакома и принцип вроде понимаю, но с большим количеством деталей уже сложнее. И в конечной таблице есть небольшие расхождения, т.е. вместо 1-8-4-21-21 в Вашем варианте должно быть 1-7-4-23-20

Если Вас не затруднит, не могли бы Вы поподробнее объяснить алгоритм расчета, пожалуйста? Мне наверное надо было уточнить, что с интерваласи 3-5, 5-10, например, значение 5 г. 0 мес. должно включаться в первый интервал, а во втором интервале уже строгое неравенство от 5 г. 0 мес. без включения

Но мне очень нравится Ваш вариант тем, что он обходится без каких-либо вспомогательных столбцов, это то, что нужно

Автор - Ikusya
Дата добавления - 07.02.2019 в 12:59
_Boroda_ Дата: Четверг, 07.02.2019, 13:19 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15417
Репутация: 6032 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Мне наверное надо было уточнить, что с интерваласи 3-5, 5-10, например, значение 5 г. 0 мес. должно включаться в первый интервал, а во втором интервале уже строгое неравенство от 5 г. 0 мес. без включения
Это и так видно по той формуле, что у Вас в файле была

По поводу 1-7-... да, все правильно, это я не закрепил столбец в формуле. Вот так нужно
Код
=СЧЁТ(1/(ЛЕВБ($A1:$A99;2)-ПСТР(G3;ПОИСК("y";G3)-3;2)<0))-СУММ($F4:F4)+СЧЁТЕСЛИ($A:$A;--ПСТР(G3;ПОИСК("y";G3)-3;2)&" г. 0 мес.")


Пояснялка
1. ЛЕВБ($A1:$A99;2) = 2 символа слева в столбце А, получаем год
2. ПСТР(G3;ПОИСК("y";G3)-3;2) - в G3 ищем букву y, отходим от нее плево на 3 и оттуда берем 2 символа, получаем макс год в условии
3. вычитаем п.2. из п.1. и сравниваем с нулем. Если меньше, то ИСТИНА, иначе - ЛОЖЬ
4. 1/п.3 даст ошибку там, где ложь и 1 где ИСТИНА
5. СЧЁТ считает единицы, игнорируя ошибки. Получаем кол-во ячеек с годами, меньшими максимального в условии
6. п.5 - СУММ( - вычитаем то, что мы уже насчитали раньше в предыдущих столбцах
7. Все бы хорошо, 5 лет 0 мес относится не к "5-10", а к "3-5", поэтому нужно добавить 5 лет 0 мес, не трогая при этом 5 лет 1,2,... мес
7.1. --ПСТР(G3;ПОИСК("y";G3)-3;2)&" г. 0 мес." - к п.2 прилепляем справа кусок " г. 0 мес.", получаем как раз такие, какие нужно найти в п.7
7.2. с помощью СЧЁТЕСЛИ считаем их количество и прибавляем к п.5
К сообщению приложен файл: _2-14-2.xlsx(24.3 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Мне наверное надо было уточнить, что с интерваласи 3-5, 5-10, например, значение 5 г. 0 мес. должно включаться в первый интервал, а во втором интервале уже строгое неравенство от 5 г. 0 мес. без включения
Это и так видно по той формуле, что у Вас в файле была

По поводу 1-7-... да, все правильно, это я не закрепил столбец в формуле. Вот так нужно
Код
=СЧЁТ(1/(ЛЕВБ($A1:$A99;2)-ПСТР(G3;ПОИСК("y";G3)-3;2)<0))-СУММ($F4:F4)+СЧЁТЕСЛИ($A:$A;--ПСТР(G3;ПОИСК("y";G3)-3;2)&" г. 0 мес.")


Пояснялка
1. ЛЕВБ($A1:$A99;2) = 2 символа слева в столбце А, получаем год
2. ПСТР(G3;ПОИСК("y";G3)-3;2) - в G3 ищем букву y, отходим от нее плево на 3 и оттуда берем 2 символа, получаем макс год в условии
3. вычитаем п.2. из п.1. и сравниваем с нулем. Если меньше, то ИСТИНА, иначе - ЛОЖЬ
4. 1/п.3 даст ошибку там, где ложь и 1 где ИСТИНА
5. СЧЁТ считает единицы, игнорируя ошибки. Получаем кол-во ячеек с годами, меньшими максимального в условии
6. п.5 - СУММ( - вычитаем то, что мы уже насчитали раньше в предыдущих столбцах
7. Все бы хорошо, 5 лет 0 мес относится не к "5-10", а к "3-5", поэтому нужно добавить 5 лет 0 мес, не трогая при этом 5 лет 1,2,... мес
7.1. --ПСТР(G3;ПОИСК("y";G3)-3;2)&" г. 0 мес." - к п.2 прилепляем справа кусок " г. 0 мес.", получаем как раз такие, какие нужно найти в п.7
7.2. с помощью СЧЁТЕСЛИ считаем их количество и прибавляем к п.5

Автор - _Boroda_
Дата добавления - 07.02.2019 в 13:19
sboy Дата: Четверг, 07.02.2019, 13:37 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 722 ±
Замечаний: 0% ±

Excel 2010
без создания дополнительного нового столбца вряд ли получится как-то объединить формулы?

конечно можно, доп.столбец это для объяснения
вот так будет для первого (формула массива)
Код
=СЧЁТ(1/(ПСТР($A$1:$A$55;1;ПОИСК("г";$A$1:$A$55)-2)*365+СЖПРОБЕЛЫ(ПСТР($A$1:$A$55;ПОИСК("г";$A$1:$A$55)+3;2))*30,4<366))


Яндекс: 410016850021169
 
Ответить
Сообщение
без создания дополнительного нового столбца вряд ли получится как-то объединить формулы?

конечно можно, доп.столбец это для объяснения
вот так будет для первого (формула массива)
Код
=СЧЁТ(1/(ПСТР($A$1:$A$55;1;ПОИСК("г";$A$1:$A$55)-2)*365+СЖПРОБЕЛЫ(ПСТР($A$1:$A$55;ПОИСК("г";$A$1:$A$55)+3;2))*30,4<366))

Автор - sboy
Дата добавления - 07.02.2019 в 13:37
Ikusya Дата: Четверг, 07.02.2019, 14:24 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, спасибо! Применила уже на готовом варианте, все просто замечательно
Но позвольте еще немного Вас помучать :angel:
Аналогичным образом возможно подсчитать сначала определенный диапазон уже в месяцах (0-6), а потом уже переход к годам? Здесь уже получается посложнее "вычленить" месяц, ибо если отсчитывать символы справа налево, то там уже мешаются буквенные обозначения и чтобы они не учитывались, нужно отдельное ограничение какое-то (если правильно понимаю)

Вы уж простите мою дотошность, просто очень хочется не тупо копировать формулы, а постараться вникнуть в сам процесс расчета и научиться выстраивать логические связи (если не сразу, то хотя бы плавно со временем) :shy:
К сообщению приложен файл: _2-14-2-1-.xlsx(25.4 Kb)
 
Ответить
Сообщение_Boroda_, спасибо! Применила уже на готовом варианте, все просто замечательно
Но позвольте еще немного Вас помучать :angel:
Аналогичным образом возможно подсчитать сначала определенный диапазон уже в месяцах (0-6), а потом уже переход к годам? Здесь уже получается посложнее "вычленить" месяц, ибо если отсчитывать символы справа налево, то там уже мешаются буквенные обозначения и чтобы они не учитывались, нужно отдельное ограничение какое-то (если правильно понимаю)

Вы уж простите мою дотошность, просто очень хочется не тупо копировать формулы, а постараться вникнуть в сам процесс расчета и научиться выстраивать логические связи (если не сразу, то хотя бы плавно со временем) :shy:

Автор - Ikusya
Дата добавления - 07.02.2019 в 14:24
Pelena Дата: Четверг, 07.02.2019, 15:51 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 14233
Репутация: 3110 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Конечно, не Борода, но, может, и я на что сгожусь...
Как вариант
Код
=СУММПРОИЗВ((СЖПРОБЕЛЫ(ЛЕВСИМВ($A$1:$A$99;2))="0")*(--(0&ЛЕВСИМВ(ПРАВСИМВ($A$1:$A$99;6);2))<6))
К сообщению приложен файл: 4894899.xlsx(28.9 Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеКонечно, не Борода, но, может, и я на что сгожусь...
Как вариант
Код
=СУММПРОИЗВ((СЖПРОБЕЛЫ(ЛЕВСИМВ($A$1:$A$99;2))="0")*(--(0&ЛЕВСИМВ(ПРАВСИМВ($A$1:$A$99;6);2))<6))

Автор - Pelena
Дата добавления - 07.02.2019 в 15:51
Ikusya Дата: Пятница, 08.02.2019, 09:57 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Pelena, попробовала Ваш вариант, он работает, но когда в оригинальном файое появляются значения с 10 мес. они также как и у меня изначально дублируются и в другой интервал не подходящий :(
 
Ответить
СообщениеPelena, попробовала Ваш вариант, он работает, но когда в оригинальном файое появляются значения с 10 мес. они также как и у меня изначально дублируются и в другой интервал не подходящий :(

Автор - Ikusya
Дата добавления - 08.02.2019 в 09:57
Pelena Дата: Пятница, 08.02.2019, 10:35 | Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 14233
Репутация: 3110 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Попробуйте так
Код
=СУММПРОИЗВ((СЖПРОБЕЛЫ(ЛЕВСИМВ($A$1:$A$99;2))="0")*(--(0&СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПРАВСИМВ($A$1:$A$99;7);"мес.";)))<6))


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеПопробуйте так
Код
=СУММПРОИЗВ((СЖПРОБЕЛЫ(ЛЕВСИМВ($A$1:$A$99;2))="0")*(--(0&СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПРАВСИМВ($A$1:$A$99;7);"мес.";)))<6))

Автор - Pelena
Дата добавления - 08.02.2019 в 10:35
Ikusya Дата: Пятница, 08.02.2019, 11:18 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
sboy, _Boroda_, и еще раз большое спасибо за помощь и подробные объяснения! Уже в рабочей таблице немного подправила "под себя" и объединила оба предложенных Вами варианта. Все замечательно работает и считает, то, что нужно. Спасибо!
 
Ответить
Сообщениеsboy, _Boroda_, и еще раз большое спасибо за помощь и подробные объяснения! Уже в рабочей таблице немного подправила "под себя" и объединила оба предложенных Вами варианта. Все замечательно работает и считает, то, что нужно. Спасибо!

Автор - Ikusya
Дата добавления - 08.02.2019 в 11:18
Ikusya Дата: Пятница, 08.02.2019, 11:28 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Pelena, второй вариант отлично подошел. Большое спасибо! ^_^ Теперь аж несколько возможных вариантов решения проблемы, и это замечательно:)
Чем больше пробуется, тем больше осознанности в действиях каждый раз появляется)
 
Ответить
СообщениеPelena, второй вариант отлично подошел. Большое спасибо! ^_^ Теперь аж несколько возможных вариантов решения проблемы, и это замечательно:)
Чем больше пробуется, тем больше осознанности в действиях каждый раз появляется)

Автор - Ikusya
Дата добавления - 08.02.2019 в 11:28
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет количества значений с учетом временного промежутка (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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