Как сделать светофор в Excel
В Excel 2010 появилось несколько интересных новых способов форматирования ячеек и использования цвета и значков для выделения значений. В качестве альтернативы простому затенению целых ячеек можно использовать различные цветные фигуры, стрелки и флажки.
В этом примере индикаторы светофора будут добавлены в ячейки S19:S22 в соответствии с их значением. Каждая ячейка содержит формулу =N19/M19 (с поправкой на каждую строку). Выделите диапазон ячеек и выберите [Главная] Стили | Условное форматирование | Наборы иконок | и выберите набор из трех цветных кругов. Это не будет иметь явного эффекта, и изначально круги не появятся, потому что правила не установлены. Для этого выберите [Главная] Стили | Условное форматирование | Управление правилами |.
Excel предлагает только «Больше чем» (> или >=) для определения полос, поэтому необходимо убедиться, что цветные фигуры расположены в правильном порядке. В этой таблице высокие значения плохие (красные), поэтому нажмите кнопку [Обратный порядок значков], чтобы переключить их.
Должны быть указаны значения, определяющие диапазоны, в которых будет отображаться каждый из цветов. В этом примере зеленый — от 0 до 0,95, желтый – от 0,95 до 0,999, а красный – больше 0,999.
Excel может быть немного капризным при внесении изменений в формулу или форматирование определенных ячеек в пределах диапазона. Может потребоваться очистить форматирование в определенной ячейке, а затем повторно ввести правила условного форматирования только для этой ячейки.
Невозможно установить эти правила для значков на основе формулы, т. е. значок не может быть определен значениями в других ячейках. Однако в диалоговом окне правила условного форматирования есть флажок «Показывать только значок». Это скроет значение в ячейке, содержащей значок.
Другие инструменты условного форматирования
Есть два других варианта этого инструмента в более поздних версиях Excel.
Столбцы данных (столбец C) – в каждой ячейке отображается столбец, показывающий относительные размеры значений.
Цветовые шкалы (столбец D) — закрашивает каждую ячейку в диапазоне в соответствии с их относительным положением между самым высоким и самым низким значением. Можно настроить параметры Excel по умолчанию с помощью параметра «Управление правилами».
Нажмите здесь для получения подробной информации о расчетах, зависящих от цвета ячеек.
Нажмите здесь, чтобы увидеть пример приложения с условным форматированием — планировщик отпусков для офиса.
Вчера я научился новому крутому приему в PowerPoint. Я узнал об объединении фигур.
Как и Excel, PowerPoint позволяет рисовать различные фигуры на слайдах (или листах в Excel). Мы можем рисовать линии, стрелки, прямоугольники, круги и многие другие фигуры.
Но в PowerPoint есть дополнительная функция с фигурами, которой нет в Excel. В PowerPoint у нас есть возможность объединять фигуры для создания новых фигур.
Мы могли бы объединить длинный тонкий прямоугольник со звездой, чтобы создать волшебную палочку. Возможности безграничны!
Это натолкнуло меня на идею причудливой визуальной панели. Итак, в этом посте мы узнаем, как создать динамический светофор, который меняет цвета в зависимости от значения некоторого ключевого показателя эффективности (KPI) на нашей информационной панели.
В отличие от волшебной палочки, наш светофор действительно будет работать! Когда значение нашего KPI изменится, наш светофор тоже изменится, в зависимости от того, в каком диапазоне значений мы его установим.
Видеоруководство
Создание фигуры светофора
Если вы добавите фигуру в PowerPoint или Excel, а затем выберете ее, на ленте появится новая вкладка Формат средств рисования. Но в PowerPoint есть дополнительный параметр «Объединить фигуры», которого нет в Excel.
В Excel есть много скрытых команд, которые не отображаются на вкладках ленты, и их можно использовать, добавив на панель быстрого доступа. Но форм слияния там даже нет.
Поэтому, чтобы создать фигуру светофора, нам нужно зайти в PowerPoint, чтобы использовать параметры объединения фигур.
Объединение фигур в PowerPoint
Сначала нам нужно добавить пару фигур. Сначала добавьте фигуру прямоугольника, таким образом, она будет наложена как нижняя фигура.
Затем добавьте форму круга. На самом деле нет формы круга, только овал, но мы можем использовать его для создания круга.Удерживая нажатой клавишу Shift, рисуйте овал, чтобы зафиксировать пропорции и создать идеальный круг.
Мы можем отрегулировать размер по мере необходимости, чтобы круг хорошо помещался внутри прямоугольника. Затем мы можем скопировать и вставить круг, всего три круга.
В PowerPoint есть встроенный инструмент выравнивания, который поможет выровнять все фигуры, что является еще одним преимуществом выполнения этой части в PowerPoint по сравнению с Excel.
Мы также можем использовать инструменты выравнивания на вкладке Формат инструментов рисования ➜ Выровнять ➜ Выровнять по центру и/или Распределить по вертикали.
После того, как мы все правильно выровняем и отцентрируем, мы готовы объединить фигуры. Мы можем перейти на вкладку Формат инструментов рисования ➜ Объединить фигуры ➜ Объединить. Это, по сути, вырежет круги из прямоугольника и оставит нам одну форму (прямоугольник с тремя отверстиями в нем).
Я не знаю ни одного синего светофора, поэтому мы также изменим цвет с синего по умолчанию.
- Вкладка «Формат» инструментов рисования ➜ «Заливка формы», затем измените цвет на темно-серый.
- Вкладка «Формат» инструментов рисования ➜ Контур формы, затем измените цвет на черный.
Скопируйте и вставьте эту новую фигуру в Excel, и мы закончили с PowerPoint.
Условные правила форматирования для изменения цвета заливки
В этом примере значение нашего ключевого показателя эффективности, которое управляет светофором, будет на Листе 1, а закулисные действия, которые фактически меняют свет, будут на Листе 2.
Это базовая настройка в Sheet2. В ячейках A2 и A3 у нас есть некоторые числовые значения, а в ячейках B2:B4 есть условное форматирование, основанное на этих значениях и нашем KPI.
- Когда наш KPI меньше значения в A2, наш светофор загорается красным цветом.
- Когда наш KPI находится между значениями в A2 и A3, наш светофор загорается желтым цветом.
- Когда наш ключевой показатель эффективности превышает значение в A3, наш светофор загорается зеленым цветом.
Для этого мы настроим условное форматирование в ячейках B2:B4.
Перед настройкой условного форматирования мы можем применить светло-серое форматирование к ячейкам в B2:B4. Это будет цвет, который отображается, когда светофор выключен.
Ячейка B2 будет нашим красным светом. Выберите эту ячейку и перейдите на вкладку «Главная» ➜ «Условное форматирование» ➜ «Новое правило».
Мы будем использовать формулу Тип правила с приведенной выше формулой, а также зададим для формата красный цвет заливки. Здесь Sheet1!$B$2 — это значение нашего KPI.
В ячейке B3 нам нужно аналогичное условное форматирование с приведенной выше формулой и желтым цветом заливки Format.
В ячейке B4 нам нужно наше последнее условное форматирование с приведенной выше формулой и зеленым цветом заливки Format.
Нам также потребуется изменить ширину столбца и высоту строки ячеек B2:B4, чтобы каждая ячейка хорошо помещалась под отверстиями нашего прямоугольника.
Вставить диапазон как связанное изображение
Мы собираемся использовать диапазон ячеек с условным форматированием в B2:B4 в качестве световых сигналов для нашего визуального элемента светофора.
Для этого мы воспользуемся отличной опцией вставки под названием Linked Picture. Это позволяет нам создать изображение ячеек в B2:B4, но это изображение является динамическим и будет меняться при изменении этих ячеек.
Мы можем скопировать диапазон B2:B4 и перейти к Sheet1, где находится наша фигура светофора.
На вкладке «Главная» нажмите маленькую стрелку под командой «Вставить», затем выберите параметр «Связанное изображение».
Упорядочить и сгруппировать фигуру и изображение
Мы можем изменить размер полученного связанного изображения так, чтобы каждая ячейка совпадала с каждым отверстием в нашей фигуре светофора.
Поместите связанное изображение поверх фигуры светофора так, чтобы каждая ячейка закрывала каждое отверстие, но изображение все равно было меньше, чем весь прямоугольник.
Затем мы можем поместить изображение за фигуру, перейдя на вкладку Формат инструментов рисования ➜ Отправить назад ➜ Отправить на задний план.
Как только нас устроит положение фигуры и изображения, мы можем сгруппировать их вместе. Вкладка Формат средств рисования ➜ Группа ➜ Группа. Это позволит нам перемещать и изменять размер светофора как одного объекта.
Теперь у нас есть симпатичный маленький светофор для наших сводок!
Выводы
С небольшой помощью PowerPoint мы можем комбинировать фигуры для создания более сложных фигур. Хотя в Excel нет этих возможностей, мы можем легко скопировать результаты PowerPoint в Excel.
Мы использовали эти новые формы, создающие сверхспособности, чтобы вырезать несколько отверстий в прямоугольнике.В сочетании со связанным изображением с некоторыми условно отформатированными ячейками мы можем создать классный визуальный эффект светофора.
Какие визуальные эффекты вы могли бы придумать? Дайте мне знать в комментариях ниже!
Об авторе
Джон МакДугалл
Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.
Вот один из способов выделить исключительную эффективность в отчетах об управлении Excel. Вам просто нужна диаграмма Excel, ее функция ВЫБОР и связанные изображения.
«Светофоры» — это обычная функция, предлагаемая программным обеспечением, предназначенным для управленческой отчетности и анализа.
Светофоры дают как минимум два преимущества. Во-первых, они предупреждают читателей об исключительных результатах, которые в противном случае читатели могли бы пропустить. Во-вторых, они обеспечивают визуальное замораживание, которое может сделать отчеты более интересными.
Приведенные ниже три примера иллюстрируют простую форму светофора.
На всех трех изображениях показана одна и та же ячейка на листе Excel. Когда продажи растут, Excel отображает зеленое изображение. Когда продажи падают, Excel отображает красное изображение. В противном случае Excel отображает желтое изображение.
Эти изображения переключаются автоматически. Никаких макросов не нужно. А простые формулы определяют, какое изображение используется.
В этой статье объясняется, как настроить светофор в собственных отчетах Excel.
Знакомство с объектом камеры
Excel представил объект Camera не менее 20 лет назад. Но несколько поколений назад Microsoft изменила свое название на Linked Picture. Несмотря на это, я обычно называю его объектом «Камера».
(Аналогично Microsoft изменила название Power Query в Excel 2016 на Data, Get & Transform Data. Но большинство людей по-прежнему называют его Power Query или сокращенно PQ.)
Объект камеры Excel часто является ключом к созданию отчетов о светофоре. Объект Camera возвращает изображение заданного диапазона, и формула может указать этот диапазон. Это простое описание дает подсказку о том, как я создал вышеприведенные светофоры.
Сначала я настроил три разные ячейки с цветными фоновыми объектами, показанными выше. Затем я написал формулы, которые сообщали объекту Camera, изображение какой из этих трех ячеек следует вернуть, на основе значения в назначенной ячейке.
Давайте выполним эти шаги по одному.
Поэкспериментируйте с объектом камеры
Первый шаг — поместить значок объекта «Камера» в одну из строк формул в Excel.
Для этого сначала выберите Инструменты, Настройка в Excel. На вкладке «Команды» выберите категорию «Инструменты». В списке команд в правой части диалогового окна вы найдете значок камеры чуть ниже середины списка. Нажмите и перетащите значок на удобную панель инструментов в рабочей области Excel.
В новом листе введите текст в ячейку C2. Добавьте цвет и другое форматирование, как показано ниже. Затем, выбрав ячейку C2, нажмите Ctrl + C, чтобы скопировать эту ячейку.
Выделите ячейку C4 и в группе Главная, Буфер обмена выберите Вставить, Связанное изображение.
При этом Excel возвращает связанный объект изображения, как показано здесь. Вы можете переместить этот объект в любое место на листе.
Если вы измените форматирование или текст в ячейке C2, вы увидите, что связанное изображение немедленно обновится.
Теперь, выбрав связанное изображение, посмотрите на формулу в строке формул. Это диапазон, изображение которого возвращает связанное изображение. Этот диапазон может состоять из одной или нескольких смежных ячеек, и, как вы увидите, вы можете изменить его вручную.
Вы можете вырезать или скопировать и вставить связанный объект изображения на любой другой рабочий лист. Однако, когда вы это сделаете, вы заметите одну небольшую проблему. Когда вы вставляете ссылку на обычную ячейку на какой-либо другой лист, Excel автоматически изменяет ссылку на форму внешней ссылки. Для иллюстрации предположим, что у вас есть эта формула на Листе 1:
Если бы вы вырезали и вставили эту ячейку на Лист2, Excel вставил бы следующую формулу:
То есть Excel автоматически корректирует ссылку, чтобы она продолжала указывать на ячейку, которую вы изначально указали.
К сожалению, когда вы вырезаете и вставляете связанное изображение между листами или книгами, ссылка на ячейку изменяется не так, как вы ожидаете. Но это не проблема. После того, как вы вставите связанное изображение в новое место, просто выберите формулу объекта в строке формул, а затем выберите диапазон, на который вы хотите ссылаться связанное изображение. Это изменит формулу объекта с чего-то вроде…
Небольшой экскурс в диапазоны Excel
До сих пор мы использовали объект Camera только с простыми ссылками, например =$B$2. Однако для светофора нам нужно использовать формулу, которая возвращает разные ссылки при разных условиях. То есть нам нужно, чтобы у объекта Camera была ссылка примерно такого вида:
Здесь, если ячейка A1 содержит значение 1, мы хотим, чтобы объект Camera возвращал изображение ячейки B2; в противном случае мы хотим, чтобы он возвращал изображение ячейки B3.
К сожалению, если вы попытаетесь ввести подобную формулу для объекта «Камера», вы получите сообщение об ошибке Excel. Но это не проблема, потому что у нас есть простой обходной путь: имена диапазонов.
Большинство имен диапазонов Excel в большинстве электронных таблиц Excel очень простые. Имена определяются с помощью простых ссылок, например…
Однако имена Excel можно задавать с помощью формул. Например, вы можете определить имя Test как:
Попробуйте. Выберите «Вставить», «Имя», «Определить». Введите Test в качестве имени. Затем в поле Ссылается введите приведенную выше формулу. (Excel добавит имя активного листа к каждой ссылке на ячейку, но все равно примет формулу.)
Чтобы проверить, работает ли названная формула должным образом, введите значение 1 в ячейку A1. Затем перейдите к диапазону, определенному Test. Для этого нажмите функциональную клавишу F5, которая запускает диалоговое окно «Перейти». Введите «Тест» (без кавычек) в поле «Ссылка». Затем выберите ОК. Excel должен выбрать ячейку B2. Затем введите любое другое значение в ячейку A1. На этот раз, когда вы перейдете к тесту, Excel выберет ячейку B3.
(Кстати, при нажатии функциональной клавиши F5 Excel не будет отображать имена, такие как Test, которые были определены с помощью формул. Это не проблема. Просто введите имя в поле «Ссылка».)
Мы отклонились от имен диапазонов, потому что объект Camera может использовать имена динамических диапазонов, имена, которые используют формулы для возврата ссылок. Для иллюстрации создайте объект Camera, который ссылается на любой диапазон, скажем, на ячейку C5. Затем, выбрав объект «Камера», измените его формулу в строке формул с =$C$5 на =Test.
Если вы определили Test, как показано выше, объект Camera должен возвращать изображение ячейки B2, когда ячейка A1 содержит значение 1. В противном случае камера должна возвращать изображение ячейки B3.
Теперь, имея в своем наборе инструментов имена объекта «Камера» и динамического диапазона, давайте создадим несколько светофоров.
Настройте формулы светофора
На этом рисунке показано простое отображение светофора.
Ячейка B2 получила название Score. Обычно он содержит формулу, которая просматривает данные, которые мы отображаем, а затем оценивает результаты. Оценка 1 возвращает зеленый цвет вокруг диаграммы; 2 возвращает желтый цвет; а 3 возвращает красный цвет.
Формула оценки может быть любой. Предположим, например, что ячейка с именем Рост содержит процент роста за последний квартал по сравнению с предыдущим кварталом.
Следующая формула возвращает 1, если продажи выросли более чем на 2 %, 3, если продажи упали более чем на 2 %, и 2 в противном случае.
Как условно форматировать красный янтарный зеленый на основе даты в Excel?
Если у вас есть список дат на листе, вы хотели бы выделить дату красным, желтым и зеленым цветами с условным форматированием. Например, если дата прошла, то показать красный; если будущие даты в течение 3 месяцев с настоящего момента окрашены в янтарный цвет; и если будущая дата наступит более чем через 3 месяца, зеленый цвет отображается, как показано на следующем снимке экрана. Как бы вы могли решить эту задачу в Excel как можно быстрее?
Условное форматирование красный янтарный зеленый на основе даты в Excel
Для условного форматирования списка дат красным, желтым и зеленым цветами, как в системе светофора, выполните следующие действия:
1. Выберите столбец даты и нажмите «Главная» > «Условное форматирование» > «Новое правило», см. снимок экрана:
2. В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки следует форматировать» в поле со списком «Выбор типа правила», а затем введите следующую формулу: = A2 в текстовое поле «Значения формата», где эта формула является истинной, см. снимок экрана :
3. Затем нажмите кнопку «Формат», чтобы перейти в диалоговое окно «Формат ячеек», на вкладке «Заливка» выберите красный цвет, см. снимок экрана:
4. Затем нажмите кнопки «ОК» > «ОК», чтобы закрыть диалоговые окна, и теперь прошлые даты в столбце выделены красным цветом, см. снимок экрана:
5. Чтобы выделить будущие даты в течение 3 месяцев, перейдите в диалоговое окно «Новое правило форматирования» и введите следующую формулу: =И(A2>=сегодня(),A2 в значения формата, где эта формула является истинным текстовым полем, см. снимок экрана) :
6. Затем нажмите «Формат» и выберите желтый цвет в диалоговом окне «Формат ячеек», нажмите «ОК» > «ОК», и даты в течение 3 месяцев будут выделены, как показано ниже:
7. Чтобы выделить дату более чем через 3 месяца, вам просто нужно применить эту формулу: =A2>ДАТА(СЕГОДНЯ(),3) в диалоговом окне «Новое правило форматирования», см. снимок экрана:
8. И выберите зеленый цвет для условного форматирования даты более 3 месяцев в диалоговом окне «Формат ячеек», и вы получите результат, как показано на следующем снимке экрана:
Читайте также:
- Программа обучения компьютерных курсов для начинающих
- Фотошоп вставить бесплатную фотографию
- Как изменить дюймы на пиксели в фотошопе
- Установщик запускает службы Windows 7. Службы зависают
- Как перевернуть таблицу в Word
КАК СДЕЛАТЬ ВИЗИТКУ В ЭКСЕЛЕ ПОШАГОВАЯ ИНСТРУКЦИЯ
Для создания визитки в Excel можно использовать шаблон или создать дизайн вручную. Вот пошаговая инструкция:
Шаг 1: Откройте новую книгу Excel.
Шаг 2: Создайте заголовки для своей визитки, такие как «Имя», «Должность», «Контактные данные» и т.д.
Шаг 3: Заполните информацию в соответствующих ячейках под заголовками.
Шаг 4: Настройте форматирование для создания привлекательного внешнего вида визитки. Вы можете изменить цвета, шрифты, добавить изображения или логотип компании.
Шаг 5: Разместите информацию по своему усмотрению, используя ячейки и их объединение.
Шаг 6: Проверьте, чтобы текст и дизайн были читабельными и привлекательными.
Шаг 7: Сохраните визитку в нужном формате, например, в формате Excel или PDF.
Теперь у вас есть визитка, созданная в Excel, которую можно распечатать или использовать в цифровом формате для представления своей компании или личной информации.
Как создать визитку в Ворде
Как вести учет в Excel / Учет товара в Эксель
MS Excel Удобный вариант создания визитных карточек, и др.
КАК СДЕЛАТЬ КРУТУЮ ВИЗИТКУ ЗА 4 МИНУТЫ? / создание визитки и подготовка к печати
+/-10 минут. Чем отличается машинное обучение от искусственного интеллекта?
Как сделать визитку в фотошоп новичкам и подготовить файл для печати. Пошаговая инструкция.
MS Excel Удобный вариант создания визитных карточек, и др rev 01
Как вести учет товаров и материалов на складе или магазине. Складской учет в Гугл таблицах
Создаем визитную карточку
Михаил Захаров
Я являюсь сертифицированным специалистом по Excel с многолетним опытом работы, помогающим пользователям улучшать и оптимизировать их работу с данными. В качестве консультанта, я провел сотни часов, обучая людей использованию сложных функций и возможностей Excel, помогая им стать более продуктивными и эффективными. Моя страсть к обучению и стремление помогать другим достигать профессиональных высот вдохновляют меня продолжать делиться знаниями и опытом через обучающие курсы и материалы.
Вам также может понравиться:
Светофор в эксель как сделать
Часто такое бывает, что сталкиваешься с большим количеством данных, которые нужно в сжатые сроки сравнить с плановыми. Допустим, у каждого вашего менеджера выручка должна составлять около 100 000 рублей. И Вы начинаете каждый показатель сравнивать в ручную, весь этот трудоемкий процесс отнимает кучу времени и сил. С новой версией Excel 2010 можно проблему решить при помощи нового встроенного компонента. Итак, давайте избавимся от этой трудоемкой работы! Вначале выделяем нашу область данных. Далее переходим во вкладку «Вставка», затем ищем «Условное форматирование» и «Набор значков»; в выпадающем меню определяемся с шаблоном, с которым в дальнейшем будем работать. Эксперты рекомендуют выбирать шаблон с названием «Светофор»: дело в том, что с ним очень удобно работать. После того, как шаблон выбран, на экране монитора появится меню с названием «Создание правил форматирования», тут нужно произвести следующие манипуляции: необходимо внести данные напротив значков. Если план сотрудника превышает указанный или недостает до него, то предлагается следующая оценка работника: отличная, удовлетворительная, неудовлетворительная. Данные вводятся в области «Значение» а опции параметров «Тип» нужно отредактировать и поменять с процентов на числа.
В данном уроке мы назначали следующие данные: 100-90 тысяч. Третьим шагом мы выставляем автоматически, например меньше «удовлетворительного» и жмем ОК.
В ячейках, где рассоложены данные, должны сразу появиться цветные кружочки (зеленый, красный, желтый). С помощью этой схемы мы сможем с Вами определить оценку о проделанной работе менеджера за определенное время. Также эта полезная функция дает нам выяснить, кто из работников достигает положительных результатов.
Дополнительно к этой функции разработчики добавили условное форматирование данных. Теперь можно создавать инфографику в виде цветовых шкал или гистограмм. Давайте попробуем разобраться с этой новой фишкой, которая очень будет нам полезна в работе. Заходим в выпадающие меню «список шаблонов», и, если мы наведем курсор мышки на любой понравившейся нам шаблон, то произойдет превью нашей работы, и мы получаем следующий результат. Мы видим, что все ячейки окрашены столбцами различной длины. И наш результат отображается в виде графика со значениями, которые находятся в ячейках. Если ваши данные Вы будете вносить со знаком минус, то Ваш график будет смещаться в совершенно противоположный край документа, что будет указывать на отрицательные значения ваших данных.
Условное форматирование – удобный инструмент для анализа данных и наглядного представления результатов. Умение им пользоваться сэкономит массу времени и сил. Достаточно бегло взглянуть на документ – нужная информация получена.
Как сделать условное форматирование в Excel
Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».
При нажатии на стрелочку справа открывается меню для условий форматирования.
Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».
Введем в диапазон А1:А11 ряд чисел:
Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».
Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:
Выходим из меню нажатием кнопки ОК.
Условное форматирование по значению другой ячейки
Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.
Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» — «Меньше»).
В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.
Результат форматирования сразу виден на листе Excel.
Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.
Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.
Выделим исходный диапазон (А1:А11). Нажмем «УФ» — «Правила выделения ячеек» — «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.
Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.
Внимание! При использовании относительных ссылок нужно следить, какая ячейка была активна в момент вызова инструмента «Условного формата». Так как именно к активной ячейке «привязывается» ссылка в условии.
В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.
Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.
Проверить правильность заданного условия можно следующим образом:
- Выделите первую ячейку диапазона с условным форматированим.
- Откройте меню инструмента, нажмите «Управление правилами».
В открывшемся окне видно, какое правило и к какому диапазону применяется.
Условное форматирование – несколько условий
Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.
- 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» — «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
- 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».
Заполняем параметры форматирования по первому условию:
Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.
Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».
То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).
Условное форматирование даты в Excel
Выделяем диапазон с датами.
Применим к нему «УФ» — «Дата».
В открывшемся окне появляется перечень доступных условий (правил):
Выбираем нужное (например, за последние 7 дней) и жмем ОК.
Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).
Условное форматирование в Excel с использованием формул
Если стандартных правил недостаточно, пользователь может применить формулу. Практически любую: возможности данного инструмента безграничны. Рассмотрим простой вариант.
Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.
Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:
Для закрытия окна и отображения результата – ОК.
Условное форматирование строки по значению ячейки
Задача: выделить цветом строку, содержащую ячейку с определенным значением.
Таблица для примера:
Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).
Выделяем диапазон со значениями таблицы. Нажимаем «УФ» — «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.
Порядок заполнения условий для форматирования «завершенных проектов»:
Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).
Аналогично задаем правила форматирования для незавершенных проектов.
В «Диспетчере» условия выглядят так:
Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».
«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.
Информация воспринимается легче, если представлена наглядно. Один из способов презентации отчетов, планов, показателей и другого вида делового материала – графики и диаграммы. В аналитике это незаменимые инструменты.
Построить график в Excel по данным таблицы можно несколькими способами. Каждый из них обладает своими преимуществами и недостатками для конкретной ситуации. Рассмотрим все по порядку.
Простейший график изменений
График нужен тогда, когда необходимо показать изменения данных. Начнем с простейшей диаграммы для демонстрации событий в разные промежутки времени.
Допустим, у нас есть данные по чистой прибыли предприятия за 5 лет:
| Год | Чистая прибыль* |
| 2010 | 13742 |
| 2011 | 11786 |
| 2012 | 6045 |
| 2013 | 7234 |
| 2014 | 15605 |
* Цифры условные, для учебных целей.
Заходим во вкладку «Вставка». Предлагается несколько типов диаграмм:
Выбираем «График». Во всплывающем окне – его вид. Когда наводишь курсор на тот или иной тип диаграммы, показывается подсказка: где лучше использовать этот график, для каких данных.
Выбрали – скопировали таблицу с данными – вставили в область диаграммы. Получается вот такой вариант:
Прямая горизонтальная (синяя) не нужна. Просто выделяем ее и удаляем. Так как у нас одна кривая – легенду (справа от графика) тоже убираем. Чтобы уточнить информацию, подписываем маркеры. На вкладке «Подписи данных» определяем местоположение цифр. В примере – справа.
Улучшим изображение – подпишем оси. «Макет» – «Название осей» – «Название основной горизонтальной (вертикальной) оси»:
Заголовок можно убрать, переместить в область графика, над ним. Изменить стиль, сделать заливку и т.д. Все манипуляции – на вкладке «Название диаграммы».
Вместо порядкового номера отчетного года нам нужен именно год. Выделяем значения горизонтальной оси. Правой кнопкой мыши – «Выбрать данные» — «Изменить подписи горизонтальной оси». В открывшейся вкладке выбрать диапазон. В таблице с данными – первый столбец. Как показано ниже на рисунке:
Можем оставить график в таком виде. А можем сделать заливку, поменять шрифт, переместить диаграмму на другой лист («Конструктор» — «Переместить диаграмму»).
График с двумя и более кривыми
Допустим, нам нужно показать не только чистую прибыль, но и стоимость активов. Данных стало больше:
Но принцип построения остался прежним. Только теперь есть смысл оставить легенду. Так как у нас 2 кривые.
Добавление второй оси
Как добавить вторую (дополнительную) ось? Когда единицы измерения одинаковы, пользуемся предложенной выше инструкцией. Если же нужно показать данные разных типов, понадобится вспомогательная ось.
Сначала строим график так, будто у нас одинаковые единицы измерения.
Выделяем ось, для которой хотим добавить вспомогательную. Правая кнопка мыши – «Формат ряда данных» – «Параметры ряда» — «По вспомогательной оси».
Нажимаем «Закрыть» — на графике появилась вторая ось, которая «подстроилась» под данные кривой.
Это один из способов. Есть и другой – изменение типа диаграммы.
Щелкаем правой кнопкой мыши по линии, для которой нужна дополнительная ось. Выбираем «Изменить тип диаграммы для ряда».
Определяемся с видом для второго ряда данных. В примере – линейчатая диаграмма.
Всего несколько нажатий – дополнительная ось для другого типа измерений готова.
Строим график функций в Excel
Вся работа состоит из двух этапов:
- Создание таблицы с данными.
- Построение графика.
Пример: y=x(√x – 2). Шаг – 0,3.
Составляем таблицу. Первый столбец – значения Х. Используем формулы. Значение первой ячейки – 1. Второй: = (имя первой ячейки) + 0,3. Выделяем правый нижний угол ячейки с формулой – тянем вниз столько, сколько нужно.
В столбце У прописываем формулу для расчета функции. В нашем примере: =A2*(КОРЕНЬ(A2)-2). Нажимаем «Ввод». Excel посчитал значение. «Размножаем» формулу по всему столбцу (потянув за правый нижний угол ячейки). Таблица с данными готова.
Переходим на новый лист (можно остаться и на этом – поставить курсор в свободную ячейку). «Вставка» — «Диаграмма» — «Точечная». Выбираем понравившийся тип. Щелкаем по области диаграммы правой кнопкой мыши – «Выбрать данные».
Выделяем значения Х (первый столбец). И нажимаем «Добавить». Открывается окно «Изменение ряда». Задаем имя ряда – функция. Значения Х – первый столбец таблицы с данными. Значения У – второй.
Жмем ОК и любуемся результатом.
Наложение и комбинирование графиков
Построить два графика в Excel не представляет никакой сложности. Совместим на одном поле два графика функций в Excel. Добавим к предыдущей Z=X(√x – 3). Таблица с данными:
Выделяем данные и вставляем в поле диаграммы. Если что-то не так (не те названия рядов, неправильно отразились цифры на оси), редактируем через вкладку «Выбрать данные».
А вот наши 2 графика функций в одном поле.
Графики зависимости
Данные одного столбца (строки) зависят от данных другого столбца (строки).
Построить график зависимости одного столбца от другого в Excel можно так:
Условия: А = f (E); В = f (E); С = f (E); D = f (E).
Выбираем тип диаграммы. Точечная. С гладкими кривыми и маркерами.
Выбор данных – «Добавить». Имя ряда – А. Значения Х – значения А. Значения У – значения Е. Снова «Добавить». Имя ряда – В. Значения Х – данные в столбце В. Значения У – данные в столбце Е. И по такому принципу всю таблицу.
Готовые примеры графиков и диаграмм в Excel скачать:
Как сделать еженедельный график в Excel вместе с ежедневным.
Пример создания динамического синхронного еженедельного графика вместе с ежедневным. Синхронное отображение двух таймфреймов на одном графике.
Точно так же можно строить кольцевые и линейчатые диаграммы, гистограммы, пузырьковые, биржевые и т.д. Возможности Excel разнообразны. Вполне достаточно, чтобы наглядно изобразить разные типы данных.
Все очень просто. Хотим, чтобы ячейка меняла свой цвет (заливка, шрифт, жирный-курсив, рамки и т.д.) если выполняется определенное условие. Отрицательный баланс заливать красным, а положительный — зеленым. Крупных клиентов делать полужирным синим шрифтом, а мелких — серым курсивом. Просроченные заказы выделять красным, а доставленные вовремя — зеленым. И так далее — насколько фантазии хватит.
Чтобы сделать подобное, выделите ячейки, которые должны автоматически менять свой цвет, и выберите в меню Формат — Условное форматирование (Format — Conditional formatting) .
В открывшемся окне можно задать условия и, нажав затем кнопку Формат (Format) , параметры форматирования ячейки, если условие выполняется. В этом примере отличники и хорошисты заливаются зеленым, троечники — желтым, а неуспевающие — красным цветом:
Кнопка А также>> (Add) позволяет добавить дополнительные условия. В Excel 2003 их количество ограничено тремя, в Excel 2007 и более новых версиях — бесконечно.
Если вы задали для диапазона ячеек критерии условного форматирования, то больше не сможете отформатировать эти ячейки вручную. Чтобы вернуть себе эту возможность надо удалить условия при помощи кнопки Удалить (Delete) в нижней части окна.
Другой, гораздо более мощный и красивый вариант применения условного форматирования — это возможность проверять не значение выделенных ячеек, а заданную формулу:
Если заданная формула верна (возвращает значение ИСТИНА), то срабатывает нужный формат. В этом случае можно задавать на порядок более сложные проверки с использованием функций и, кроме того, проверять одни ячейки, а форматировать — другие.
Выделение цветом всей строки
Главный нюанс заключается в знаке доллара ($) перед буквой столбца в адресе — он фиксирует столбец, оставляя незафиксированной ссылку на строку — проверяемые значения берутся из столбца С, по очереди из каждой последующей строки:
Выделение максимальных и минимальных значений
Ну, здесь все достаточно очевидно — проверяем, равно ли значение ячейки максимальному или минимальному по диапазону — и заливаем соответствующим цветом:
В англоязычной версии это функции MIN и MAX, соответственно.
Выделение всех значений больше(меньше) среднего
Аналогично предыдущему примеру, но используется функция СРЗНАЧ (AVERAGE) для вычисления среднего:
Скрытие ячеек с ошибками
Чтобы скрыть ячейки, где образуется ошибка, можно использовать условное форматирование, чтобы сделать цвет шрифта в ячейке белым (цвет фона ячейки) и функцию ЕОШ (ISERROR) , которая выдает значения ИСТИНА или ЛОЖЬ в зависимости от того, содержит данная ячейка ошибку или нет:
Скрытие данных при печати
Аналогично предыдущему примеру можно использовать условное форматирование, чтобы скрывать содержимое некоторых ячеек, например, при печати — делать цвет шрифта белым, если содержимое определенной ячейки имеет заданное значение («да», «нет»):
Заливка недопустимых значений
Сочетая условное форматирование с функцией СЧЁТЕСЛИ (COUNTIF) , которая выдает количество найденных значений в диапазоне, можно подсвечивать, например, ячейки с недопустимыми или нежелательными значениями:
Проверка дат и сроков
Поскольку даты в Excel представляют собой те же числа (один день = 1), то можно легко использовать условное форматирование для проверки сроков выполнения задач. Например, для выделения просроченных элементов красным, а тех, что предстоят в ближайшую неделю — желтым:
Счастливые обладатели последних версий Excel 2007-2010 получили в свое распоряжение гораздо более мощные средства условного форматирования — заливку ячеек цветовыми градиентами, миниграфики и значки:
Вот такое форматирование для таблицы сделано, буквально, за пару-тройку щелчков мышью. 🙂
С выходом новой версии программы для ПК Microsoft Office появились и новые возможности. Разработчики доработали некоторые компоненты, сделали еще более удобным работу с программами. Нельзя обойти вниманием и Excel 2010 и новые возможности инфографики в нем. Поэтому в данной статье мы на примере расскажу вам, как работать с новыми компонентами Excel 2010.
С выходом новой версии Microsoft Office появились и новые возможности. Разработчики доработали некоторые компоненты, сделали еще более удобным работу с программами. Нельзя обойти вниманием и Excel 2010 и новые возможности инфографики в нем. Поэтому в данной статье мы на примере расскажу вам, как работать с новыми компонентами Excel 2010.
Делаем сводную таблицу в Excel
В нашем распоряжении есть достаточно большая таблица. В ней огромное количество столбцов и строк. По этим данным нужно построить что-то в виде отчета, чтобы просмотреть результаты по какой-либо деятельности за определенный период. На вкладке «Вставка» нажимаем кнопку «Сводная таблица». Перед нами открывается диалоговое окно, в котором Excel в качестве диапазона данных выбрал всю таблицу. Нажимаем кнопку «Ок».
Теперь на новом листе появился макет сводной таблицы. В правой части окна перечислены все параметры, которые фигурировали в начальной таблице. Нам необходимо с помощью мыши перетащить их в поле «Название строк». В нашем случае это будут «Даты», «Менеджеры». Такие показатели как: «Объем продаж», «Выручка» и «Прибыль» мы перенесем в поле «Значения». Когда осуществляется перенос параметров в поле, таблица автоматически формируется и изменяется «налету». Расположение элементов в «Название строк» играет большую роль. Если «Даты» будут расположены выше «Менеджеры», то данные будут разбиты на отдельные блоки по датам. Если же «Менеджеры» будут расположены в списке первыми, то сортировка будет проходить по именам сотрудников.
Когда таблица построена, перейдем к ее оформлению. Для начала изменим цветовую схему, применив к ней шаблон. Переходим на вкладку «Главная» и нажимаем на кнопку «Форматировать как таблицу». На экране появится список различных шаблонов форматирования, выбираем понравившийся нам и нажимаем на него. Excel автоматически определит границы таблицы, если они заданы не верно, выделяем таблицу вручную и нажимаем кнопку «Ок». Таблица поменяла цветовую гамму и появилась возможность сортировки параметров.
Условное форматирование таблицы в Excel 2010
Не всегда удобно просматривать большое число значений и сравнивать их с плановыми. Предположим, что объем выручки на каждого менеджера в месяц должен составлять не менее 100 000 рублей. Но не обязательно оценивать показатели вручную, просматривая каждое значение, проще довериться встроенному компоненту Excel. Выделим область данных. Переходим во вкладку «Вставка – Условное форматирование — Набор значков» из выпадающего меню выбираем понравившийся шаблон, мне нравиться светофор, так как с ним очень удобно работать. После выбора шаблона перед нами появится окно «Создание правил форматирования». Здесь необходимо напротив этих самых значков ввести данные, при превышении которых работа сотрудника оценивается как: отличная, удовлетворительная и неудовлетворительная. Данные вводятся в параметр «Значение» напротив каждого из кружков, а поле «Тип» в данном случае необходимо изменить с «Процент» на «Числа». Мною были заданы следующие параметры: 110, 90. Третий параметр задается автоматически, он оценивается как все значения меньше удовлетворительного. Нажимаем кнопку «Ок».
В ячейках всех значений появились кружки трех различных цветов. На основе, представленной в таком виде информации намного проще оценить работу менеджеров за определенный период времени. Мы можем сравнить качество работы сотрудников, определить кто из работников добивается наиболее выдающихся результатов, а кто наоборот требует к себе пристального внимания.
Но это еще не последний способ условного форматирования данных. В Excel 2010 появились такие инфографические элементы как «Гистограммы» и «Цветовые шкалы». Рассмотрим их более подробно. Выделим значения в ячейках и переедем «Вставка – Условное форматирование — Гистограммы». В выпадающем меню появится список шаблонов, при наведении на любой из них происходит пред просмотр результата. Выбираем понравившуюся цветовую схему и видим, что ячейки залиты горизонтальными столбцами разной величины. Они отображают в графическом виде те значения, которые присутствуют в ячейках. Если число будет введено со знаком минус, то график сместится в противоположную сторону от ячейки, указывая на отрицательные величины.
Компонент «Цветовые шкалы» заливает ячейки тем цветом, который соответствуют значению, введенному в нее. К примеру, наименьшие значения будет залиты в красный цвет, средние в желтый, а наибольшие в зеленый. Цветовая схема может быть подобрана вами индивидуально, но суть примерно остается тоже, что при использовании «Набор значков».
Срезы и не только
Но это еще не все способности визуализации данных, включенные в пакет Excel 2010. Рассмотрим еще такую удобную функцию как «Срезы». Выбранные работники отработали в компании весьма внушительный срок и сложно при формировании сводной таблицы выделить ту или иную дату. Есть два способа обращения к определённой дате. Когда мы строим сводную таблицу в правой части у нас расположены элементы, которые мы можем разместить в различные поля. Обращаемся к элементу «Даты» и вызываем выпадающее меню, путем нажатия на маркер со стрелочкой. Находим пункт «Фильтр по дате». Открывается огромный список с различными вариантами форматирования, но нам нужна помесячная сортировка. Открываем «Все даты за период» и выбираем «Октябрь». Сводная таблиц значительно сократилась, в ней остались значения только за октябрь. Это первый способ выборки данных.
Второй способ организуется с помощью новой функции «Срез», интересного инструмента анализа цифровых данных. Перейдем к «Вставка — Срез». Открывается окно «Вставка среза» в нем нужно отметить тот показатель, по которому будет производиться выборка значений, то есть колонку таблицы, по которой вы сможете посмотреть срезы своего отчета. Отмечаем «Даты» и нажимаем кнопку «Ок». На листе отобразится рамка с записанными в нее значениями.
Перетащим ее в любое удобно для нас место и отрегулируем ее размер таким образом, чтобы можно было видеть все представленные в ней значения. Так же можно изменить цвет среза, все шаблоны отображаются на верхней панели. Теперь мы можем одним кликом выбрать определенную дату, и увидеть каких результатов за эти дни добились сотрудники. Данная функция на порядок удобнее, чем «Фильтр по дате», так как она более гибкая. В ней вы можете выбрать сразу несколько значений, по которым будет идти выборка.
Инфокривые
Следующий способ визуального анализа данных – это инфокривые. Делаем активной свободную ячейку напротив строк с данными. Во вкладке «Вставка» находим раздел «Инфокривые» (в моей версии Excel 2010 они назывались почему-то «Сперклайны»). Выделяем диапазон данных – это будет наша строка, и нажимаем кнопку «Ок». Вы можете увидеть, как в выбранной нами ячейке построился мини график, это и есть инфокривая.
Растянем эту ячейку на все остальные строки, потянув за край с точкой или сделав двойной клик на нем. При желании вы можете изменить стиль инфокривой, его выбор осуществляется на верхней панели в режиме конструктора инфокривой. Полученный график позволяет увидеть тенденцию, тренд. При огромном количестве данных инфокривая дает общий визуальный анализ всего множества. По ней с легкостью можно определить пики и падения, начало роста или его замедление.
Инфокривые бывают трех типов: «График» — как раз его мы и рассматривали; «Столбец» — отображает данные в виде маленьких столбцов, наглядно показывая максимальные и минимальные значения; «Выигрыш/проигрыш» — ячейка как бы разделяется на де части, в нижней части размещаются квадраты с отрицательными значениями, в верхней с положительными, ноль не отображается вовсе.
В данной статье мы не только научились быстро оформлять таблицу, но и проводить визуальный анализ данных. Также мы ознакомились с такими понятием как сводная таблица, научились производить фильтрацию значений и условное форматирование цифровых значений, составлять срезы. Кроме этого, мы наглядно разобрались с новой функцией под названием «Инфокривые». Нельзя не отметить, что усовершенствования в Excel 2010 видны на лицо, и практически все новые функции направлены на облегчение труда специалиста и наглядное представление данных. Если вас заинтересовала новая функциональность табличного редактора MS Excel 2010, то вы можете приобрести Microsoft Office 2010 у партнеров компании 1CSoft.
Неберекутин Александр
Все права защищены. По вопросам использования статьи обращайтесь к администраторам сайта
Читайте также:
- Как узнать размер базы 1с
- Как сделать в опере чтобы вкладки не закрывались
- Не устанавливаются драйвера на ноутбук lenovo
- Как добавить информацию о себе в яндекс браузере
- Статья оборотов в 1с это
Работа в Excel с формулами и таблицами для чайников
Формула предписывает программе Excel порядок действий с числами, значениями в ячейке или группе ячеек. Без формул электронные таблицы не нужны в принципе.
Конструкция формулы включает в себя: константы, операторы, ссылки, функции, имена диапазонов, круглые скобки содержащие аргументы и другие формулы. На примере разберем практическое применение формул для начинающих пользователей.
Формулы в Excel для чайников
Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.
В Excel применяются стандартные математические операторы:
| Оператор | Операция | Пример |
| + (плюс) | Сложение | =В4+7 |
| — (минус) | Вычитание | =А9-100 |
| * (звездочка) | Умножение | =А3*2 |
| / (наклонная черта) | Деление | =А7/А8 |
| ^ (циркумфлекс) | Степень | =6^2 |
| = (знак равенства) | Равно | |
| Больше | ||
| = | Больше или равно | |
| <> | Не равно |
Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.
Программу Excel можно использовать как калькулятор. То есть вводить в формулу числа и операторы математических вычислений и сразу получать результат.
Но чаще вводятся адреса ячеек. То есть пользователь вводит ссылку на ячейку, со значением которой будет оперировать формула.
При изменении значений в ячейках формула автоматически пересчитывает результат.
Ссылки можно комбинировать в рамках одной формулы с простыми числами.
Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.
В нашем примере:
- Поставили курсор в ячейку В3 и ввели =.
- Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
- Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.
Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:
Поменять последовательность можно посредством круглых скобок: Excel в первую очередь вычисляет значение выражения в скобках.
Как в формуле Excel обозначить постоянную ячейку
Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.
Все ссылки на ячейки программа считает относительными, если пользователем не задано другое условие. С помощью относительных ссылок можно размножить одну и ту же формулу на несколько строк или столбцов.
- Вручную заполним первые графы учебной таблицы. У нас – такой вариант:
- Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.
- Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.
Находим в правом нижнем углу первой ячейки столбца маркер автозаполнения. Нажимаем на эту точку левой кнопкой мыши, держим ее и «тащим» вниз по столбцу.
Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.
Ссылки в ячейке соотнесены со строкой.
Формула с абсолютной ссылкой ссылается на одну и ту же ячейку. То есть при автозаполнении или копировании константа остается неизменной (или постоянной).
Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.
- Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
- Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».
- После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.
Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:
- Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.
- Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5
- Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.
При создании формул используются следующие форматы абсолютных ссылок:
- $В$2 – при копировании остаются постоянными столбец и строка;
- B$2 – при копировании неизменна строка;
- $B2 – столбец не изменяется.
Как составить таблицу в Excel с формулами
Чтобы сэкономить время при введении однотипных формул в ячейки таблицы, применяются маркеры автозаполнения. Если нужно закрепить ссылку, делаем ее абсолютной. Для изменения значений при копировании относительной ссылки.
Простейшие формулы заполнения таблиц в Excel:
- Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+» Новая графа.» src=»https://exceltable.com/formuly/images/formuly18-16.png» >
- По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.
- Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» — выбираем формулу для автоматического расчета среднего значения.
Чтобы проверить правильность вставленной формулы, дважды щелкните по ячейке с результатом.
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры