Выборка под фильтром по годам
LikeARS
Дата: Четверг, 21.10.2021, 10:41 |
Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация:
0
±
Замечаний:
0% ±
Добрый день всем! В принципе в файле изложил с какой формулой прошу помочь, вообщем необходимо, чтобы по годам получались средние значения по выборке под фильтром.
Добрый день всем! В принципе в файле изложил с какой формулой прошу помочь, вообщем необходимо, чтобы по годам получались средние значения по выборке под фильтром. LikeARS
Ответить
Сообщение Добрый день всем! В принципе в файле изложил с какой формулой прошу помочь, вообщем необходимо, чтобы по годам получались средние значения по выборке под фильтром. Автор - LikeARS Дата добавления - 21.10.2021 в 10:41
Serge_007
Дата: Четверг, 21.10.2021, 10:52 |
Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация:
2749
±
Замечаний:
±
Excel 2016
Здравствуйте Нач. запасы по годам средние 2020 - 464,1428571 Как получилась эта цифра?
Здравствуйте Нач. запасы по годам средние 2020 - 464,1428571 Как получилась эта цифра? Serge_007
ЮMoney :41001419691823 | WMR :126292472390
Ответить
Сообщение Здравствуйте Нач. запасы по годам средние 2020 - 464,1428571 Как получилась эта цифра? Автор - Serge_007 Дата добавления - 21.10.2021 в 10:52
LikeARS
Дата: Четверг, 21.10.2021, 10:56 |
Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация:
0
±
Замечаний:
0% ±
Serge_007, это с листа "Начальный" среднее значение из столбца 4 по 2020 году
Serge_007, это с листа "Начальный" среднее значение из столбца 4 по 2020 году LikeARS
Ответить
Сообщение Serge_007, это с листа "Начальный" среднее значение из столбца 4 по 2020 году Автор - LikeARS Дата добавления - 21.10.2021 в 10:56
Serge_007
Дата: Четверг, 21.10.2021, 11:34 |
Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация:
2749
±
Замечаний:
±
Excel 2016
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(Начальный!D$1;СТРОКА(2:19)-1;)))/СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ(Начальный!D$1;СТРОКА(2:19)-1;)))
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(Начальный!D$1;СТРОКА(2:19)-1;)))/СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ(Начальный!D$1;СТРОКА(2:19)-1;)))
Serge_007
ЮMoney :41001419691823 | WMR :126292472390
Ответить
Сообщение Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(Начальный!D$1;СТРОКА(2:19)-1;)))/СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ(Начальный!D$1;СТРОКА(2:19)-1;)))
Автор - Serge_007 Дата добавления - 21.10.2021 в 11:34
LikeARS
Дата: Четверг, 21.10.2021, 11:43 |
Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация:
0
±
Замечаний:
0% ±
Serge_007, спасибо немного не так работает, фильтр ставится на листе по-годам, а сейчвс считается просто среднее по всем годам
Serge_007, спасибо немного не так работает, фильтр ставится на листе по-годам, а сейчвс считается просто среднее по всем годам LikeARS
Ответить
Сообщение Serge_007, спасибо немного не так работает, фильтр ставится на листе по-годам, а сейчвс считается просто среднее по всем годам Автор - LikeARS Дата добавления - 21.10.2021 в 11:43
Serge_007
Дата: Четверг, 21.10.2021, 11:46 |
Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация:
2749
±
Замечаний:
±
Excel 2016
фильтр ставится на листе по-годам
Так что откуда брать-то? В моём файле (сообщением выше) результат формулами на 100% такой же как у Вас проставлен вручную Не так надо?
фильтр ставится на листе по-годам
Так что откуда брать-то? В моём файле (сообщением выше) результат формулами на 100% такой же как у Вас проставлен вручную Не так надо?Serge_007
ЮMoney :41001419691823 | WMR :126292472390
Ответить
Сообщение фильтр ставится на листе по-годам
Так что откуда брать-то? В моём файле (сообщением выше) результат формулами на 100% такой же как у Вас проставлен вручную Не так надо?Автор - Serge_007 Дата добавления - 21.10.2021 в 11:46
LikeARS
Дата: Четверг, 21.10.2021, 11:50 |
Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация:
0
±
Замечаний:
0% ±
Serge_007, фильтр будет ставиться на листе по_годам, лист начальный должен быть без фильтров
Serge_007, фильтр будет ставиться на листе по_годам, лист начальный должен быть без фильтров LikeARS
Ответить
Сообщение Serge_007, фильтр будет ставиться на листе по_годам, лист начальный должен быть без фильтров Автор - LikeARS Дата добавления - 21.10.2021 в 11:50
Serge_007
Дата: Четверг, 21.10.2021, 11:53 |
Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация:
2749
±
Замечаний:
±
Excel 2016
фильтр будет ставиться на листе по_годам
А данные, которые надо посчитать - на листе Начальный лист начальный должен быть без фильтров
Как Вы себе представляете среднее значение отфильтрованных данных без применения фильтра?
фильтр будет ставиться на листе по_годам
А данные, которые надо посчитать - на листе Начальный лист начальный должен быть без фильтров
Как Вы себе представляете среднее значение отфильтрованных данных без применения фильтра?Serge_007
ЮMoney :41001419691823 | WMR :126292472390
Ответить
Сообщение фильтр будет ставиться на листе по_годам
А данные, которые надо посчитать - на листе Начальный лист начальный должен быть без фильтров
Как Вы себе представляете среднее значение отфильтрованных данных без применения фильтра?Автор - Serge_007 Дата добавления - 21.10.2021 в 11:53
LikeARS
Дата: Четверг, 21.10.2021, 11:57 |
Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация:
0
±
Замечаний:
0% ±
Serge_007, в столбце B уникальные значения по ним будет однозначное соответствие с листом Начальный
Serge_007, в столбце B уникальные значения по ним будет однозначное соответствие с листом Начальный LikeARS
Ответить
Сообщение Serge_007, в столбце B уникальные значения по ним будет однозначное соответствие с листом Начальный Автор - LikeARS Дата добавления - 21.10.2021 в 11:57
Serge_007
Дата: Четверг, 21.10.2021, 12:08 |
Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация:
2749
±
Замечаний:
±
Excel 2016
Формулы считают те данные, которые отфильтрованы Если на листе нет фильтра - будут учитываться все
Формулы считают те данные, которые отфильтрованы Если на листе нет фильтра - будут учитываться все Serge_007
ЮMoney :41001419691823 | WMR :126292472390
Ответить
Сообщение Формулы считают те данные, которые отфильтрованы Если на листе нет фильтра - будут учитываться все Автор - Serge_007 Дата добавления - 21.10.2021 в 12:08
NikitaDvorets
Дата: Четверг, 21.10.2021, 16:53 |
Сообщение № 11
Группа: Авторы
Ранг: Ветеран
Сообщений: 557
Репутация:
126
±
Замечаний:
0% ±
Excel 2019
LikeARS , добрый день. Вариант - без фильтра в виде выпадающего списка D16 трудно представить, как решить данную задачу (прилагается).
LikeARS , добрый день. Вариант - без фильтра в виде выпадающего списка D16 трудно представить, как решить данную задачу (прилагается).NikitaDvorets
Ответить
Сообщение LikeARS , добрый день. Вариант - без фильтра в виде выпадающего списка D16 трудно представить, как решить данную задачу (прилагается).Автор - NikitaDvorets Дата добавления - 21.10.2021 в 16:53
LikeARS
Дата: Пятница, 22.10.2021, 04:09 |
Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация:
0
±
Замечаний:
0% ±
NikitaDvorets, спасибо за неравнодушие! Но я хотел ещё более универсально, допустим выбрать показатель не только 5, а допустим 5 и 6, думаю уже над UDF, задача сильно упростилась, если бы порядок на листе начальный был такой же как и на листе по_годам.
NikitaDvorets, спасибо за неравнодушие! Но я хотел ещё более универсально, допустим выбрать показатель не только 5, а допустим 5 и 6, думаю уже над UDF, задача сильно упростилась, если бы порядок на листе начальный был такой же как и на листе по_годам. LikeARS
Ответить
Сообщение NikitaDvorets, спасибо за неравнодушие! Но я хотел ещё более универсально, допустим выбрать показатель не только 5, а допустим 5 и 6, думаю уже над UDF, задача сильно упростилась, если бы порядок на листе начальный был такой же как и на листе по_годам. Автор - LikeARS Дата добавления - 22.10.2021 в 04:09
NikitaDvorets
Дата: Пятница, 22.10.2021, 10:08 |
Сообщение № 13
Группа: Авторы
Ранг: Ветеран
Сообщений: 557
Репутация:
126
±
Замечаний:
0% ±
Excel 2019
LikeARS , добрый день. Возможно, в данном случае потребуется новая формулировка задачи на платной ветке форума.
LikeARS , добрый день. Возможно, в данном случае потребуется новая формулировка задачи на платной ветке форума.NikitaDvorets
Ответить
Сообщение LikeARS , добрый день. Возможно, в данном случае потребуется новая формулировка задачи на платной ветке форума.Автор - NikitaDvorets Дата добавления - 22.10.2021 в 10:08
Egyptian
Дата: Пятница, 22.10.2021, 15:34 |
Сообщение № 14
Группа: Проверенные
Ранг: Ветеран
Сообщений: 514
Репутация:
185
±
Замечаний:
0% ±
Excel 2013/2016
Посмотрите, вдруг такой вариант устроит - перечисление показателей через запятую в ячейке D16. Число, запятая, пробел число, запятая и т.д.Код
=IFERROR(SUM(SUMIFS(Начальный!$D$2:$D$19;Начальный!$C$2:$C$19;E14;Начальный!$A$2:$A$19;IFERROR(--TRIM(MID(SUBSTITUTE($D$16;", ";REPT(" ";99));ROW($1:$10)*99-98;99));"")))/SUM(COUNTIFS(Начальный!$C$2:$C$19;E14;Начальный!$A$2:$A$19;IFERROR(--TRIM(MID(SUBSTITUTE($D$16;", ";REPT(" ";99));ROW($1:$10)*99-98;99));"")));"-")
Посмотрите, вдруг такой вариант устроит - перечисление показателей через запятую в ячейке D16. Число, запятая, пробел число, запятая и т.д.Код
=IFERROR(SUM(SUMIFS(Начальный!$D$2:$D$19;Начальный!$C$2:$C$19;E14;Начальный!$A$2:$A$19;IFERROR(--TRIM(MID(SUBSTITUTE($D$16;", ";REPT(" ";99));ROW($1:$10)*99-98;99));"")))/SUM(COUNTIFS(Начальный!$C$2:$C$19;E14;Начальный!$A$2:$A$19;IFERROR(--TRIM(MID(SUBSTITUTE($D$16;", ";REPT(" ";99));ROW($1:$10)*99-98;99));"")));"-")
Egyptian
Ответить
Сообщение Посмотрите, вдруг такой вариант устроит - перечисление показателей через запятую в ячейке D16. Число, запятая, пробел число, запятая и т.д.Код
=IFERROR(SUM(SUMIFS(Начальный!$D$2:$D$19;Начальный!$C$2:$C$19;E14;Начальный!$A$2:$A$19;IFERROR(--TRIM(MID(SUBSTITUTE($D$16;", ";REPT(" ";99));ROW($1:$10)*99-98;99));"")))/SUM(COUNTIFS(Начальный!$C$2:$C$19;E14;Начальный!$A$2:$A$19;IFERROR(--TRIM(MID(SUBSTITUTE($D$16;", ";REPT(" ";99));ROW($1:$10)*99-98;99));"")));"-")
Автор - Egyptian Дата добавления - 22.10.2021 в 15:34
NikitaDvorets
Дата: Пятница, 22.10.2021, 16:16 |
Сообщение № 15
Группа: Авторы
Ранг: Ветеран
Сообщений: 557
Репутация:
126
±
Замечаний:
0% ±
Excel 2019
Egyptian , - замечательно! Цитата
=ЕСЛИОШИБКА(СУММ(СУММЕСЛИМН(Начальный!$D$2:$D$19;Начальный!$C$2:$C$19;E14;Начальный!$A$2:$A$19;ЕСЛИОШИБКА(--СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($D$16;", ";ПОВТОР(" ";99));СТРОКА($1:$10)*99-98;99));"")))/СУММ(СЧЁТЕСЛИМН(Начальный!$C$2:$C$19;E14;Начальный!$A$2:$A$19;ЕСЛИОШИБКА(--СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($D$16;", ";ПОВТОР(" ";99));СТРОКА($1:$10)*99-98;99));"")));"-")
Знания не бывают лишними! Но может было бы удобнее делать множественный выбор из выпадающего списка D16 ? (недостижимый вариант?)
Egyptian , - замечательно! Цитата
=ЕСЛИОШИБКА(СУММ(СУММЕСЛИМН(Начальный!$D$2:$D$19;Начальный!$C$2:$C$19;E14;Начальный!$A$2:$A$19;ЕСЛИОШИБКА(--СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($D$16;", ";ПОВТОР(" ";99));СТРОКА($1:$10)*99-98;99));"")))/СУММ(СЧЁТЕСЛИМН(Начальный!$C$2:$C$19;E14;Начальный!$A$2:$A$19;ЕСЛИОШИБКА(--СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($D$16;", ";ПОВТОР(" ";99));СТРОКА($1:$10)*99-98;99));"")));"-")
Знания не бывают лишними! Но может было бы удобнее делать множественный выбор из выпадающего списка D16 ? (недостижимый вариант?)NikitaDvorets
Ответить
Сообщение Egyptian , - замечательно! Цитата
=ЕСЛИОШИБКА(СУММ(СУММЕСЛИМН(Начальный!$D$2:$D$19;Начальный!$C$2:$C$19;E14;Начальный!$A$2:$A$19;ЕСЛИОШИБКА(--СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($D$16;", ";ПОВТОР(" ";99));СТРОКА($1:$10)*99-98;99));"")))/СУММ(СЧЁТЕСЛИМН(Начальный!$C$2:$C$19;E14;Начальный!$A$2:$A$19;ЕСЛИОШИБКА(--СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($D$16;", ";ПОВТОР(" ";99));СТРОКА($1:$10)*99-98;99));"")));"-")
Знания не бывают лишними! Но может было бы удобнее делать множественный выбор из выпадающего списка D16 ? (недостижимый вариант?)Автор - NikitaDvorets Дата добавления - 22.10.2021 в 16:16
Egyptian
Дата: Пятница, 22.10.2021, 17:16 |
Сообщение № 16
Группа: Проверенные
Ранг: Ветеран
Сообщений: 514
Репутация:
185
±
Замечаний:
0% ±
Excel 2013/2016
NikitaDvorets ,Цитата
Но может было бы удобнее делать множественный выбор из выпадающего списка D16
Насколько я в курсе, это можно сделать только с помощью VBA. К примеру, один из сайтов , где описано, как это сделать.Update. Сделал множественный вып. список по описанию.
NikitaDvorets ,Цитата
Но может было бы удобнее делать множественный выбор из выпадающего списка D16
Насколько я в курсе, это можно сделать только с помощью VBA. К примеру, один из сайтов , где описано, как это сделать.Update. Сделал множественный вып. список по описанию.Egyptian
Сообщение отредактировал Egyptian - Суббота, 23.10.2021, 09:58
Ответить
Сообщение NikitaDvorets ,Цитата
Но может было бы удобнее делать множественный выбор из выпадающего списка D16
Насколько я в курсе, это можно сделать только с помощью VBA. К примеру, один из сайтов , где описано, как это сделать.Update. Сделал множественный вып. список по описанию.Автор - Egyptian Дата добавления - 22.10.2021 в 17:16
LikeARS
Дата: Вторник, 26.10.2021, 12:39 |
Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация:
0
±
Замечаний:
0% ±
Egyptian, спасибо за участие. Но я хотел вообще, чтобы был произвольный фильтр на листе по годам. Вообщем, спасибо я уже UDF сваял. Просто хотелось формулой все сделать. Пример файла и код UDF ниже. [vba]Код
Option Base 1 Function Middle_Year(FR As Variant, Year As Variant, R1 As Range, R2 As Range, R3 As Range) As Variant 'FR - массив под фильтром 'Year - необходимый год Dim myDictionary As Object Dim arr() Dim cnt, sum As Long cnt = 0 sum = 0 'Предпологаем что R1 R2 и R3 будут указаны одинаковыми по размерности ReDim arr(1 To 3, 1 To R3.Rows.Count) For j = 1 To R3.Rows.Count arr(1, j) = R3(j) Next For i = 1 To R2.Rows.Count arr(2, i) = R2(i) Next For u = 1 To R1.Rows.Count arr(3, u) = R1(u) Next For Each myCell In FR If myCell <> 0 Then For i = 1 To R1.Rows.Count If arr(2, i) = Year And arr(3, i) = myCell Then sum = sum + arr(1, i) cnt = cnt + 1 End If Next End If Next If cnt <> 0 Then Middle_Year = sum / cnt Else Middle_Year = 0 End If End Function
[/vba] Формула массива! в ячейках по годам: Код
=Middle_Year((ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(по_годам!$B$1;СТРОКА(по_годам!$B$2:$B$19)-1;)));C$1;Начальный!$B2:$B19;Начальный!$C2:$C19;Начальный!$D2:$D19)
Egyptian, спасибо за участие. Но я хотел вообще, чтобы был произвольный фильтр на листе по годам. Вообщем, спасибо я уже UDF сваял. Просто хотелось формулой все сделать. Пример файла и код UDF ниже. [vba]Код
Option Base 1 Function Middle_Year(FR As Variant, Year As Variant, R1 As Range, R2 As Range, R3 As Range) As Variant 'FR - массив под фильтром 'Year - необходимый год Dim myDictionary As Object Dim arr() Dim cnt, sum As Long cnt = 0 sum = 0 'Предпологаем что R1 R2 и R3 будут указаны одинаковыми по размерности ReDim arr(1 To 3, 1 To R3.Rows.Count) For j = 1 To R3.Rows.Count arr(1, j) = R3(j) Next For i = 1 To R2.Rows.Count arr(2, i) = R2(i) Next For u = 1 To R1.Rows.Count arr(3, u) = R1(u) Next For Each myCell In FR If myCell <> 0 Then For i = 1 To R1.Rows.Count If arr(2, i) = Year And arr(3, i) = myCell Then sum = sum + arr(1, i) cnt = cnt + 1 End If Next End If Next If cnt <> 0 Then Middle_Year = sum / cnt Else Middle_Year = 0 End If End Function
[/vba] Формула массива! в ячейках по годам: Код
=Middle_Year((ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(по_годам!$B$1;СТРОКА(по_годам!$B$2:$B$19)-1;)));C$1;Начальный!$B2:$B19;Начальный!$C2:$C19;Начальный!$D2:$D19)
LikeARS
Ответить
Сообщение Egyptian, спасибо за участие. Но я хотел вообще, чтобы был произвольный фильтр на листе по годам. Вообщем, спасибо я уже UDF сваял. Просто хотелось формулой все сделать. Пример файла и код UDF ниже. [vba]Код
Option Base 1 Function Middle_Year(FR As Variant, Year As Variant, R1 As Range, R2 As Range, R3 As Range) As Variant 'FR - массив под фильтром 'Year - необходимый год Dim myDictionary As Object Dim arr() Dim cnt, sum As Long cnt = 0 sum = 0 'Предпологаем что R1 R2 и R3 будут указаны одинаковыми по размерности ReDim arr(1 To 3, 1 To R3.Rows.Count) For j = 1 To R3.Rows.Count arr(1, j) = R3(j) Next For i = 1 To R2.Rows.Count arr(2, i) = R2(i) Next For u = 1 To R1.Rows.Count arr(3, u) = R1(u) Next For Each myCell In FR If myCell <> 0 Then For i = 1 To R1.Rows.Count If arr(2, i) = Year And arr(3, i) = myCell Then sum = sum + arr(1, i) cnt = cnt + 1 End If Next End If Next If cnt <> 0 Then Middle_Year = sum / cnt Else Middle_Year = 0 End If End Function
[/vba] Формула массива! в ячейках по годам: Код
=Middle_Year((ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(по_годам!$B$1;СТРОКА(по_годам!$B$2:$B$19)-1;)));C$1;Начальный!$B2:$B19;Начальный!$C2:$C19;Начальный!$D2:$D19)
Автор - LikeARS Дата добавления - 26.10.2021 в 12:39