Это глава из книги Билла Джелена Гуру 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. Формулы дают полезную, но визуально бедную информацию
Скачать заметку в формате Word или pdf, примеры в формате Excel
Постройте диаграмму SmartArt. Для этого перейдите на вкладку ВСТАВКА, в область Иллюстрации, и кликните на кнопке SmartArt. Откроется окно Выбор графического элемента SmartArt. В левой части выберите Список и кликните на пиктограмме Вертикальный список (рис. 2).
Рис. 2. Выбор вертикального списка
Оставьте в диаграмме SmartArt три элемента списка. Введите с клавиатуры текст в левом окне (рис. 3). Выберите цветовую гамму (меню РАБОТА С РИСУНКАМИ SMARTART —> КОНСТРУКТОР —> Изменить цвета). Установите уменьшающийся размер блоков.
Рис. 3. Текст всё еще является статическим
Кликните внутри объекта SmartArt. Нажмите Ctrl+А для выделения всех фигур диаграммы SmartArt. Нажмите Ctrl+C для копирования в буфер. Кликните за пределами объектов SmartArt, нажмите Ctrl+V, чтобы вставить фигуры на листе. Удалите исходную диаграмму SmartArt.
Кликните первую фигуру на листе. Выделите весь текст в этой форме. Щелкните в строке формул, и введите =Н8, нажмите Enter. Текст в выделенной фигуре изменится, чтобы отразить результат формулы в Н8. Повторите эти шаги для второй и третьей фигуры, введя =Н9 и =Н10, соответственно.
Теперь у вас есть нечто, что выглядит как диаграмма SmartArt, но текст поставляется динамически на основании данных из ячеек Н8:Н10 (рис. 4).
Рис. 4. Теперь текст на диаграмме — результат расчета; текст будет меняться с изменением данных
По мере изменения данных в базе продаж (область А:С), будут обновляться и итоговые результаты, а вместе с ними и визуальное их представление. Пока Ted взял отгул, ? Mary увеличила свою выручку на $95, и вырвалась на первое место (рис. 5). [1]
Рис. 5. Excel динамически изменил текст в формах
Резюме: хотя добавление формул в рисунки SmartArt невозможно, вы можете использовать графические элементы SmartArt для создания диаграммы, преобразовать ее в фигуру и уже ей приписать динамические формулы.
[1] У меня визуальные данные автоматически не обновились, и пришлось повторно ввести формулы =Н8, =Н9, =Н10. — Прим. Багузина.
