Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Проблема с отображение Диаграмм (нулевые значения для пустых вычисляемых по формулам ячеек)
 
#1
20.12.2010 23:09:39
Доброго времени суток!  
 
Имеется следующая задача:  
 
Нужно отобразить в диаграмме (обычном графике) диапазон числовых значений привязанных к датам (ось "х").    
 
Числовые значения вычисляются по формулам типа 'if(a1="";"";a1/b1)'. Смысл заключается в том, чтобы не пачкать таблицу выражениями типа 'div/0!', если используемые в вычислениях ячейки пусты.  
 
Всё бы ничего, но в случае, когда выполняется условие 'a1=""' и ячейке присваивается значение '""' диаграмма считает, что значение ячейки равно нулю. В итоге график содержит фальшивые данные (нули) и соответственно неправильно вычисляет тренд.  
 
Все попытки изменить ситуацию через прямые настройки диаграммы ни к чему не привели.  
 
Решил сделать это через макросы, но столкнулся с проблемой, что не могу присвоить значение конкретной ячейки данных в диаграмме. Обращение возможно только ко всему графику:  
'Sheets("Page1").ChartObjects("Diagramm1").Chart.SeriesCollection(1).Values'  
 
 
Если подскажете, как обойти эту загвоздку или возможно решить проблему другим способом, буду очень признателен.  
 
С уважением,  
 
Дмитрий
 
 
 
#2
20.12.2010 23:17:12
Здравствуйте! А интерполировать значения - не вариант? Сервис - Параметры - Диаграммы.
 
 
 
#3
21.12.2010 09:33:51
Можно поподробнее?  
 
 Я работаю с одной из последний версий офиса (2010), там не имеется меню как такового. А Меню свойств диаграммы позволяет настраивать только внешние данные.  
 
 Что подразумевается в данном случае под интерполяцией?
 
 
 
#4
21.12.2010 11:40:11
Чтобы не быть голословным прикрепляю файл с пояснением.  
 
 Первая диаграмма показывает, как выглядит график взятый из ячеек с формулами.  
 
 Во-второй диаграмме заполненные ячейки вычисляются по формуле, а остальные удаляются вручную. При этом график имеет "правильный" вид.  
 
 Как сделать так, чтобы диаграмма брала данные из заполненных формулой ячеек, но не приписывала пустым ячейкам нулевые значения?  
 
 Цель: создать полностью готовую таблицу, в которую пользователь будет только вводить данные (без лишних движений мышью, типа удаление пустых ячеек). Из данных будут регулярно создаваться отчёты, а посму наличие строк типа "div/0" и тому подобных нежелательно.  
 
 Прошу не обращать внимание на внешний вид таблицы и диаграмм, они вырваны из контекста для облегчения понимания проблемы.  
<EM><STRONG>Файл удален</STRONG> - велик размер - [<STRONG>МОДЕРАТОРЫ</STRONG>]</EM>
 
 
 
#5
21.12.2010 12:32:00
Файл будет удален. Нечего съедать ресурсы форума (об этом есть в правилах - вторая тема сверху). Поиском пользоваться тоже не грешно, слово "интерполяция" нашлось 669 раз, даже при повторах поисковика есть ссылки на нужные темы.  
Меню Сервис-Параметры-Диаграмма-Интерполировать.
 
 
 
#6
21.12.2010 13:16:49
Абсолютное большинство ссылок из поиска (начиния страницы этак со второй) ведут на одну из главных страниц форума, а не к запрашиваемым темам (возможно они удалены или перемещены и не переиндексированны). Тем не менее принимаю упрёк и буду искать, но первые исследования показывают, что речь идёт о несколько других материях (встраивание дополнительных графиков, создание таблиц для дополнительных вычислений и т.п.).  
 
Далее по списку: совершенно не понял, почему я получил по шапке за размещение файла. Цель этого была наглядно показать, в чём заключается проблема. Что так же находится в согласии с одним из ваших собственных постов к другому участнику форума "Для этого Вам нужно немного напрячься и создать простой пример с требуемым..."  
 
 
Ещё раз коротко описание проблемы:    
 
- Данные для графика берутся из таблицы.    
- Табличные значения вычисляются по формуле.  
- Формула решает, присвоить ячейке числовое или пустое значение  
- Пустые ячейки (значение "") обображаются на графике как нулевые, а не должны отображаться вообще.  
 
 Речь идёт о таблице со многими сотнями записей и десятками параметров (и соответственно десятками графиков). Возможности размещать двойные таблицы с убранными пустыми ячейками нет. Всё что нужно, это красивое отображение графиков (и как следствие правильно вычисленные тренды и т.п.).  
 
 Как уже сказал, буду пытаться найти решение при помощи интерполяции, но если есть возможность, прошу пояснить спорные моменты для возможной экономии времени и сил.  
 
С уважением,  
 
Дмитрий
 
 
 
#7
21.12.2010 13:30:48
Получается, что интерполяция как и нд() мне не подходят, так как меня вполне устраивают разрывы в графике, но не устраивают нулевые значения вместо разрывов и надписи типа "нд()" в ячейках таблицы (в этом случае так же некорректно происходит вычисление среднего значения диапазона)
 
 
 
#8
21.12.2010 13:31:04
{quote}{login=Dmitry_V}{date=21.12.2010 01:16}{thema=Re: }{post} совершенно не понял, почему я получил по шапке за размещение файла. {/post}{/quote}  
За то, что разместили большой файл, т.е. - не ознакомились с Правилами.
 
 
 
#9
21.12.2010 19:47:04
Моя ошибка, спасибо за разъяснение.  
 
Попытка номер два: Файл с примером проблемы
 
 
 
#10
21.12.2010 20:45:19
Не вникал, т.к. смотреть столько данных нет желания. Возможно, не устраивает "пустой" график в конце? Посмотрите в поиске "динамичный график", т.е. график, у которого диапазон исходных данных изменяем.
 
 
 
#11
21.12.2010 21:23:47
Сложного в графике ничего нет: данные для графика берутся из столбца "P".    
 
Также в области данных графика указан диапазон за весь текущий год, поэтому ничего странного в "пустоте" графика нет, данные будут запольняться постепенно.  
 
Неустраивает, что график интерпретирует выданные формулой пустые значения ("") как нули и тем самым придумывает несуществующие значения (что ведёт к неправильному отображению как самого графика, так и тренда).  
 
После разбора истории с интерполяцией и сопуствующими опциями получается следующее: это решение не подходит в принципе, так как преобразует пустые ячейки в нули/промежуточные значения, а не наоборот (так как работаю с английской версией офиса, то не сразу понял в чём заключается суть рекомендаций интерполировать данные).  
 
Подстановка "нд()" так же не подходит, потому как таблица (предназначенная для постоения отчёта) приобретает неряшливый вид а так же перестают вычисляться суммы и средние значения по столбцу.  
 
Построение дополнительных таблиц труднореализуемо, так как файл и без того перегружен данными (и лишние таблицы приведут к неразберихе), которые так же имеют жёсткую привязку к оси абсцисс на графике (набор данных из трёх значений привязывается к конкретной дате и если начинать убирать фильтром пустые значения, то на графике будет куча-мала).  
 
Единственная альтернатива VBA, по моему мнению, состоит в том, чтобы вручную подставлять формулу для расчёта только в те ячейки, где не должно быть пустых значений. Так как заранее неизвестно, где будут такие значения, а где нет, то формулы придётся подставлять в реальном времени, а это неприемлимо (конечный пользователь должен просто вводить начальные данные и распечатывать отчёты).  
 
Таким образом пытаюсь в данный момент найти способ прочитать из таблицы строчку ...SeriesCollection(x).Values, которая содержит искомые данные. Так как не имею опыта работы с бейсиком, то испытываю известные трудности.  
 
Если будут предложения, то пожалуйста...
 
 
 
#12
25.12.2010 12:21:17
Так как желающих/могущих помочь советом не наблюдается, пришлось воспользоваться единственной доступной альтернативой и написать некрасивый макрос типа:  
 
Sub Tabellenaktualisierung()  
'  
' Tabellenaktualisierung Macro  
'  
' Keyboard Shortcut: Ctrl+t  
'  
Worksheets("Daten").Activate  
                               ' Aktivierung der Seite "Daten" um auf die Zellen zugreifen zu koennen  
     
For i = 5 To 1096               ' Datenzeilenindex  
 
                               ' pruefen, ob folgende Werte ausgerechnet werden muessen  
 
   If (Cells(i, 40).Value <> "") And (Cells(i, 40).Value <> 0) Then    ' Taktzahl "P"  
                                                                       ' Formel: J / AN  
       Cells(i, 16).Value = Cells(i, 10).Value / Cells(i, 40).Value  
   Else                        ' Wenn nicht, dann wird die Zelle geloescht  
       Cells(i, 16).ClearContents  
   End If  
...  
...  
...  
Next  
End Sub  
 
Макрос запускается по нажатию комбинации клавишь и требует для выполнения около минуты.  
За сим проблему считаю принципиально решённой. Если есть предложения по оптимизации кода, буду рад их услышать.    
Спасибо всем нашедшим время почитать и ответить на пост.  
С уважением,  
Дмитрий
 
 
 
#13
25.12.2010 12:40:47
Сюда загляните: http://andypope.info/charts/brokenlines.htm
 
 
 
#14
25.12.2010 14:43:44
Можно отключить обновление экрана и автоматический пересчет - это ускорит работу макроса.  
разделить проверку не несколько этапов  
обрабатывать только диапазон с данными.    
Например так, проверяйте (для копирования текста макроса - нажмите ответить с цитированием. В окошке появится текст с отступами)  
Будет тормозить - обращайтесь. Загоним данные в массивы и будем крутить в них.  
Будет быстро, но не совсем понятно.  
 
Option Explicit  
 
Sub Tabellenaktualisierung()  
   Dim lLastRow As Long, i As Long  
       ' Tabellenaktualisierung Macro  
   ' Keyboard Shortcut: Ctrl+t  
 
   'um Macro schneller machen, shalten ScreenUpdating und Calculation aus  
   Application.ScreenUpdating = False    '  
   Application.Calculation = xlManual    '  
 
   'Zaigen Excel welche Seite wir bearbeiten  
   With ActiveWorkbook.Worksheets("Daten")  
       'jetzt brauhen wir das nicht - "Aktivierung der Seite "Daten" um auf die Zellen zugreifen zu koennen"  
 
       'rechnen lezte Row mit Daten an der Seite  
       lLastRow = .Row + .Rows.Count - 1  
       For i = 5 To lLastRow    ' Datenzeilenindex  
 
           ' pruefen, ob folgende Werte ausgerechnet werden muessen  
           If .Cells(i, 40).Value <> "" Then  
               If .Cells(i, 40).Value <> 0 Then    ' Taktzahl "P"  
                   ' Formel: J / AN  
                   .Cells(i, 16).Value = .Cells(i, 10).Value / .Cells(i, 40).Value  
               End If  
 
           Else    ' Wenn nicht, dann wird Daten in der Zelle geloescht  
               .Cells(i, 16).ClearContents  
           End If  
       Next  
   End With  
   'Einschalten  
   Application.ScreenUpdating = True    '  
   Application.Calculation = xlAutomatic    '  
 
End Sub
 
 
 
#15
25.12.2010 14:48:17
{quote}{login=Юрий М}{date=25.12.2010 12:40}{thema=}{post}Сюда загляните: http://andypope.info/charts/brokenlines.htm{/post}{/quote}  
 
Уважаемый Юрий,  
 
Вы уже давали совет с интерполяцией, но он не подходит по условию, потому что:  
 
- он либо действует только если ячейка пуста и требуется определиться, что будет отображаться на графике: ничего, нуль или усреднённое значение  
 
- либо хотя при введении NA() точка на графике не равна нулю, но зато загрязняется лишней надписью ячейка в таблице (по определению она должна быть пуста, если отсутствуют вычисляемые данные)  
 
 Моя проблема заключается в том, что в вычисляемые ячейки встроены формулы, и если по условию вычислять нечего (учавствующие в вычислении ячейки первичных данных пусты), то независимо от выбираемого значения (не числового, например "" или подобного, чтобы ячейка представлялась пустой) диаграмма присваивает соответствующему пункту графика нуль и ничего поделать с этим не получается.  
 
 Я подробно описал это в одном из моих постов. Все обычные настройки я уже перепробовал. Оптимально было бы написать макрос для диаграмм, чтобы все нулевые значения просто удалялись. Но к сожалению мои знания VBA недостаточны для этого и я не располагаю достаточным количеством времени для его освоения.
 
 
 
#16
25.12.2010 15:28:32
to Igor67  
 
Нда ваша идея с отключением обновления экрана и автоматического перерасчёта принесла некоторое ускорение с 72 секунд до ... 2 секунд.  
 
На этом уже огромное спасибо.  
 
Далее по списку:  
 
- строчка "lLastRow = .Row + .Rows.Count - 1" инициирует следующую очибку при компиляции: "Object doesn't support this property or method"  
 
- если не определять переменные в начале макроса, то какой тип присваивается по-умолчанию?  
 
- какое преимущество у оборота:  
  "With ActiveWorkbook.Worksheets("Daten") End With"  
  перед:  
  "Worksheets("Daten").Activate" ?  
 
- Вы что-то говорили про массивы? Если можно один короткий пример с пояснениями.  
 
- Ваш немецкий совсем неплох (за исключением пары косяков :) ). Прошу прощения за непереведённые коментарии.
 
 
 
#17
25.12.2010 15:31:42
{quote}{login=Dmitry_V}{post}...Моя проблема заключается в том, что в вычисляемые ячейки встроены формулы, и если по условию вычислять нечего (учавствующие в вычислении ячейки первичных данных пусты), то независимо от выбираемого значения (не числового, например "" или подобного, чтобы ячейка представлялась пустой) диаграмма присваивает соответствующему пункту графика нуль и ничего поделать с этим не получается...{/post}{/quote}  
Выход найти можно, вопрос - насколько он удовлетворит? Для справки: смотрим Вставка-Текст-Объект-Диаграмма Майкрософт Граф, проверяем на стандартном варианте (скрин). А вообще ваш подход к построению диаграммы по данным за год - это экстрим... ;-)  
Z. 85486
 
 
 
#18
25.12.2010 15:34:15
V-2.  
"Выход найти можно, вопрос - насколько он удовлетворит? Для справки: смотрим Вставка-Текст-Объект-Диаграмма Майкрософт Граф, проверяем на стандартном варианте (скрин). А вообще ваш подход к построению диаграммы по данным за год - это экстрим... ;-)".  
Z. 86976
 
 
 
#19
25.12.2010 15:38:53
Да косяк, пробуйте так  
lLastRow = .usedrange.Row + .usedrange.Rows.Count    
oder  
With ActiveWorkbook.Worksheets("Daten").usedrange  
--------  
Вариант  
---------  
Селект и активате = тормоза, требуют вывода обрабатываемой книги "на передний" план и ехс или ВБА может спокойно обходится без них.  
коротко не получится - идея наибольшее время тратиться на операции обращения к ячейкам. Поэтому за 1 раз заносим данные с листа в массив. Массив проверяем, обрабатываем в памяти и формируем итоговый. который так же за 1 раз выгружаем на лист.  
спасибо Abbilingvo и Яндексу
 
 
 
#20
25.12.2010 16:04:37
{quote}{login=Dmitry_V}{date=25.12.2010 03:28}{thema=}{post}- если не определять переменные в начале макроса, то какой тип присваивается по-умолчанию?  
- какое преимущество у оборота:  
  "With ActiveWorkbook.Worksheets("Daten") End With"  
  перед:  
  "Worksheets("Daten").Activate" ?{/post}{/quote}  
Переменные по умолчанию - Variant. тип лучше указывать, экономится память.  
 
В каждой строке внутри With ХХ... End With можно не прописывать ХХ.Оператор  структурирует код, делает более удобочитаемым.
 
 
 
#21
25.12.2010 16:18:48
"при введении NA() точка на графике не равна нулю, но зато загрязняется лишней надписью ячейка в таблице (по определению она должна быть пуста, если отсутствуют вычисляемые данные"  
А если условным форматирование шрифт белой красочкой?
 
 
 
#22
25.12.2010 17:19:19
to Z  
 
 Пожалуйста внимательно посмотрите в описание проблемы: ячейки не пусты, они содержат формулы. Эти формулы присваивают ячейке либо числовое значение, либо "" (в случае если в ячейках из которых берутся данные для вычисления ничего нет).  
 
Пример такой проблемной ячейки: A1=if(B1=0;"";B1+C1). Таким образом если B1 ничего не содержит, то значение A1 будет равно "". Это выглядит как пустая ячейка, но интерпретируется Диаграммой как нуль.  
 
 
 
 
to Igor67  
 
 Спасибо за помощь, с массивами пока не будем заморачиваться (теперь всё работает с приемлемой скоростью). Если будет нужно, почитаю книжку по VBA или попрошу отдельно.  
 
Файл со статистиками составляется один раз за год, потом с ним будут работать люди далёкие от Excel и программирования. Таким образом работа должна быть максимально комфортабельной и внешний вид данных как можно более дружелюбным. Поэтому стараемся.  
 
to vikttur  
 
 Понял, спасибо (иногда проще спросить, чем в хелпе тыкаться)  
 
 
 
 
to Юрий М  
 
 Сам часто занимаюсь "левой резьбой", но в данном случае (как видно из файла с примером) строчки имеют различный окрас, который пришлось бы присваивать либо вручную, либо специальным макросом. Поэтому работа не упростиласть бы. Хотя возможно и есть более простой способ задать в формуле необходимый цвет шрифта?
 
 
 
#23
25.12.2010 17:29:42
Dmitry_V! Было предложено для справки заглянуть во "Вставка объекта", т.к. в "стандартной" диаграмме 2010 (и даже с 1SPb) в РУССКОЙ локали, по-моему, подобной интерполяции нет... Возможно, что ошибаюсь...  
Z. 39992
 
 
 
Страницы: 1
Читают тему
Наверх