Перейти к содержимому

Добавление формул в рисунки SmartArt

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: для поклонников Excel одним из разочарований является статичность текста диаграмм SmartArt. Штатными средствами нельзя сделать так, чтобы содержание текста диаграммы SmartArt определялось динамически на основе формул.

Решение: вы можете пойти в обход; используйте инструмент SmartArt для построения диаграммы, преобразуйте диаграмму в фигуру, и лишь после этого примените формулы к тексту фигур.

На рис. 1 запрос к базе данных продаж, расположенной в столбцах А:С, возвращает в диапазон G4:G6 суммарные продажи по каждому агенту. Для этого используется функция СУММЕСЛИ. Например, в ячейке G4 формула: =СУММЕСЛИ($A$4:$A$204;F4;$C$4). Любопытно, что в качестве третьего аргумента – диапазона суммирования – используется указание на одну ячейку $C$4, а не на диапазон $C$4: $С$204. В ячейках Е4:Е6 используется функция РАНГ. Например, в ячейке Е4 формула: =РАНГ(G4;$G$4:$G$6). В диапазоне Е8:G10 те же результаты представлены по рангу. Для этого использованы формулы на основе функции ВПР (подробнее см. Использование ВПР для решения общих проблем, параграф Сортировка с помощью формулы). Эти результаты являются полезными, но им не хватает визуального эффекта.

Рис. 1. Формулы дают полезную, но визуально бедную информацию

Рис. 1. Формулы дают полезную, но визуально бедную информацию

Скачать заметку в формате Word или pdf, примеры в формате Excel

Постройте диаграмму SmartArt. Для этого перейдите на вкладку ВСТАВКА, в область Иллюстрации, и кликните на кнопке SmartArt. Откроется окно Выбор графического элемента SmartArt. В левой части выберите Список и кликните на пиктограмме Вертикальный список (рис. 2).

Рис. 2. Выбор вертикального списка

Рис. 2. Выбор вертикального списка

Оставьте в диаграмме SmartArt три элемента списка. Введите с клавиатуры текст в левом окне (рис. 3). Выберите цветовую гамму (меню РАБОТА С РИСУНКАМИ SMARTART –> КОНСТРУКТОР –> Изменить цвета). Установите уменьшающийся размер блоков.

Рис. 3. Текст всё еще является статическим

Рис. 3. Текст всё еще является статическим

Кликните внутри объекта SmartArt. Нажмите Ctrl+А для выделения всех фигур диаграммы SmartArt. Нажмите Ctrl+C для копирования в буфер. Кликните за пределами объектов SmartArt, нажмите Ctrl+V, чтобы вставить фигуры на листе. Удалите исходную диаграмму SmartArt.

Кликните первую фигуру на листе. Выделите весь текст в этой форме. Щелкните в строке формул, и введите =Н8, нажмите Enter. Текст в выделенной фигуре изменится, чтобы отразить результат формулы в Н8. Повторите эти шаги для второй и третьей фигуры, введя =Н9 и =Н10, соответственно.

Теперь у вас есть нечто, что выглядит как диаграмма SmartArt, но текст поставляется динамически на основании данных из ячеек Н8:Н10 (рис. 4).

Рис. 4. Теперь текст на диаграмме – результат расчета; текст будет меняться с изменением данных

Рис. 4. Теперь текст на диаграмме – результат расчета; текст будет меняться с изменением данных

По мере изменения данных в базе продаж (область А:С), будут обновляться и итоговые результаты, а вместе с ними и визуальное их представление. Пока Ted взял отгул, 🙂 Mary увеличила свою выручку на $95, и вырвалась на первое место (рис. 5). [1]

Рис. 5. Excel динамически изменил текст в формах

Рис. 5. Excel динамически изменил текст в формах

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

[1] У меня визуальные данные автоматически не обновились, и пришлось повторно ввести формулы =Н8, =Н9, =Н10. – Прим. Багузина.

6 комментариев для “Добавление формул в рисунки SmartArt”

  1. Дай Бог тебе здоровья, хороший человек!
    Получилось! Я так рада!
    Ваша статья единственная, которая «рассказала» как это сделать. Благодарю еще раз.

  2. Добрый день!
    Благодарю Вас за такое лаконичное изложение мысли, которая приносит огромную пользу!
    Вы очень помогли: раскрыли суть такого замечательного инструмента! Очень помогает в работе для отображения данных!
    Вы — Молодец! Спасибо огромное Вам!
    И сайт Ваш суперский! Многое видел, но не везде так! Спасибо Вам за все Ваши труды! У Вас я узнал про интересную функцию «Ч»))) и многое другое!

  3. Спасибо большое! я хотел бы добавить, после того как вы вставите фигуру на лист (пока все выделены) советую группировать все выделенные фигуры (ПКМ — Группировать). Легко будет перетаскивать все фигуры вместе.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *