русский
Germany.ruForen → Архив Досок→ Computer & Co

Формула в Excel

325  
OGIS завсегдатай28.11.13 12:12
OGIS
28.11.13 12:12 
Всем привет,
человеку надо помочь вычислить среднемесячные данные в таблице Excel.
Существуют 3 и более колонок (ежемесячно, ежедневно) с дву-трёхзначными цифрами.
Часть цифр помечены красным цветом.
Не могу вспомнить формулу. Напомните пожалуйста (пошагово).
Excel 2000, немецкий.
#1 
Esslinger старожил28.11.13 13:32
NEW 28.11.13 13:32 
in Antwort OGIS 28.11.13 12:12, Zuletzt geändert 28.11.13 13:38 (Esslinger)
Какую формулу нужно? Чтобы пометить красным в зависимости от определенного условия?
Format->Bedingte Formatierung...
#2 
Puschkina старожил29.11.13 09:54
NEW 29.11.13 09:54 
in Antwort OGIS 28.11.13 12:12
=Mittelwert() - Просто среднее значение вычислить. Или в чем задача конкретнее?
#3 
OGIS завсегдатай02.12.13 17:05
OGIS
NEW 02.12.13 17:05 
in Antwort OGIS 28.11.13 12:12, Zuletzt geändert 02.12.13 17:09 (OGIS)
Esslinger & Puschkina
Благодарю вас, что откликнулись.
Пример таблицы:

в конце каждой строки с данными (в конце месяца), хотелось бы иметь среднестатистические данные +
отдельно среднестатистические данные "красных" цифр.
#4 
snort посетитель02.12.13 21:04
NEW 02.12.13 21:04 
in Antwort OGIS 02.12.13 17:05
В ответ на:
в конце каждой строки с данными (в конце месяца), хотелось бы иметь среднестатистические данные


#5 
Esslinger старожил03.12.13 09:42
NEW 03.12.13 09:42 
in Antwort OGIS 02.12.13 17:05, Zuletzt geändert 03.12.13 09:44 (Esslinger)
Такое (среднее красных чисел) стандартными формулами не реализовать, нужно писать свою функцию в VBA (макросы).
Попозже, когда время будет, могу попробовать сваять такую функцию.
#6 
Puschkina старожил03.12.13 11:53
NEW 03.12.13 11:53 
in Antwort Esslinger 03.12.13 09:42
Можно и формулой. Вот:
=MITTELWERTWENN(A1:G1;">70";A1:G1)
(Если данные стоят в ячейках с A1 до G1, и условие - красным помечается то, что больше 70)
#7 
Puschkina старожил03.12.13 11:57
NEW 03.12.13 11:57 
in Antwort OGIS 02.12.13 17:05
Напишите, какое у вас условие для пульса, что должно красным помечаться?
#8 
OGIS завсегдатай03.12.13 17:32
OGIS
NEW 03.12.13 17:32 
in Antwort Puschkina 03.12.13 11:57, Zuletzt geändert 03.12.13 18:37 (OGIS)
Так называемая функция (прибора для измерения давления) "Herzrhythmusstörung" - помечается в таблице красным цветом.
И цифры могут быть самыми различными.
Возможно ли вообще в формулу Excel внести понятие "жирный" шрифт и/ или "цвет".
#9 
Esslinger старожил03.12.13 22:53
NEW 03.12.13 22:53 
in Antwort Puschkina 03.12.13 11:53
Можно, если условие постоянное. Как видно из ответа ТС это не наш случай.
#10 
Esslinger старожил03.12.13 23:22
NEW 03.12.13 23:22 
in Antwort OGIS 03.12.13 17:32, Zuletzt geändert 03.12.13 23:33 (Esslinger)
Ниже в тэгах цитата код функции для выполнения вышепоставленной задачи. Протестирована в Excel 2010.
Способ применения следующий (для первой попытки рекомендую создать пробный файл, либо же работать с копией оригинального файла):
1. В нужном документе клик правой кнопкой на названии рабочего листа (напр. Tabelle1), затем выбрать "Code anzeigen". Откроется окно Microsoft Visual Basic.
2. В новом окне вверху в меню Einfügen выбрать Modul. В окрывшееся окно скопировать нижестоящий текст как есть.
3. Закрыть окно Microsoft Visual Basic и вернуться в окно с открытой таблицей.
4. В нужной ячейке задать функцию =MittelwertWennFarbe().
Функции требуются следующие параметры:
- область поиска (range), напр. А1:Е1
- цвет или числовой код цвета (важно чтобы во всех ячейках был один цвет, а не например разные оттенки какого-либо цвета)
Пара примеров:
=MittelwertWennFarbe(А1:Е1;3) - функция считает среднее значение содержимого ячеек в области А1:Е1, цвет шрифта которых красный (3=красный)
=MittelwertWennFarbe(А1:Е1;А1) - функция считает среднее значение содержимого ячеек в области А1:Е1, цвет шрифта которых функция считывает из ячейки А1.
Для того чтобы результат обновлялся при изменении исходных данных, к основной формуле можно добавить +(0*JETZT()).
Примеры:
=MittelwertWennFarbe(А1:Е1;3)+(0*JETZT())
=MittelwertWennFarbe(А1:Е1;А1)+(0*JETZT())
При изменениицвета шрифта в одной или нескольких ячейках нужно нажать F9 для того чтобы результат формул обновился.
Надеюсь смог помочь. Код функции можно изменять под свои конкретные нужды. Для этого повторить пункты 1-3 выше.
И еще: при сохранении файла появится окно с вопросом по поводу безопасности макросов и т.д. Надо ответить "нет" и в открывшемся окне сохранения файла вместо "Excel-Arbeitsmappe" выбрать "Excel-Arbeitsmappemit Makros" как тип файла (Dateityp).
После этого при открытии файла возможно появиться вопрос разрешить ли макросы, нужно разрешить, иначе функция не будет работать.
В ответ на:
Public Function MittelwertWennFarbe(Bereich As Range, Farbe As Variant)
Dim Zelle As Object
Dim FarbenNr As Integer
Dim Nenner As Integer
Dim Summe As Double
If IsObject(Farbe) Then
FarbenNr = Farbe(1).Font.ColorIndex
Else
FarbenNr = Farbe
End If

For Each Zelle In Bereich
If Zelle.Font.ColorIndex = FarbenNr Then
Summe = Summe + Zelle
Nenner = Nenner + 1
End If
Next
MittelwertWennFarbe = Summe / Nenner
End Function

#11 
OGIS завсегдатай04.12.13 22:04
OGIS
NEW 04.12.13 22:04 
in Antwort Esslinger 03.12.13 23:22
Благодарю Вас, что не поленились написать подробную инструкцию.
По ходу выполнения Вашей инструкции, понял, что абсолютно ничего не помню
и следует снова начинать с азов.
Ещё раз спасибо!
#12