Главная » Статьи » Excel » Графики и диаграммы

Может ли Excel строить сложные кривые?

Меня трудно чем-то удивить в Excel, но когда я впервые увидел вот этот сайт, то у меня чуть глаз не выпал на клавиатуру. Очень рекомендую вам пройти по ссылке и поглазеть. В моём личном рейтинге эта команда (там двое мужчин и девушка) является №1 в мире по Excel.

Сегодня я вам покажу нечто из их арсенала (с моими некоторыми адаптациями). Посмотрим на то, как при помощи точечной диаграммы с гладкими кривыми строить ТАКИЕ сложные вещи, что, когда глядишь на это в первый раз, просто не веришь, что это вообще можно нарисовать в Excel. Ну, например, как вам это:

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

Те граждане, кто пока плохо понимает, зачем это всё надо, сейчас просто откроют файлик, включат макросы и нажмут кнопку Старт, потому что это ПРОСТО КРАСИВО. На это можно смотреть минут 15 и всё равно будет интересно. Дольше смотреть не рекомендую, так как глаза и мозг устанут.

Смотреть красоту ТУТ

А с теми, кому интересно, как это устроено, продолжаем разговор.

В принципе абсолютно без разницы, какой график строить. Я взял циклоидальные кривые, так как они очень красивы, разнообразны и сложны.

Эпициклоиды и гипоциклоиды (подмножество циклоидальных кривых) строятся по следующим параметрическим формулам:

Эпициклоиды строятся так:

Гипоциклоиды так:

Знаки у R1 и R2 относительно друг друга определяют тип кривой (по какой поверхности круга R1 - по внутренней или внешней - будет катиться круг R2). Обратите внимание, что t - это угод в радианах. Напоминаю, что углы в 5' и 365' - разные углы.

Если мы заглянем в ряды данных диаграммы, то увидим это:

Сердце решения располагается тут:

Как видите, большинство именованных диапазонов просто сылаются на конкретные ячейки и ничего сложного из себя не представляют. Кроме трёх диапазонов: arr, x_e, y_e. Можно заметить, что x_e и y_e - это то, на что ссылается ряд данных в диаграмме, и, что это формулы, описывающие кривые, которые я приводил выше. А вот ИД arr надо осмыслить:

=СТРОКА( СМЕЩ( Лепота!$A$1; ; ; x) )

X у нас равен 5400. Функция СМЕЩ() сформирует диапазон A1:A5400. А формула СТРОКА() будет вычислена для каждой строки данного диапазона, поэтому в результате мы получим массив чисел от 1 до 5400. Этот массив, будучи переданным в x_e, y_e тоже породит соответствующие массивы. Обратите внимание, что arr пропускается через функцию РАДИАНЫ(). Таким образом мы градусы (1..5400) переводим в радианы. Если честно, то я не знал, что ТАК можно :)

Вот собственно и всё. А тот макрос, который у меня есть в книге, он просто случайным образом, но плавно меняет параметры R1, R2, D в пределах от From до To с шагом Step.

P.S. Любителям WinAPI с его Sleep рекомендую обратить внимание на конструкцию Application.Wait Now + 1 / 24 / 60 / 60 / 2 :)

Ну а теперь залипайте дальше на лицезрение циклоид :)

Категория: Графики и диаграммы | Добавил: dsb75 (18.09.2015) | Автор: Батьянов Денис E W
Просмотров: 5608 | Комментарии: 12 | Теги: гипоциклоиды, именованные диапазоны, Диаграммы, циклоиды, СМЕЩ, эпициклоиды | Рейтинг: 5.0/5
Всего комментариев: 11
2 MikhaOrlov   (07.07.2017 15:02)
Здравствуйте, Денис.
"Позалипал" на циклоиды. Красиво, но в процессе возникли замечания.

1. Вообще-то, X = R * cos (t); Y = R * sin (t). В вашем примере оси X и Y перепутаны местами, если брать за основу картинку из статьи, циклоида на диаграмме строится от вертикальной оси против часовой стрелки. Попробуйте ввести на листе разные значения угла, меньше 360 (45, 90, 180, 270) - увидите, о чём я. На красоту не влияет, конечно.

2. Попробуйте ставить в ячейку х значения 1 и 5400 и сохранять книгу, размер файла изменится в несколько раз. Диапазоны значений не хранятся на листе, но диаграмма хранит весь диапазон значений в файле. Для уменьшения размера можно кодом VBA присваивать x 5400 после открытия и 1 перед закрытием.

3. Ни в одной из статей вашего сайта нет ни одного вызова Windows API, да и фраза "Любителям WinAPI с его Sleep" позволяет предположить, что сами вы к его любителям не относитесь. Можно полюбопытствовать, откуда у вас такое принципиальное (практически - религиозное :-) ) избегание Windows API? Просто потому, что это "не совсем Excel"? Не хотите вступать на территорию, которую в принципе невозможно исследовать целиком?
Я тут немного дополнил ваш пример, оставив его "как есть" и дополнительно сделав всё то же самое, но на форме и кодом VBA (кнопка "форма"). 
С WinAPI 32, естественно. Буду благодарен, если вы его посмотрите и выскажете своё мнение. Сайт не позволяет прикреплять файлы, поэтому ссылка: https://yadi.sk/i/UjUOP-hG3KqPYN

Для корректности сравнения скоростей из вашего кода выкинуты строки:
Код
ActiveSheet.Calculate ' не нужна, если есть  DoEvents

Application.Wait Now + 1 / 24 / 60 / 60 / 2 ' просто не нужна :-)


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

0
3 dsb75   (07.07.2017 15:32)
Здравствуйте.
1. Видимо вы правы
2. Интересный факт, но для цели статьи не особо важен.
3. Пальцем в небо smile Зачем делать столь глубокие выводы на единичных фактах? Я дурачился. Но вы правы в том, что WinAPI я не знаю, но никаких религиозных запретов на этой почве перед собой не воздвигаю. Просто часто sleep - единственное, что видишь у VBA-шников, но в Excel особой необходимости в нем нет. То есть, если вам нужно сделать паузу, то нефиг городить огород с winapi. Файл ваш посмотрел - офигенно. Дома посмотрю подробнее. Вот для этого и winapi не грех применять smile

Спасибо за содержательный комментарий. Успехов вам!

4 MikhaOrlov   (10.07.2017 12:19)
Использование Sleep вместо Application.Wait является шагом вперёд, осознанным или нет - второй вопрос.

При выполнении програмной задержки состояние приложения можно разделить:

- по загрузке процессора: грузит / не грузит;
- по реакции на события: не реагирует / реагирует;

Так вот, Application.Wait находится в худшем из четырёх возможных сочетаний этих состояний.

Пусть надо выполнить програмную задержку dDelay сек. Тогда варианты:

' вариант 0: не реагирует на события, загрузка процессора (в современном мире - одного из ядер) - 100%
Application.Wait Now + dDelay / (24 * 60 *60)

' вариант 1: не реагирует на события, загрузка процессора - 0%
Sleep 1000 * dDelay

' вариант 2: реагирует на события, загрузка процессора - 100%
dWhaitFor = Timer + dDelay
Do 
    DoEvents
Loop Until Timer > dWhaitFor

' вариант 3: сочетает достоинства и недостатки двух предыдущих, (не мгновенно) реагирует на события, загрузка процессора - больше 0%
dWhaitFor = Timer + dDelay
Do
    Sleep 50
    DoEvents
Loop Until Timer > dWhaitFor

Ни один из этих вариантов не является идеальным с точки зрения потоков, обработки сообщений, машинного времени и пр.
В этой статье http://www.smsoft.ru/ru/vbwait.htm описан полностью корректный вариант.
Статья "загрузочная" wacko , так что лучше прочитать её "по диагонали" и никогда так не делать.  smile

0
5 dsb75   (10.07.2017 14:15)
Ну, остаётся только согласиться, что Application.Wait в Excel убог. Значит sleep нужен в любом случае smile
Михаил, вам сколько лет? Держу пари, что 50, как минимум. Чувствуется в Вас этакая оld school основательность smile

6 MikhaOrlov   (10.07.2017 14:40)
Ровно столько, сколько и вам: 42.  smile

0
7 dsb75   (10.07.2017 14:44)
Вдвойне похвально. Чем занимаетесь?

8 MikhaOrlov   (10.07.2017 15:25)
Долгая история  wacko 
Прямо сейчас моя работа не связана с ИТ. Собираюсь её поменять на программиста VBA в ближайшие несколько месяцев.

0
9 dsb75   (10.07.2017 16:07)
Забавно. У меня сейчас тоже не связана. VBA программист? Вы серьёзно? 
Мягко говоря, не самый перспективный язык... На себя хотите работать или как?

10 MikhaOrlov   (10.07.2017 16:48)
Давайте, о себе любимом напишу подробно позже и в почту.

Пока в догонку об убогости Application.Wait: https://yadi.sk/i/7NQT-OFN3Kv2BK
Комментировать там нечего, просто текст программы посмотрите.

Коротко: Application.Wait не предназначен для организации задержек, не кратных 1 сек., в частности - менее 1 сек. Поэтому он у вас в Beauty на самом деле не работал, можно было апостроф в начале строки не ставить   smile .Добавить
И чтоб совсем добить тему. Посмотрите загрузку процессора в task manager при работе Application.Wait и Sleep.

0
11 dsb75   (10.07.2017 17:16)
Ну вы просто уничтожили бедный wait smile
Странно, что они его реализовали не через sleep...

0
1 dsb75   (22.09.2015 20:03)
Аналогично smile

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика