Уважаемые форумчане, подскажите, пожалуйста, как можно решить следующую проблему. В файле-примере есть ряд временных промежутков в формате ... г. ... мес. Мне нужно расчитать количество этих значений в зависимости от промежутка (сколько, напрмер, значений входит в интервал от года до трех, 3-5, 5-10 и тд.).
И, к сожалению, в моих формулах оказалось много погрешностей в расчетах, например: при вводе значения "3 г. 7 мес." значение считается и в нужном интервале от 3-5 лет, и дублируется в интервале больше 10 лет, а все значения больше 10 лет попадают под интервал с единицей (есть подозрение, что я допустила где-то ошибку с форматами, и Excel вместо 10 видит 1, или 0 г. 10 мес. тоже считывает как 0,1 и поэтому распределяет в другой интервал)
Для удобства в первом столбце файла значения выделены цветом, чтобы было ясно, сколько значений относится к тому или иному интервалу, по второму столбцу делались расчеты (но не все, а несколько основных ошибочных примеров)
Буду очень благодарна, если получится объяснить, что не так сделала
Добрй день!
Уважаемые форумчане, подскажите, пожалуйста, как можно решить следующую проблему. В файле-примере есть ряд временных промежутков в формате ... г. ... мес. Мне нужно расчитать количество этих значений в зависимости от промежутка (сколько, напрмер, значений входит в интервал от года до трех, 3-5, 5-10 и тд.).
И, к сожалению, в моих формулах оказалось много погрешностей в расчетах, например: при вводе значения "3 г. 7 мес." значение считается и в нужном интервале от 3-5 лет, и дублируется в интервале больше 10 лет, а все значения больше 10 лет попадают под интервал с единицей (есть подозрение, что я допустила где-то ошибку с форматами, и Excel вместо 10 видит 1, или 0 г. 10 мес. тоже считывает как 0,1 и поэтому распределяет в другой интервал)
Для удобства в первом столбце файла значения выделены цветом, чтобы было ясно, сколько значений относится к тому или иному интервалу, по второму столбцу делались расчеты (но не все, а несколько основных ошибочных примеров)
Буду очень благодарна, если получится объяснить, что не так сделала Ikusya
sboy, огромное спасибо, формула работает отлично и синтаксис более чем понятен!
Мне просто сначала показалось, что за счет присвоения формата даты арифметически получится действия произвести, но теперь понимаю, что была не права) А решение оказалось намного ближе и проще, чем думалось)
А подскажите, пожалуйста, без создания дополнительного нового столбца вряд ли получится как-то объединить формулы? Это уже просто из любопытства для визуализации, чтобы оригинальный файл с намного бОльшим количеством данных не нагружать лишними столбцами
sboy, огромное спасибо, формула работает отлично и синтаксис более чем понятен!
Мне просто сначала показалось, что за счет присвоения формата даты арифметически получится действия произвести, но теперь понимаю, что была не права) А решение оказалось намного ближе и проще, чем думалось)
А подскажите, пожалуйста, без создания дополнительного нового столбца вряд ли получится как-то объединить формулы? Это уже просто из любопытства для визуализации, чтобы оригинальный файл с намного бОльшим количеством данных не нагружать лишними столбцамиIkusya
Единственное, для меня как не для совсем продвинутого пользователя сложноват к пониманию синтаксис. По отдельности со всеми функциями знакома и принцип вроде понимаю, но с большим количеством деталей уже сложнее. И в конечной таблице есть небольшие расхождения, т.е. вместо 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
Мне наверное надо было уточнить, что с интерваласи 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
Мне наверное надо было уточнить, что с интерваласи 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_
_Boroda_, спасибо! Применила уже на готовом варианте, все просто замечательно Но позвольте еще немного Вас помучать :angel: Аналогичным образом возможно подсчитать сначала определенный диапазон уже в месяцах (0-6), а потом уже переход к годам? Здесь уже получается посложнее "вычленить" месяц, ибо если отсчитывать символы справа налево, то там уже мешаются буквенные обозначения и чтобы они не учитывались, нужно отдельное ограничение какое-то (если правильно понимаю)
Вы уж простите мою дотошность, просто очень хочется не тупо копировать формулы, а постараться вникнуть в сам процесс расчета и научиться выстраивать логические связи (если не сразу, то хотя бы плавно со временем) :shy:
_Boroda_, спасибо! Применила уже на готовом варианте, все просто замечательно Но позвольте еще немного Вас помучать :angel: Аналогичным образом возможно подсчитать сначала определенный диапазон уже в месяцах (0-6), а потом уже переход к годам? Здесь уже получается посложнее "вычленить" месяц, ибо если отсчитывать символы справа налево, то там уже мешаются буквенные обозначения и чтобы они не учитывались, нужно отдельное ограничение какое-то (если правильно понимаю)
Вы уж простите мою дотошность, просто очень хочется не тупо копировать формулы, а постараться вникнуть в сам процесс расчета и научиться выстраивать логические связи (если не сразу, то хотя бы плавно со временем) :shy:Ikusya
Pelena, попробовала Ваш вариант, он работает, но когда в оригинальном файое появляются значения с 10 мес. они также как и у меня изначально дублируются и в другой интервал не подходящий
Pelena, попробовала Ваш вариант, он работает, но когда в оригинальном файое появляются значения с 10 мес. они также как и у меня изначально дублируются и в другой интервал не подходящий Ikusya
sboy, _Boroda_, и еще раз большое спасибо за помощь и подробные объяснения! Уже в рабочей таблице немного подправила "под себя" и объединила оба предложенных Вами варианта. Все замечательно работает и считает, то, что нужно. Спасибо!
sboy, _Boroda_, и еще раз большое спасибо за помощь и подробные объяснения! Уже в рабочей таблице немного подправила "под себя" и объединила оба предложенных Вами варианта. Все замечательно работает и считает, то, что нужно. Спасибо!Ikusya
Pelena, второй вариант отлично подошел. Большое спасибо! Теперь аж несколько возможных вариантов решения проблемы, и это замечательно:) Чем больше пробуется, тем больше осознанности в действиях каждый раз появляется)
Pelena, второй вариант отлично подошел. Большое спасибо! Теперь аж несколько возможных вариантов решения проблемы, и это замечательно:) Чем больше пробуется, тем больше осознанности в действиях каждый раз появляется)Ikusya
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
