Ref-sgain.ru

Программы и сервисы
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как создать индикатор выполнения в Excel?

Как в Excel сделать так, чтобы оформление ячейки автоматически менялось в зависимости от ее содержимого

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

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

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

Рис. 1. Ячейки с условным форматированием в Excel

Рисунок 1. Ячейки с условным форматированием в Excel

Чтобы установить условное форматирование для диапазона ячеек, сначала выделите его. Затем нажмите кнопку Условное форматирование на вкладке Главная панели инструментов. Откроется палитра условного форматирования. Затем выберите один из предложенных вариантов цвета и выберите Гистограммы (см. рис.) 2). Содержимое ячеек будет визуализировано в виде цветных полос в ячейках.

Рис. 2. Выбор условного форматирования

Рисунок 2: Выбор формата условного обозначения

Попробуйте выбрать Цветные стрелки, а затем Наборы значков. В ячейках появятся стрелки, указывающие, больше или меньше среднее значение, содержащееся в ячейке. Условия также можно комбинировать (см. рис.) 3). Когда вы решаете, какие материалы купить для реставрации мебели, условное форматирование поможет вам провести сравнительный анализ цен на материалы, чтобы выбрать подходящие материалы.

Читайте так же:
Как сделать так, чтобы ячейка мигала красным в Excel?

Рис. 3. Совместное использование нескольких вариантов условного форматирования

Рисунок 3. Комбинированное использование нескольких опций условного форматирования

Вы можете задать любое условие форматирования с помощью кнопки Условное форматирование на вкладке Главная панели инструментов. Нажмите кнопку Создать правило после нажатия кнопки Условное форматирование, чтобы задать цвет для всех ячеек со значениями более 2000. После нажатия появится диалоговое окно Создать правило форматирования (см. Рисунок 4), в котором сначала можно выбрать тип правила, а затем ввести параметры.

Рис. 4. Диалоговое окно «Создание правила формирования»

Рис. 4. Диалоговое окно «Создание правила формирования

Так, для нашего примера с превышением максимальной границы в 2000 следует вверху окна выбрать тип правила Форматировать все ячейки на основании их значений, в области Минимальное значение выбрать тип Число, а в ставшем доступным поле Значение ввести значение 2000. В поле Цвет области Минимальное значение можно выбрать цвет, которым должны выделяться ячейки со значением, меньшим 2000, а в поле Цвет области Максимальное значение — цвет, которым будут выделяться ячейки с числами, большими 2000.

Рис. 5. Удаление условного форматирования

Рисунок 5 — Устранение условного форматирования

Чтобы удалить условное форматирование с каких-либо ячеек, их следует выделить, на вкладке Главная ленты инструментов нажать кнопку Условное форматирование, выбрать там команду Удалить правила, а затем указать, откуда именно должны быть те удалены, — со всего листа или только с выделенного диапазона ячеек. Можно также выбрать, какое именно условное форматирование должно быть удалено (см. рис. 5).

Как создать индикатор выполнения в Excel?

Часто бывает необходимо подчеркнуть состояние процесса с помощью цветов в отчетах. Метафора светофора очень популярна. Если все хорошо — зеленый, если что-то беспокоит — желтый, если плохо — красный. Таким образом, каждый, кто просматривает ваши отчеты, может сразу же найти проблемные цифры по цвету. Отчеты стали более четкими и интуитивно понятными и экономят время пользователя.

Ограничения условного форматирования

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

Читайте так же:
Как скрыть содержимое строки формул в Excel?

Фигуры

Фигурами в MS Office можно нарисовать всё, что угодно. Серьёзно. Любой сложный рисунок «собирается» из простых элементов. Это вопрос только времени и стараний. В этой статье мы будем управлять вот такими несложными, но достаточно привлекательными светофорами, которые легко делаются из фигур овал (круг — частный случай овала/эллипса) и кольцо .

Мы хотим визуализировать соотношение фактической и плановой стоимости проекта с помощью светофора. Например:

Пример

Скачать

Последовательность шагов

В качестве первого шага давайте подготовим данные, на которых будут основаны наши расчеты. В нашем примере это следующие столбцы: Проект, Бюджет, Факт.

Подготовьте вспомогательную таблицу, на основе которой мы будем присваивать значения статусов. В нашем случае эта таблица находится в листе конфигурации и оформлена как смарт-таблица под названием Scale. Состояние G соответствует зеленому цвету, Y — желтому, а R — красному.

В ячейку E3 листа Статусы введена формула
=ЕСЛИОШИБКА(ВПР((D3-C3)/C3;Шкала;2);»D») .
Как видите, мы находим разницу между фактом и бюджетом и делим её на бюджет. Минимальное значение этого соотношения -1 (минус единица) достигается при нулевых фактических затратах. Этот факт определяет пороговое значение (-1 = -100%) для статуса G в таблице Шкала . Порог начала жёлтого цвета вы определяете сами — у меня он 0%. То есть зелёный цвет должен быть у всего, что в диапазоне от -100% до 0%. Жёлтый — от 0% до 15%. Красный — 15% и выше. Для выбора значения из Шкалы идеально подходит формула ВПР в своей диапазонной версии, которая ищёт диапазон, в который попадает значение ( (D3-C3)/C3 ) в справочнике ( Шкала ), и возвращает из справочника содержимое ячейки на пересечении найденной строки и указанного столбца ( 2 ). Если вычисление функции ВПР (VLOOKUP) оканчивается ошибкой (например, когда Бюджет=0), то формула ЕСЛИОШИБКА (IFERROR) её перехватывает и возвращает в ячейку значение D , что будет означать, что светофор не горит (серый). Формулу из E3 распространяем на E4:E5 .

Читайте так же:
Как создать гистограмму из ячеек "да нет" в Excel?

Формат данных диапазона E3:E5 определен как » ;;;;», чтобы числа не были видны через светофор, который мы размещаем над этими ячейками.

Создадим именованный диапазон rngTrafLight для ячеек E3:E5 .

Сделайте наши светофоры по чертежам. Круги, цвет которых мы хотим изменить, называются figTL1 для E3, figTL2 для E4 и figTL3 для E5. Разместите фигуры в нужном месте.

В редакторе Visual Basic for Application ( Alt + F11 ) вставляем module с любым именем (у меня TL ). Для этого щёлкните правой кнопкой по папке Modules и выберите Insert -> Module . Вставьте в модуль этот код:

Интерфейс Excel. Элементы окна Excel и их назначение

Интерфейс Excel

MS Excel содержит огромное количество элементов: поля, строки, столбцы, командные строки и т.д. Это пугает многих пользователей, когда они запускают его в первый раз. Именно это пугает многих пользователей, когда они впервые начинают работать с Excel для работы с электронными таблицами. Но если вы копнете немного глубже и освоите интерфейс Excel, работа в Excel покажется вам легкой прогулкой по парку.

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

Основные элементы окна Excel

Элементы окна Excel

Вертикальная полоса прокрутки позволяет прокручивать экран в вертикальном направлении.

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

Горизонтальная полоса прокрутки позволяет прокручивать лист по горизонтали.

Заголовки столбцов. В рабочем листе Excel имеется 16 384 столбца. Эти столбцы обозначены буквами от A до XFD в заголовках. Щелчок левой кнопкой мыши по заголовку столбца выделит все ячейки в столбце. Удерживайте левую кнопку мыши при перетаскивании границы столбца, чтобы отрегулировать ширину.

Читайте так же:
Как сложить столбцы слева направо в один столбец в Excel?

Нажав на кнопку «Вставить функцию», вы вызовете диалоговое окно, в котором легко найти нужную функцию и вставить правильную формулу и аргументы.

Чтобы создать новый рабочий лист, просто нажмите кнопку «Новый лист». В строке «Метка листа» метка нового листа появляется после последнего существующего листа.

Кнопка минимизации ленты скрывает ленту, освобождая место на экране. Чтобы лента появилась снова, дважды щелкните левой кнопкой мыши на корешке вкладки (или нажмите ).

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

Кнопки просмотра страниц используются для изменения способа отображения электронной таблицы.

Кнопки управления окном позволяют свернуть текущее окно, увеличить или восстановить его, а также закрыть окно.

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

Номера строк. На этом рабочем листе имеются заголовки от 1 до 1 048 576. Заголовок строки нужно щелкнуть левой кнопкой мыши, чтобы выделить все ячейки в строке. Удерживая нажатой левую кнопку мыши, щелкните левой кнопкой границы между двумя номерами строк. Затем перетащите границу на нужную высоту.

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

Читайте так же:
Как скопировать ячейку выше или ячейку слева с помощью сочетания клавиш в Excel?

Параметры отображения ленты. Нажав на этот элемент управления, вы можете выбрать, как будет отображаться лента. В настоящее время здесь доступны три варианта:

  • Автоматическое скрытие ленты. Канал будет скрыт до тех пор, пока вы не нажмете кнопку в верхней части программы.
  • Показать вкладки. Отображаются только вкладки (без команд). Чтобы просмотреть доступные команды для конкретной вкладки, щелкните заголовок вкладки, чтобы просмотреть доступные команды для конкретной вкладки.
  • Показать вкладки и команды. Вкладки и команды на ленте всегда будут отображаться в приложении.

В поле «Имя» отображается адрес активной ячейки или ссылка на выбранный диапазон ячеек или объект.

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

Строка заголовка. Здесь отображается название программы (в нашем случае Excel) и название текущей рабочей книги. В левой части строки заголовка находится панель инструментов быстрого доступа. С помощью кнопок управления справа можно изменять отображение ленты и окна программы.

Строка состояния. Здесь отображается несколько сообщений (номер страницы, среднее количество чисел в выбранных ячейках, состояние клавиши и ). Настройте строку состояния, отображая только ту информацию, которая вам нужна. Щелкните строку состояния, и вы сможете выбрать тип отображаемой информации.

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

Темный контур выделяет активную в данный момент ячейку в качестве курсора. Так же можно назвать индикатор выделенной ячейки.

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector