Как сделать спидометр в экселе

, чтобы отобразить формулы на рабочем листе. Можно воспользоваться командой Сервис → Параметры → Вид (Tools → Options → View) и установить флажок Формулы (Formulas), хотя это и более длинный путь.

Теперь выделите диапазон В2:В5 и запустите мастер диаграмм. На первом шаге мастера перейдите на вкладку Стандартные (Standard Types) (хотя она должна раскрываться по умолчанию). Затем в группе Тип (Chart Type) выберите вариант Кольцевая (Doughnut). Щелкните кнопку Далее (Next), чтобы перейти ко второму шагу мастера, и удостоверьтесь, что данные выводятся на диаграмму по строкам (в столбцах). Щелкните кнопку Далее (Next), чтобы перейти к шагу 3. Если необходимо, можно что-либо изменить на этом шаге, но для этого трюка настройка этого шага не обязательна. Щелкните кнопку Далее (Next), чтобы перейти к четвертому шагу, и удостоверьтесь, что диаграмма будет создана как объект на текущем рабочем листе (и снова это параметр по умолчанию). Создание диаграммы как объекта упростит работу с ней при настройке спидометра (рис. 5.29).

Рис. 5.29. Обычная кольцевая диаграмма

Выделите кольцевую диаграмму, медленно дважды щелкните самый большой сектор, чтобы выделить его, а затем щелкните его правой кнопкой мыши, в контекстном меню выберите команду Формат точки данных (Format Data Point) и перейдите на вкладку Параметры (Options). Выберите угол поворота для этого сектора равным 90 градусам. Щелкните вкладку Вид (Patterns), выберите невидимую границу и прозрачную заливку, затем щелкните кнопку ОК. По очереди каждый из оставшихся секторов дважды медленно щелкните, затем дважды щелкните, чтобы открыть диалоговое окно Формат элемента данных (Format Data Series), и выберите нужный цвет. Кольцевая диаграмма должна выглядеть, как на рис. 5.30.

Рис. 5.30. Заготовка спидометра

Необходимо добавить еще один ряд (Series 2, Ряд 2) значений, чтобы создать циферблат. Снова выделите диаграмму, щелкните ее правой кнопкой мыши, в контекстном меню выберите команду Исходные данные (Source Data) и перейдите на вкладку Ряд (Series). Щелкните кнопку Добавить (Add), чтобы создать новый ряд, и в поле Значения (Values) выберите диапазон С2:С13. Еще раз щелкните кнопку Добавить (Add), чтобы добавить третий ряд (Series 3, Ряд 3), отвечающий за стрелку, и в поле Значения (Values) выберите диапазон Е2:Е5. Результат должен выглядеть, как на рис. 5.31.

Рис. 5.31. Кольцевая диаграмма с несколькими рядами

Теперь спидометр начинает обретать свой вид. Если вы хотите добавить подписи, стоит загрузить специальные утилиты (John Walkenbach’s Chart Tools) с нашей страницы загрузок. Часть этой надстройки, которая, к сожалению, работает только в Windows, предназначена специально для меток данных. Она позволяет указывать диапазон на рабочем листе, на основе которого будут создаваться подписи данных для рядов диаграммы. Надстройка Джона также поддерживает возможности, перечисленные в следующем списке.

• Chart Size (Размер диаграммы). Позволяет указывать точный размер диаграммы, а также приводить все диаграммы к одному размеру.
• Export (Экспортировать). Позволяет сохранять диаграммы в виде файлов в формате .gif, .jpg, .tif и .png.
• Picture (Рисунок). Преобразует диаграмму в рисунок (цветной или черно-белый).
• Text Size (Размер текста). Фиксирует размер всех текстовых элементов диаграммы. Когда размер диаграммы меняется, текстовые элементы сохраняют свой размер.
• Chart Report (Отчет диаграммы). Генерирует отчет для всех диаграмм или подробный отчет для одной диаграммы.

При помощи этой надстройки отформатируйте ряд Series 2 (Ряд 2), чтобы он отображал подписи данных, взятые из диапазона D2:D13. He сбрасывая выделение Series 2 (Ряд 2), дважды щелкните его, чтобы открыть диалоговое окно Формат ряда данных (Format Data Series). Перейдите на вкладку Вид (Patterns) и выберите невидимую границу и прозрачную заливку. Диаграмма должна выглядеть, как на рис. 5.32.

Рис. 5.32. Улучшенная диаграмма спидометра с добавленными подписями

Выделите ряд Series 3 (Ряд 3), затем щелкните его правой кнопкой мыши и в контекстном меню выберите команду Тип диаграммы (Chart Type). Выберите круговую диаграмму. Да, это выглядит немного странно (рис. 5.33). Но будьте уверены, если круговая диаграмма перекрывает кольцевую диаграмму, вы все сделали правильно.

Рис. 5.33. Круговая диаграмма перекрывает диаграмму спидометра

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

Рис. 5.34. Разобранная и уменьшенная круговая диаграмма

Теперь выделите всю круговую диаграмму, дважды щелкните ее, выберите команду Формат ряда данных (Format Data Series) и перейдите на вкладку Параметры (Options). Измените угол поворота первого сектора на 90 градусов. По очереди выделяйте все секторы круговой диаграммы, щелкайте их правой кнопкой мыши и в диалоговом окне Формат рядов данных (Format Data Series) переходите на вкладку Вид (Patterns). Для параметров Граница (Border) и Заливка (Area) выбирайте значение Невидимая (или Прозрачная) (None) для всех секторов, кроме третьего, который следует залить черным цветом. Вы получите диаграмму, показанную на рис. 5.35.

Рис. 5.35. Диаграмма спидометра, на которой окрашен только третий сектор круговой диаграммы

Чтобы добавить легенду, выделите диаграмму, щелкните ее правой кнопкой мыши, в контекстном меню выберите команду Параметры диаграммы (Chart Options) и перейдите на вкладку Подписи данных (Data Labels). Установите флажок Ключ легенды (Legend Key). Вы увидите спидометр (рис. 5.36). Теперь перемещайте диаграмму, изменяйте размер и редактируйте ее, как необходимо. Теперь, когда диаграмма спидометра создана, нужно создать полосу прокрутки с панели инструментов Элементы управления (Control Toolbox) и связать полосу прокрутки и диаграмму.

Рис. 5.36. Диаграмма спидометра с легендой

Для этого правой кнопкой мыши щелкните область панелей инструментов на экране — это верхняя область экрана, где расположены панели инструментов Стандартная (Standard) и Форматирование (Formatting), — и выберите команду Элементы управления (Control Toolbox). Выберите инструмент полосы прокрутки и перетащите его в нужное место на рабочем листе. Выделите полосу прокрутки, щелкните ее правой кнопкой мыши и в контекстном меню выберите команду Свойства (Properties). Откроется диалоговое окно Свойства (Properties). В поле LinkedCell выберите ячейку F3, укажите максимальное значение 100 и минимальное значение 0. Закрыв это диалоговое окно и переместив полосу прокрутки на диаграмму, вы увидите приблизительно то же, что и на рис. 5.37.

Рис. 5.37. Законченная диаграмма спидометра

Щелкая стрелки или перетаскивая ползунок на полосе прокрутки, вы будете изменять показания спидометра, но помните, что при этом также изменяются данные на связанном со спидометром рабочем листе.

Как построить диаграмму типа спидометр в MS Excel?

Правильно подобранное изображение может донести больше полезной информации, чем самая элегантная таблица. Очень эффектной является диаграмма типа спидометр, которая показывает текущее значение на некоторой ограниченной шкале. На этой диаграмме можно отображать, например, уровень KPI, выполнение плана, уровень обслуживания клиентов и другие показатели с ограниченным диапазоном изменения. Циферблат спидометра делится на зоны, стрелка показывает текущее состояние дел. Снизу можно добавить точное значение измеряемого показателя.

Такая диаграмма отлично встраивается в отчеты и дашборды, т.к. весьма информативна, не занимает много места и на вид красива. Однако этой диаграммы нет в стандартном наборе Excel (во всяком случае пока). Для ее построения, придется использовать доступные средства, скомбинировав кольцевую и круговую диаграммы, для шкалы и стрелки, соответственно.

Вначале об общем принципе. Шкала – это верхняя половина кольцевой диаграммы. Нижняя половина также есть, но она прозрачная. Стрелка – это контур видимого сектора круговой диаграммы. Там же есть еще два сектора, но они прозрачны. Местоположение стрелки определяет измеряемый показатель.

Теперь изучим, как сделать диаграмму-спидометр в Excel. Вначале подготовим данные для шкалы, для чего нужно задать 4 значения: величина нижней прозрачной части, красной, желтой и зеленой зоны (цвета и их количество, разумеется, можно выбирать самостоятельно). Т.к. прозрачная часть занимает половину диаграммы, то она должна быть равна сумме трех цветов. Для простоты пусть весь циферблат занимает 100 делений. Тогда красная зона (плохо) – 50, желтая (нормально) – 30 и зеленая (хорошо) – 20 (50+30+20=100). Чтобы получился полукруг, невидимая часть также должна быть равна 100.

Выделяем весь диапазон и создаем кольцевую диаграмму.

По умолчанию получится следующее.

В параметрах ряда делаем поворот на 90⁰.

Удаляем название и легенду.

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

Получаем циферблат спидометра.

Теперь сделаем стрелку. Подготовим данные, по которым будут строится три сектора круговой диаграммы. Первый сектор – от нуля до стрелки (прозрачный), второй – стрелка, третий – оставшаяся часть круга (прозрачный).

На этот раз секторы должны быть подвижными и зависеть от измеряемого показателя. Результатом будет «отклонение стрелки» на соответствующую величину. Пусть показатель измеряется в процентах и его первоначальное значение равно 60%.

Как и с циферблатом, диапазон от 0 до 100% должен приходиться на верхний полукруг. Тогда весь круг – это 200%. Чтобы стрелка меняла свое положение, первый сектор (от которого строятся остальные) привяжем к значению измеряемого показателя. Стрелка имеет фиксированный размер, установим пока 2% (потом вообще уберем). Последний сектор – это разница между 200% и суммой первых двух секторов.

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

Указываем источник данных (диапазон из трех значений) и ОК. Должно получиться примерно следующее.

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

Меняем диаграмму на круговую.

Обязательно нужно выбрать вспомогательную ось, как на рисунке, иначе стрелка окажется под циферблатом. Осталось повернуть диаграмму на 270⁰ и сделать прозрачными ненужные секторы.

Не забываем убрать контуры секторов.

Чтобы стрелка была больше похожа на стрелку, а не на узкий кусочек пирога, зададим для нее вначале черный (или другой) контур, а затем ширину сектора вместо 2% сделаем 0%.

Сектор исчезнет, а контур превратится в черную линию.

Для более точного и отображения снизу можно добавить цифровое значение показателя. Это сделать очень просто. Вставим прямоугольник с закругленными углами.

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

Отформатируем, как нужно и получим окончательный вид спидометра.

Остался один нюанс. Дело в том, что, если значение выйдет за пределы от 0 до 100%, то стрелка окажется не известно где.

Чтобы исправить возможную ошибку, с помощью функции ЕСЛИ в формуле, определяющей отклонение стрелки, зададим минимальное значение 0 и максимальное 100%.

Примерно так рисуется «классический» спидометр.

Иногда диапазон возможных значений нельзя разделить четкими границам типа «плохо», «нормально», «хорошо». Четких границ может не быть, тогда потребуется плавный переход от одного цвета к другому. Например, когда в качестве результата получается некоторая вероятность (p-value, бинарная классификация и др.) или измеряется уровень дефицита запасов, где также нет четких границ и хотелось бы подчеркнуть их размытость. В этом случае для шкалы спидометра следует использовать градиентную заливку. В целом диаграмма строится также, но ее циферблат состоит из одного цвета, плавно переходящего в другой.

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

Используйте диаграмму спидометр в Excel, чтобы удивить своих коллег, а также руководство. По кнопке ниже можно скачать файл с примерами.

Как сделать биржевые часы в Excel из диаграммы спидометр скачать

Как сделать аналоговые часы в Excel используя стандартные инструменты для визуализации данных и формулы? Для реализации данной задачи можно использовать кольцевую диаграмму так же как при построении популярного типа диаграмм в инфографике – спидометра. Аналоговые часы в Excel можно использовать в качестве таймера. При анализе с учетом разницы во времени в разных временных зонах крупнейших фондовых бирж: New York, London, Tokyo, Sydney, Moscow и т.п.

Пример как из диаграммы спидометр сделать аналоговые часы в Excel

Сначала составим Техническое Задание (ТЗ) для, того чтобы сделать часы в Excel. Как говориться в кулуарах программистов без ТЗ результат ХЗ. Чтобы создать стильные аналоговые часы в программе Excel нам понадобиться:

1. С помощью формул подготовить входящие данные для показателей: часов, минут и секунд.
2. Четыре диаграммы для циферблата и стрелок.
3. Кнопки управления часами: Start и Stop.
4. Код макроса обновления данных.

Формулы для перевода времени в секунды, минуты и часы

Приступим к реализации задачи по пунктам ТЗ. В первую очередь подготавливаем исходные данные. Сначала создаем значения для секундной стрелки. Нам необходимо перевести значение полной текущей даты и времени в секунды:

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

Сумма всех значений при каждом пересчете формул (F9) постоянно должна быть равна числу 60 так как секунды считаются по шестидесятеричной системе счисления (позиционная система счисления по целочисленному основанию 60).

Готовым данные для минутной диаграммы. Для этого нужно перевести текущую дату и время в минуты. На основе текущих значений секунд составляем формулу для определения размера доли диаграммы (от числа 60), которая будет перед минутной стрелкой:

Используя в формуле значение секунд позволит нам определять точное положение минутной стрелки в режиме онлайн. Также указываем размер второй неизменяемой доли 0,1 и вводим формулу для вычисления размера оставшейся доли от 60-ти:

Также сумма всех значений в диапазоне B7:B9 равна числу 60.

И наконец осталось подготовить данные для часовой стрелки. Здесь необходимо перевести текущее значение даты и времени в часы. Сложность заключается в том, что в аналоговых часах минуты и секунды рассчитывается по шестидесятеричной, а часы по двенадцатеричной системе счисления. Поэтому придется использовать более сложную формулу для точного вычисления положения часовой стрелки на циферблате часов:

12;ЧАС(ТДАТА())-12;ЧАС(ТДАТА()))+(B7/60)+(B2/3600)’ >

Эту формулу достаточно легко прочитать. Если количество часов (получено функцией =ЧАС()) от текущей даты и времени (которые возвращает функция =ТДАТА()) составляет больше чем число 12, тогда переводим его в двенадцатеричную систему исчисления вычитая от этого количества число 12. А если меньше чем 12, тогда оставляем все как есть.

И последняя формула для часовой стрелки:

На этот раз сумма всех чисел в диапазоне B12:B14 равна числу 12. Данные подготовлены переходим непосредственно к рисованию диаграмм для аналоговых часов в Excel.

Кольцевая диаграмма для создания циферблата аналоговых часов

Аналоговые часы в Excel нарисуем или сконструируем из 4-х диаграмм. Сначала сделаем стильный циферблат. Для него нам не понадобятся исходные данные, поэтому переместите курсор Excel в любую пустую ячейку и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Кольцевая». После чего будет создана пустая диаграмма, но нам уже будет доступно дополнительное меню из которого выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Выбрать данные»:

В появившемся окне «Выбор источника данных» нажмите на кнопку «Добавить» для добавления первого ряда данных и нажмите ОК:

В результате мы создали рамку корпуса циферблата часов, переходим к делениям.

Далее создаем еще 2 рада данных. Для этого снова нажмите на кнопку «Добавить», но на этот раз в поле значение необходимо ввести массив из 12-ти единиц как показано ниже на рисунке:

Порядок расположения рядов должен быть такой же как на рисунке. При необходимости используйте стрелки (вверх и вниз) для управления расположением рядов на диаграмме.

Далее по прядку настраиваем все 3 ряда данных кольцевой диаграммы для дизайна циферблата. Выделите первый ряд двойным кликом левой кнопки мышки или выбрав инструмент: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Текущий фрагмент»-«Ряд1»-«Формат выделенного» и внесите следующие настройки в параметры ряда:

1. «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«Градиентная заливка»-«Цвета» (RGB коды цветов точек градиента: бирюзовый: 0;173;173, темно-сизый: 0;54;54) здесь же «Граница»-«Нет линий».
2. «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Диаметр отверстия, в % от общего диаметра».

Не снимая выделения с «Ряд1» выберите инструмент для 3D оформления корпуса часов: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Стили фигур»-«Эффекты фигуры»-«Рельеф»-«Угол»:

Корпус аналоговых часов полностью готов!

Теперь делаем настройки для второго ряда. Он послужит нам главным циферблатом. Выделите второй ряд2 таким же способом как вы выделяли первый и внесите следующие настройки в параметры ряда:

1. «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«Сплошная заливка»-«Цвет»-Белый. Здесь же «ГРАНИЦА»-«Сплошная линия»-«Ширина»-0,25пт.
2. «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Угол поворота первого сектора»-15 градусов.

Не снимая выделения со второго ряда данных добавим цифры на циферблат. Для этого нажмите на кнопку плюс возле диаграммы и из выпадающего меню отметьте опцию «Подписи данных» за одно снимите галочку с опции «Легенда»:

По отдельности выделяйте каждую подпись данных ряда и пока не снято выделения вводите в строку формул соответствующую цифру, а после для подтверждения нажимайте на клавишу Enter. Пока полностью не заполните весь циферблат всеми 12-ю цифрами.

Переходим к оформлению третьего ряда. Выделите Ряд3 на диаграмме и указываем только другой цвет заливки: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«Сплошная заливка»-«Цвет»-RGB: 225;239;255. После чего также, как и для второго ряда добавляем метки «Подписи данных» таким же способом, как и в предыдущем примере описанном выше:

На этот раз при поочередном выделении каждой метки в строку формул вводим одну и туже внешнюю ссылку: =Часы!$D$1. Во всех диаграммах Excel всегда используются только внешние ссылки на листы. Слово перед восклицательным знаком «Часы» — это имя листа. В ячейке D1 на которую ссылается ссылка находиться функция =СИМВОЛ(149), которая возвращает символ «•» по коду 149. При желании можно ввести в ячейку D1 любой другой символ или функцию с другим кодом символа для отображения на часах.

Как сделать стрелки для аналоговых часов в Excel

Далее создаем стрелки для аналоговых часов в Excel. Для этого нам понадобиться создать еще 3 диаграммы. Чтобы создать первую стрелку выделите диапазон ячеек A2:B4 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Круговая»:

На диаграмме не видно второй доли «Ширина», но о том что она там присутствует свидетельствует легенда. Сейчас необходимо отформатировать диаграмму таким образом, чтобы была видна только лишь одна вторая доля, которая и послужит секундной стрелкой.

Сначала убираем цвет заливки и линию ряда диаграммы, щелкнув двойным щелчком мышки по ряду и выбрав инструмент: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«Нет заливки» и здесь же «ГРАНИЦА»-«Нет линий»:

Все доли на диаграмме находятся на одном и том же ряду. Поэтому чтобы получить доступ ко второй (невидимой) доли следует воспользоваться легендой диаграммы. Выбираем мышкой только лишь один элемент легенды «Ширина», а затем двойным щелчком по нему вызываем: «Формат элемента легенды»-«ПАРАМЕТРЫ ЭЛЕМЕНТА ЭЛЕГЕНДЫ»-«ЗАЛИВКА»-«Цвет»-Красный и здесь же «ГРАНИЦА»-«Сплошная линия»-«Цвет»-Красный:

Теперь убираем все лишние элементы на диаграмме, нажав на кнопку плюс «+» с правого края и сняв все галочки из выпадающего меню:

Секундная стрелка – ГОТОВА! Далее таким же образом создаем часовую и минутную стрелку выполняя те же самые действия за исключением выбора цвета – черный. Кроме того, для всех троих диаграмм необходимо сделать прозрачный фон. Для этого делаем на каждой из них двойной щелчок по фоновой области диаграммы и выбираем инструмент: «Формат области диаграммы»-«ПАРАМЕТРЫ ДИАГРАММЫ»-«ЗАЛИВКА»-«Нет заливки»:

Следующий шаг, как не сложно догадаться – это наложение стрелок на циферблат аналоговых часов в Excel:

Полезный совет! Для удобной работы с несколькими графическими объектам на рабочем листе Excel, хорошо использовать инструменты для выделения и расположения объектов из дополнительного меню: «ФОРМАТ»-«Упорядочение»-«Область выделения» и здесь же «Выровнять»-«По центру» и «Выровнять»-«По середине».

Создадим фигуру овал для последней детали часов, выбрав инструмент: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Овал»:

Придадим фигуре цвет и 3D дизайн гармоничный для наших аналоговых часов. Для этого сначала выберите инструмент из дополнительного меню: «СРЕДСТВА РИСОВАНИЯ»-«ФОРМАТ»-«Стили фигур»-«Заливка фигуры»-Код цвета RGB: 0; 173; 173, а потом здесь же выберите «Эффекты фигуры»-«Затовка1»:

Выполняем финальные настройки по конструкции аналоговых часов в Excel:

1. Накладываем фигуру на часы.
2. Выделяем все элементы: «СРЕДСТВА РИСОВАНИЯ» или «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Упорядочение»-«Область выделения».
3. Группируем: «ФОРМАТ»-«Упорядочение»-«Группировать».

И наслаждаемся готовым результатом!

Кнопки управления часами

Перед написанием кода макроса добавим на лист еще 2 элемента управления аналоговыми часами: Кнопка 1 – ButtonStart и Кнопка 2 – ButtonStop.

Для этого создадим две овальных фигуры, как и в предыдущий раз через меню: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Скругленный прямоугольник». Но на этот присвоим им оформление дизайна из коллекции предустановленных стилей Excel. Для кнопки ButtonStart выберите: «СРЕДСТВА РИСОВАНИЯ»-«Стили фигур»-«Средний эффект – Зеленый, Акцент 6». А для кнопки ButtonStop «Средний эффект – Оранжевый, Акцент 2»:

Макрос для автоматического перевода стрелок часов в Excel

Конструкция и дизайн аналоговых часов готов, осталось лишь запустить механизм. Если на данном этапе разработки мы будем периодически нажимать клавишу F9 или выбирать инструмент: «ФОРМУЛЫ»-«Вычисления»-«Пересчет» все формулы на листе будут автоматически пересчитываться и обновлять свои возвращаемые значения. Следовательно, будут переводиться стрелки часов. Значит нам нужен макрос, который будет выполнять данную функцию с периодичностью таймера в одну секунду.

Все готово для создания макросов в Excel, выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» или нажмите комбинацию клавиш ALT+F11. В появившемся окне редактора макросов «Microsoft Visual Basic for Applications» создайте модуль выбрав опцию из меню: «»

Двойным щелчком левой кнопкой мышки перейдите в модуль и вставьте в него ниже приведенный код сразу для двух макросов Start_Clock и Stop_Clock:

Sub Start_Clock()
ActiveSheet.Shapes( "ButtonStart" ).Shadow. Type = msoShadow30
ActiveSheet.Shapes( "ButtonStop" ).Shadow. Type = msoShadow21
Dim sh As Worksheet
Set sh = ActiveSheet

X:
VBA. DoEvents
If sh.Range( "N1" ).Value = "Stop" Then Exit Sub
Application.Calculate
GoTo X

Dim sh As Worksheet
Set sh = ActiveSheet

sh.Range( "N1" ).Value = "Stop"
ActiveSheet.Shapes( "ButtonStart" ).Shadow. Type = msoShadow21
ActiveSheet.Shapes( "ButtonStop" ).Shadow. Type = msoShadow30

Осталось подключить макросы к соответственным нашим кнопкам для управления часами. Делаем щелчок правой кнопкой мышки по кнопке Start и из появившегося контекстного меню выбираем опцию «Назначить макрос». В появишвемя окне «Назначить макрос объекту» выбираем соответсвеное имя макроса «Start_Clock» из списка и нажимаем на кнопку ОК:

Такие же действия выполняем для кнопки Stop, только для нее следует из списка макросов выбрать имя Stop_Clock – соответственно.

Все ГОТОВО для запуска часов! Теперь при нажатии на кнопку Start будет выполняться код макроса Start_Clock, а при клике на кнопку Stop выполнится макрос Stop_Clock.

По такому же принципу можно создавать аналоговые часы самого разного дизайна. В программе Excel можно использовать много разных инструментов для оформления стиля диаграмм:

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

Биржевые часы в Excel с разницей во времени

Как сделать биржевые часы для текущего времени фондовых бирж на разных континентах мира в принципе уже понятно. Нужно лишь посчитать разницу во времени в Excel. Если надо сделать набор часов для крупнейших мировых бирж, которые находятся в городах на разных часовых поясах: NEW YORK, LONDON, TOKYO, SYDNEY, MOSCOW. В формулу часовой стрелки следует добавить операции для вычисления разницы во времени. Например, у Вас на компьютере отображается московское время тогда разница во времени города Москва и Нью-Йорк равна -7 часов. Формула вычисления количества часов для часовой стрелки приобретает следующий вид:

12;ЧАС(ТДАТА()-(7/24))-12;ЧАС(ТДАТА()-(7/24)))+(B12/60)+(B7/3600)’ >

Как видно к функции возвращающей текущую дату и время =ТДАТА() добавилась операция вычитания -(7/24). Чтобы вычитать от значения даты и времени 7 часов нам нужно случала преобразовать данное число в формат времени разделив его на количество часов в сутках 24. О правилах математических операций со временем в Excel читайте: Как сложить время и число часов или минут в Excel

Диаграмма в виде спидометра в Excel — комбинация круговой и кольцевой диаграммы, отображающая уровень показателя согласно заданной шкале с зонами.

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

Однако почему диаграмму удобно представлять именно в виде спидометра?
Такой вид интуитивно удобен за счет схожести с автомобильным спидометром.
Представьте, ведь при управлении автомобилем (компанией) взгляд на спидометр (диаграмму) позволяет быстро оценить текущую ситуацию на дороге (в бизнесе) и в случае необходимости принять оперативные решения.

В стандартном виде график спидометр выглядит следующим образом:

Подготовка исходных данных

В первую очередь подготовим исходные данные для построения графика в виде спидометра: ряд для отображения шкалы (диапазон C4:C7) и ряд для отображения стрелки (диапазон F4:F7):

Обычно шкала делится на 3 основные зоны: красная, желтая и зеленая, при этом при попадании показателя в красную зону результат будет интерпретироваться как плохой, в желтую — хороший, в зеленую — отличный.
В зависимости от поставленной задачи можно варьировать количество зон оценки, их размер и т.д.
В данном примере зададим прозрачную часть равной 180 градусам (половине круга), остальные зоны рассчитаем из пропорции 70%/15%/15%, толщину стрелки примем равной 4 градусам.

Построение графика

Для построения графика в виде спидометра, выделяем диапазон данных C4:C7 и на панели вкладок нажимаем Вставка -> Диаграмма -> Круговая:

В итоге мы получаем круговую диаграмму со шкалой:

Далее добавим на диаграмму стрелку. Щелкаем правой кнопкой мыши по графику и нажимаем Выбрать данные, после чего добавляем диапазон F4:F7 в качестве нового ряда.
Внешний вид диаграммы не поменялся, за счет того, что шкала закрывает собой стрелку, поэтому перенесем ее на вспомогательную ось (щелкаем правой кнопкой по графику, далее выбираем Формат ряда данных -> Параметры ряда -> По вспомогательной оси), а затем меняем тип диаграммы для стрелки на кольцевую (щелкаем правой кнопкой по ряду со стрелкой и нажимаем Изменить тип диаграммы для ряда):

В результате получаем диаграмму, на которой на внешней части располагается шкала с зонами, на внутренней — стрелка:

Удаляем заливку с ненужных частей диаграммы, оставляем только шкалу и стрелку:

Для удобства чтения и восприятия графика повернем его на 90 градусов, в параметрах рядов (и для шкалы, и для стрелки) ставим угол 90 градусов в поле Угол поворота первого сектора:

Также выведем стрелку на диаграмме на передний план, для этого в параметрах ряда со стрелкой устанавливаем ряд на основную ось:

Далее настраиваем диаграмму на свой вкус — выбираем цвета для шкалы и стрелки, определяем оптимальную толщину стрелки, добавляем/удаляем легенду, подписи данных и прочее:

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