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

Вход

Регистрация

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

 

= Мир MS Excel/Выборка под фильтром по годам - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка под фильтром по годам (Формулы/Formulas)
Выборка под фильтром по годам
LikeARS Дата: Четверг, 21.10.2021, 10:41 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Добрый день всем! В принципе в файле изложил с какой формулой прошу помочь, вообщем необходимо, чтобы по годам получались средние значения по выборке под фильтром.
К сообщению приложен файл: 8299382.xlsx(14.4 Kb)
 
Ответить
СообщениеДобрый день всем! В принципе в файле изложил с какой формулой прошу помочь, вообщем необходимо, чтобы по годам получались средние значения по выборке под фильтром.

Автор - LikeARS
Дата добавления - 21.10.2021 в 10:41
Serge_007 Дата: Четверг, 21.10.2021, 10:52 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 14697
Репутация: 2432 ±
Замечаний: ±

Excel 2010
Здравствуйте

Нач. запасы по годам средние 2020 - 464,1428571
Как получилась эта цифра?


Яндекс-деньги: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
Дата добавления - 21.10.2021 в 10:56
Serge_007 Дата: Четверг, 21.10.2021, 11:34 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 14697
Репутация: 2432 ±
Замечаний: ±

Excel 2010
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(Начальный!D$1;СТРОКА(2:19)-1;)))/СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ(Начальный!D$1;СТРОКА(2:19)-1;)))
К сообщению приложен файл: 20211021_LikeAR.xls(77.0 Kb)


Яндекс-деньги: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
Дата добавления - 21.10.2021 в 11:43
Serge_007 Дата: Четверг, 21.10.2021, 11:46 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 14697
Репутация: 2432 ±
Замечаний: ±

Excel 2010
фильтр ставится на листе по-годам
это с листа "Начальный"
Так что откуда брать-то?

В моём файле (сообщением выше) результат формулами на 100% такой же как у Вас проставлен вручную
Не так надо?


Яндекс-деньги: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
Дата добавления - 21.10.2021 в 11:50
Serge_007 Дата: Четверг, 21.10.2021, 11:53 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 14697
Репутация: 2432 ±
Замечаний: ±

Excel 2010
фильтр будет ставиться на листе по_годам
А данные, которые надо посчитать - на листе Начальный

лист начальный должен быть без фильтров
Как Вы себе представляете среднее значение отфильтрованных данных без применения фильтра?


Яндекс-деньги: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
Дата добавления - 21.10.2021 в 11:57
Serge_007 Дата: Четверг, 21.10.2021, 12:08 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 14697
Репутация: 2432 ±
Замечаний: ±

Excel 2010
Формулы считают те данные, которые отфильтрованы
Если на листе нет фильтра - будут учитываться все


Яндекс-деньги:41001419691823 | WMR:126292472390
 
Ответить
СообщениеФормулы считают те данные, которые отфильтрованы
Если на листе нет фильтра - будут учитываться все

Автор - Serge_007
Дата добавления - 21.10.2021 в 12:08
NikitaDvorets Дата: Четверг, 21.10.2021, 16:53 | Сообщение № 11
Группа: Проверенные
Ранг: Обитатель
Сообщений: 256
Репутация: 42 ±
Замечаний: 0% ±

Excel 2019
LikeARS, добрый день.
Вариант - без фильтра в виде выпадающего списка D16 трудно представить, как решить данную задачу (прилагается).
К сообщению приложен файл: ___21-10-2021.xlsx(19.3 Kb)
 
Ответить
Сообщение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
Дата добавления - 22.10.2021 в 04:09
NikitaDvorets Дата: Пятница, 22.10.2021, 10:08 | Сообщение № 13
Группа: Проверенные
Ранг: Обитатель
Сообщений: 256
Репутация: 42 ±
Замечаний: 0% ±

Excel 2019
LikeARS, добрый день.
Возможно, в данном случае потребуется новая формулировка задачи на платной ветке форума.
 
Ответить
СообщениеLikeARS, добрый день.
Возможно, в данном случае потребуется новая формулировка задачи на платной ветке форума.

Автор - NikitaDvorets
Дата добавления - 22.10.2021 в 10:08
Egyptian Дата: Пятница, 22.10.2021, 15:34 | Сообщение № 14
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 202
Репутация: 68 ±
Замечаний: 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));"")));"-")
К сообщению приложен файл: _21-10-2021.xlsx(19.2 Kb)
 
Ответить
СообщениеПосмотрите, вдруг такой вариант устроит - перечисление показателей через запятую в ячейке 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
Группа: Проверенные
Ранг: Обитатель
Сообщений: 256
Репутация: 42 ±
Замечаний: 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
Дата добавления - 22.10.2021 в 16:16
Egyptian Дата: Пятница, 22.10.2021, 17:16 | Сообщение № 16
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 202
Репутация: 68 ±
Замечаний: 0% ±

Excel 2013/2016
NikitaDvorets,
Цитата
Но может было бы удобнее делать множественный выбор из выпадающего списка D16

Насколько я в курсе, это можно сделать только с помощью VBA. К примеру, один из сайтов, где описано, как это сделать.
Update.
Сделал множественный вып. список по описанию.
К сообщению приложен файл: _21-10-2021.xlsb(24.0 Kb)


Сообщение отредактировал 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)
К сообщению приложен файл: 8299382.xlsm(23.4 Kb)
 
Ответить
Сообщение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
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка под фильтром по годам (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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