on-line-teaching.com

[Lilia]

Здравствуйте, я далеко не эксперт - почти начинающий.
Нужна помощь!

Как обновлять диаграммы - ежемесячно, к примеру в этом месяце показывать данные с января по июнь, в следующем с февраля по июль, потом с марта по август - т е передвигать в право по месяцам.
Возможно ли это осуществить в Excel автоматически или же с минимальными усилиями. Диаграммы и таблицы расположены на разных страницах файла

У меня сотни графиков и каждый месяц я с ума схожу делать все эту в ручную, занимает неделю.
Если знаете подскажите как, или хотя бы намекните.

Заранее благодарна!

[abcde]

Да это возможно с помощью VBA.
Если диаграммы расположены не на рабочих листах, то их можно перебрать в цикле:

For Each diagram In Charts
msgbox diagram.Name ‘ показываем имя листа диаграммы
Next

Установить новый исходный диапазон данных для конкретной диаграммы:
Charts(diagram).SetSourceData Source:=Sheets("Лист3").Range("A1:F4")

Где diagram – индекс (номер по порядку) диаграммы.

Возможны разные варианты. Необязательно изменять исходный диапазон данных, можно программно создавать новые диаграммы.
Для более подробного ответа надо знать структуру рабочей книги и возможно придется ее немного изменить.

[AsIs]

Есть тут одна проблемка - получение ссылки на имеющийся диапазон, по которому диаграмма построена. А это нужно, т.к. автор вопроса попросил выполнять смещение относительно него.

[abcde]

Получение ссылки на имеющийся диапазон раскопать не удалось. Нет необходимости получать эту ссылку тем более, если формировать диаграммы программно.
“Проблемка ” выдумана для рекламы своего сайта (платных курсов). Некрасиво давать ссылку не на конкретную тему форума с решением задачи, а на форумы своего сайта вообще. Надо уважать людей! Кстати на avalon.ru на форуме, посвященном Microsoft Office в теме посвященной поиску последней записи на рабочем листе Excell (http://forums.avalon.ru/forum/topic.asp?TOPIC_ID=8304) Alexander.Schukin (почему-то ассоциируется с AsIs) утверждает:

Galina, есть проблемы решения которых нужно\можно искать. А есть проблемы, которые можно только констатировать, как факт. Но сделать что-то нельзя.
Это наверное такой случай.
Посудите сами, если Вы ничего неординарного не делаете, а программа ведет себя таким образом, то что остается... только развести руками.
Ну вот еще разве использовать команду Save.

-------------------------
Форум хорошо, а форум+курсы лучше!


Я понимаю, что человеку нужны деньги, но все-таки надо стараться оставаться человеком.

У этой «Проблемы» Alexander.Schukinа (представленного на Avalon.ru как “Декан Факультета переподготовки специалистов”) есть минимум два решения:

1. y = Range("A1").End(xlDown).Row – эквивалент нажатия клавиш (Shift + End и затем ↓)
2. y = Range("A1").CurrentRegion.Rows.Count
или y = Cells(1, 1).CurrentRegion.Rows.Count
Видимо товарищ очень редко обращается к встроенному в Excel Help VBA.
Вот такие у нас курсы и к сожалению не только курсы.
Прошу прощения за отступление от темы.

[Urikor]

Ну, здесь, abcde, Вы немножко не правы

Ставить ссылки на свои ресурсы в подписях сообщений на форуме разрешено, поэтому, упрекать в этом Александра не стоит.

Тем более, что ссылка на Авалон стоит вполне "легально" на главной русскоязычной

[AsIs]

abcde, ой -ей сколько в Вас желчи-то, гражданин... Спокойнее нужно быть.

Не собираюсь комментировать Вашу петицию, честно говоря времени нет. Как впрочем и другие Ваши ответы. Я своей ремаркой хотел обратить Ваше внимание на эту проблему, пригласить к диалогу, без всякой задней мысли. Но вижу тут полный "неадекват".

Однако прошу заметить, что Вы ответ на вопрос так и не дали. Я знаю, есть такой тип пользователей на форумах, пытаются чего-то из себя изобразить, даже не пробуя свои же советы на практике, которые оказываются неработающие. В итоге пыль в глаза пустили, но автору вопроса от этого не легче, его проблема ни на йоту не сдвинулась.

PS: ссылка в моем сообщении это не реклама - мы в ней не нуждаемся. Точнее говоря, в рекламе такого плана.
Это просто способ дополнить возможности двух хороших организаций и сайтов avalon.ru и urikor.ru, объединить контингент этих двух сайтов, чтобы в случае заинтересованности посетители могли найти подходящее предложение на сайте партнера. Вот видите ,все проще.

PPS: да, господин abcde, меня зовут Александр В. Щукин.

[Lilia]

Уважаемые господа!

Спасибо за отзывы, честно говоря я сомневалась что будут ответы, так как знаю что многие ленивые студенты пользуются подобными форумами дабы решить свои задачки.

Я поняла что возможно сделать так чтобы графики обновлялись, но звучит это очень сложно для меня. Так что я думаю,что скорее всего буду думать как переделать все файлы, и поместить графики с таблицами, так что я смогу менять data range вручную handler-ами.

Пожалуйста не ссорьтесь , честно - моя проблема с диаграммами не стоит чтобы было столько негатива.
Мир!!!Дружба!!!Ура!!!

С уважением, Лилия

[AsIs]

Лилия, давайте я все-таки попробую подвести логическую черту. А может и поставить запятую.

Как Вы поняли выше, вариант, который предлагается использует язык программирования VBA. А потому, если у Вас не пугает необходимость с этим разбираться и в той организации, в которой Вы работаете макросы как таковые не запрещены, то тогда вперед.... Если это не так, то то,что я напишу ниже можете не читать.
------------
Итак, программно действительно можно обойти все диаграммы и задать им явным образом диапазон исходных данных. Но! Вот получить текущий диапазон, по которому диаграмма строится не получается. А это проблема, т.к. не получается сместиться относительно него.
Можно конечно от начала и до конца строить диаграммы программным образом, но это достаточно сложная задача, тем более, которая возможно идеть вразрез ТЗ.

Немного подумав, я могу предложить вариант другой. Вместо ввода адресов ячеек в диаграммах нужно ввести имя. Создасть имя: Вставка\Имя\Присвоить.
В этом случае нужно делать обход и изменение не диаграмм, а имен. У имени в макросе можно получить ее диапазон и соотв-но сдвинуться относительно него на один столбик вправо. Цикл по именам выглядит примерно так:
Dim c As Name

For Each c In Names
MsgBox c.RefersToRange.Address
Next

Если такой способ Вам подходит, то я напишу Вам его более детально.

[Lilia]

Уважаемый Александр,

Спасибо за вашу отзывчивость!

Да, макросы у нас не заперщены - по крайней мере не слышала об этом (кстати с ними тоже никогда не работала),я решительно готова углубиться в дебри VMA, так как оно того стоит - мне главное в этом разобраться хорошо, ну я не хочу делать что то - чего в итоге будет работать - а я не в курсе как оно работает и почему, потому что если вдруг какая ошибка , я сама не сумею с ней справиться, а ответственность на мне.

Так что мне любой способ годиться, лишь бы был результат - тратить как можно меньше времени на ежемесячные обновления графиков.

Жду дальнейших указаний.

С уважением, Лилия

[abcde]

Господин Щукин все равно закончил скрытой рекламой.
Администратор сайта сам подтверждает, что: «ссылка на Авалон стоит вполне "легально" на главной русскоязычной».
Комментировать действительно нечего, лучше посоветуйте Galine (см. мое предыдущее сообщение) решить ее проблему хотя бы с помощью поиска пустой ячейки в цикле или записью макроса: Главное меню – Правка – Найти.
Невозможность решения проблемы чаще всего говорит о том, что мы мало знаем. Я – не исключение и тем более не Гуру, просто стараюсь добросовестно помогать другим. А теперь к «проблемке» господина Щукина. Раскопать не удалось – это значит, что пока не нашел решения (см. мое предыдущее сообщение). Вернемся к решению вопроса, поставленного господином Щукиным.

Не долго сражался я с ПРОБЛЕМОЙ, положила она меня на обе лопатки в мгновение единое. И направил я стопы своя, по совету посвященных, в священный град Авалон (да простят меня обитатели всех Авалонов). Ночь на пролет восхвалял я божеств местных и молил их ниспослать код верный, но видно жизнь моя юзеровская неправильная вся была, потому, как экран монитора пестрел сообщениями: “Отстань со своими мелочами”. Однако как только первый луч солнца коснулся земли нашей грешной, за усердие мое ниспослан был код верный, а главное робастный до невозможности. Еще день ушел на осмысление сей тайны великой, и настал час поделиться сим знанием со всеми живущими во исполнение клятвы страшной, данной божествам Авалона.
Это конечно шутка, а то все очень мрачно получается.

В Microsoft Excel Objects (Charts) нет объекта с данными для диаграммы, поэтому пришлось идти обходным путем:

Sub Data_Range_Of_Chart()
'поиск ссылки на диапазон ячеек исходных данных
'для диаграмм расположенных на листах диаграмм рабочей книги
'не претендует на совершенство, но работает нормально

'получаем текстовую ссылку на первый ряд данных в диаграмме
d = Charts(1).SeriesCollection.Item(1).Formula
'извлекаем из строки нужный нам текст
d1 = InStr(1, d, "(")
d2 = InStr(1, d, "!")

'получаем имя рабочего листа с данными для диаграммы 1
sH = Mid(d, d1 + 1, d2 - d1 - 1)

d3 = InStr(1, d, ",")
rNG = Mid(d, d2 + 1, d3 - d2 - 1)

'получаем ссылку на диапазон ячеек с данными для диаграммы 1
rNG = Sheets(sH).Range(rNG).CurrentRegion.Address

End Sub

Для ГУРУ – Microsoft Excel Objects (Charts) и Microsoft Graph не одно и то же.

Господин Щукин, если у Вас действительно не работают мои советы, не стесняйтесь, обращайтесь ко мне, я в меру своих сил и возможностей постараюсь все разложить по полочкам (котлеты отдельно, мухи отдельно – дискуссия дискуссией, а помощь помощью).

Уважаемая Лилия!
На счет “Мир!!! Дружба!!! Взаимное уважение!!!” я обеими руками «ЗА». ТЗ – это техническое задание на проектирование. Лучшим ТЗ будет образец Вашей рабочей книги. Для неразглашения данных о Вашей фирме замените все названия и данные любыми, которые придут Вам в голову. Не присылайте всю книгу, достаточно куска. Главное – объяснить принцип и тогда, скорее всего, появятся конкретные ответы и у Александра и у меня и у других участников этой темы. Я надеюсь, что общими усилиями мы одолеем задачу.

Уважаемые читатели этой темы приношу Вам свои извинения за получившийся диалог.

[AsIs]

Господин аноним, abcde.
Не надо так нервничать, если кто-то нашел изъян в вашем ответе, сразу бежать на его сайт и поливать всех и вся. Ничего страшного, ведь если вы профессионал, то от вас не убудет.

Я мне намерен продолжать с вами диалог, потому что:
1. Я не собираюсь перед вами доказывает свой уровень знаний и профессионализм. Мне этого не надо.
2. Я не общаюсь с такими хамами. Предлагаю вам немного поучится вежливому разговору. И еще давать ответ на поставленный вопрос и не отвлекаться от темы (это заболевание есть такое ).
3. Вы даете некоректные ответы. Даже в случае с вопросом Галины. Просто перечитайте еще раз внимательно вопрос и посмотрите какие варианты предлагаете вы и немного (чуть-чуть) подумайте.
4. И последнее. Помните, не вы здесь модератор.

[Urikor]

На этом "обмен любезностями" будем считать законченным.

Просьба дальше постить по проблеме Галины.

Личностные высказывания буду вырезать.

[abcde]

Извините за долгое молчание.
18.07.2008 получил от автора темы книгу – образец.
20.07.2008 отправил книгу – решение.
21.07.2008 автор темы сообщил, что получил рабочую книгу, но сейчас не может ее просмотреть из-за отсутствия архиватора RAR, отправил самораспаковывающийся архив.
Отзыв еще не получил.
Привожу решение задачи без отзыва автора темы.

‘===============================================
Sub Diagram()

‘OffsetSeries – величина смещения данных диаграммы
‘Устанавливаем величину смещения
‘+ n - смещение вправо на n столбцов (месяцев)
‘-n - смещение влево на n столбцов (месяцев)

OffsetSeries = 1

‘Перебираем все рабочие листы книги
For sH_Namb = 1 To ThisWorkbook.Sheets.Count
‘Если на рабочем листе есть диаграммы,
If Sheets(sH_Namb).ChartObjects.Count > 0 Then
‘узнаем их количество
DiagramCount = Worksheets(sH_Namb).ChartObjects.Count
‘и по очереди перебираем их.
For DgmNumber = 1 To DiagramCount
‘Запускаем процедуру (макрос), выполняющую смещение данных и подписей данных ‘очередной диаграммы.
Call Offset_Data(DgmNumber, sH_Namb, OffsetSeries)
Next ‘ переходим к следующей диаграмме
End If
Next ‘ переходим к следующему рабочему листу
End Sub

‘===============================================
Sub Offset_Data(DgmNumber, sH_Namb, OffsetSeries)

‘Узнаем количество рядов (линий, столбцов) диаграммы
SeriesCount = Worksheets(sH_Namb).ChartObjects(DgmNumber).Chart.SeriesCollection.Count
‘и перебираем их.
For n = 1 To SeriesCount
‘ Получаем строку, описывающую очередной ряд данных.
d = Worksheets(sH_Namb).ChartObjects(DgmNumber).Chart.SeriesCollection(n).Formula

d1 = InStr(1, d, ",")
d2 = InStr(1, d, "!")

‘ Из полученной строки выделяем название рабочего листа, на котором расположены ‘данные.
sH = Mid(d, d1 + 1, d2 - d1 - 1)
d3 = InStr(d2, d, ",")

‘ Из полученной строки выделяем интервал ячеек, содержащий подписи к очередному ‘ряду данных.
Signature_Range = Mid(d, d2 + 1, d3 - d2 - 1)
d4 = InStr(d3 + 1, d, "!")
d5 = InStr(d4 + 1, d, ",")

‘ Из полученной строки выделяем интервал ячеек, содержащий данные очередного ряда ‘данных.

Data_Range = Mid(d, d4 + 1, d5 - d4 - 1)

‘Интервал данных & Range(Data_Range) смещаем в соответствующую сторону ‘Offset(rowOffset:=0, columnOffset:=OffsetSeries) и получаем адрес нового интервала в ‘стиле xlR1C1 (Address(ReferenceStyle:=xlR1C1)).
‘Формируем строку, указывающую новый интервал ячеек с данными для очередного ряда ‘данных и изменяем интервал данных.
Worksheets(sH_Namb).ChartObjects(DgmNumber).Chart.SeriesCollection(n).Values _
= "=" & sH & "!" & Range(Data_Range).Offset(rowOffset:=0, _
columnOffset:=OffsetSeries).Address(ReferenceStyle:=xlR1C1)

‘Формируем строку, указывающую новый интервал ячеек с подписями для очередного ‘ряда данных и изменяем интервал подписей аналогично приведенному выше.
Worksheets(sH_Namb).ChartObjects(DgmNumber).Chart.SeriesCollection(n).XValues _
= "=" & sH & "!" & Range(Signature_Range).Offset(rowOffset:=0, _
columnOffset:=OffsetSeries).Address(ReferenceStyle:=xlR1C1)

Next ‘ переходим к следующему ряду данных.

End Sub
‘========================================
Это решение для диаграмм расположенных на рабочих листах, для листов диаграмм (на листе диаграммы может только диаграмма), решение аналогично только вместо рабочих листов(Worksheets) в цикле перебираем диаграммы (Charts) и соответственно вместо Worksheets(sH_Namb).ChartObjects(DgmNumber).Chart.SeriesCollection(n).Formula применяем Charts(n).SeriesCollection(n).Formula.

Недостаток примера – не контролируется программно выход за пределы данных.

Пример можно скачать http://www.filehoster.ru/files/bg6513
Ограничение файлообменника: Файл будет удалён, если в течение 30 дней этот файл никем не скачивался.

[abcde]

Что-то наши интеллигентные профессионалы молчат. Зря, есть что совершенствовать.