Просьба помочь придумать формулу для поиска среднего значени

Дата: Пятница, 31.07.2015, 08:39 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Добрый день!
Просьба помочь придумать формулу для поиска среднего значения.
Суть задачи:
Дано 24 значения, эти значения соответствуют одному критерию, но расположены они все на разных листах и в разных ячейках. Просьба найти среднее значение.
Самый простой способ, использовать формулу СРЗНАЧ(ВПР1;ВПР2;ВПР3... и тд). Такой вариант не подходит.
Заранее благодарен.
Пример прилагается.

К сообщению приложен файл: 3123932.xls (26.5 Kb)

Дата: Пятница, 31.07.2015, 09:13 | Сообщение № 2

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Например можно UDF:

[vba]

Код

Function SRED(find$, columnFind$, columnZnach$, Optional i% = 2, Optional iE%) As Double
Dim ii&, m, m1, n&, sum#, col&
If iE < i Then iE = Sheets.Count
For i = i To iE
        With Sheets(i)
         n = .Cells(Rows.Count, columnFind$).End(xlUp).Row
         m = .Range(.Cells(1, columnFind), .Cells(n, columnFind)).Value
         m1 = .Range(.Cells(1, columnZnach), .Cells(n, columnZnach)).Value
        End With
        For ii = 1 To UBound(m)
        If m(ii, 1) = find Then col = col + 1: sum = sum + m1(ii, 1)
        Next
Next
If col > 0 Then SRED = sum / col
End Function

[/vba]
Перевложил файл
Будет искать с листов от I до Ie листа до конца книги, в указанных столбцах ^_^

К сообщению приложен файл: 0013985-1-.xls (45.5 Kb)

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Пятница, 31.07.2015, 09:25

pabchek

Дата: Пятница, 31.07.2015, 09:56 | Сообщение № 3

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

Репутация: 218 ±

Замечаний: 0% ±

Excel 2007

Или формула массива (шифт+кантрл+ентер)

Код

=СУММ((E4:E8=K7)*F4:F8;('2'!E4:E8='1'!K7)*'2'!F4:F8;('3'!E4:E8='1'!K7)*'3'!F4:F8;('4'!E4:E8='1'!K7)*'4'!F4:F8)/4

К сообщению приложен файл: 3003141.xls (21.0 Kb)

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Дата: Пятница, 31.07.2015, 10:04 | Сообщение № 4

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Цитата pabchek, 31.07.2015 в 09:56, в сообщении № 3

Или формула массива (шифт+кантрл+ентер)

pabchek, А как дописывать эту формулу если будет листов 30... или 100

ТС написал же в файле:

Цитата

Перечисление через ";" не подходит, так как листов в очень много.

Иногда все проще чем кажется с первого взгляда.

Дата: Пятница, 31.07.2015, 10:06 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

НУ...
Спасибо! Прежде чем ответить, пришлось почитать что такое UDF. Таков мой уровень.
В моем варианте придется писать целый макрос, так как придется собирать значения с ряда файлов.
Уточню. Есть 31 файл - дни (очень тяжелые), в каждом 24 листа - часы, надо собрать среднемесячное значение в другом отдельном файле.
В VBA я не силен, вот и просил формулу))
В любом случае, спасибо! Мне надо подтягивать свои знания в VBA.

Дата: Пятница, 31.07.2015, 10:17 | Сообщение № 6

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Перечисление через ";" не подходит- это по сути перебор адресов.
Так можно найти все значения в 744 ячейках и потом найти среднее.
Файл легче не становится((

Дата: Пятница, 31.07.2015, 10:26 | Сообщение № 7

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Цитата Франц, 31.07.2015 в 10:17, в сообщении № 6

В моем варианте придется писать целый макрос

Там осталось-то дописать практически ничего - можете создать тему в ветке ВБА только приложите несколько файлов - примеров файлов данных и конечного файла.

А вообще по правильному было бы собрать данные всех листов со всех книг в одну таблицу- базу данных а там уже можно делать какие хотите отчеты ^_^

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Пятница, 31.07.2015, 10:29

Дата: Пятница, 31.07.2015, 10:41 | Сообщение № 8

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

К сожалению, от общей БД после попыток все собрать, отказались. Суточный файл весит 24 МБ, на листе 28000 значений, листов 24. У меня комп на работе дымится, когда его одного открывает.
Надо писать макрос.

Дата: Пятница, 31.07.2015, 11:12 | Сообщение № 9

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Цитата Франц, 31.07.2015 в 10:41, в сообщении № 8

на листе 28000 значений, листов 24

28000*24*31= 20832000
Советую создать базу данных, в sql - тогда будет нормально работать - дописывать в нее каждый день.
У меня на работе - база sql - больше 25 млн строк * 20 столбцов + справочники + вычисляемые поля-- работает

Цитата Франц, 31.07.2015 в 10:41, в сообщении № 8

У меня комп на работе дымится, когда его одного открывает.

а ТЕПЕРЬ представьте как он задымится, если вы захотите перелопатить все эти данные для всех фамилий.

Мое Имхо - нужна база sql + PowerPivot для excel - и будет Вам счастье yes

Иногда все проще чем кажется с первого взгляда.

Дата: Понедельник, 03.08.2015, 05:17 | Сообщение № 10

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Всем спасибо за помощь и советы.
Отдельное спасибо SLAVIKу. Благодаря Вам, уже сегодня пишем макрос.
Про базу sql, наверно хорошо. Почему "наверно"? Потому что не имею представления о чем вы говорите

Для этого надо писать ТЗ нашим программистам.

Тема закрыта.