Выделим ячейки с одинаковыми значениями

Форматирование с помощью гистограмм

До этого мы рассматривали форматирование с помощью простого выделения ячеек. Теперь познакомимся с более интересным способом.

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

Чтобы это сделать, нужно в «Условном форматировании» найти «Гистограммы» и выбрать градиентную или сплошную заливку:

В итоге мы получим таблицу, в которой сразу видно отношение данных друг к другу:

Использование значков в сортировке

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

В «Условном форматировании» есть разные наборы значков, которые можно использовать:

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

Можно ранжировать по процентным соотношениям, расчетам формул или, как у нас, по значениям: галочка, если больше 26 000; восклицательный знак, если больше 22 000, но меньше 26 000; крестик, если меньше 22 000:

Форматирование с помощью цветовых шкал

Последний прием, о котором мы поговорим в разделе «Условного форматирование», это использование цветовых шкал.

Для этого выделим данные таблицы, зайдем в «Условное форматирование» —> «Цветовые шкалы», где можно выбрать самые разные дизайны раскрашивания:

Мы в таблице хотели сравнить максимальные и минимальные значения. Поэтому выбрали шкалу «красный-желтый-зеленый», которая показывает относительную величину значений с помощью насыщенности оттенков.

Вот какая красивая картинка у нас получилась (максимальной температуре соответствуют темно-красные ячейки, минимальной – темно-зеленые):

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

 

Задание

    Перенесите таблицу по успеваемости в Excel. Для отметок задайте условное выделение, чтобы разные отметки в таблице выделялись разными цветами.

Видео: https://youtu.be/ihp2KlGaFbE

 

Примеры вычислений в Excel

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

1. Выбираем ячейку, где должна будет отображаться сумма, и ставим там =. Далее выделяем ячейку с количеством товара — ссылка на нее сразу же появляется после знака равенства. После координат ячейки нужно вставить арифметический знак. В нашем случае это будет знак умножения — *. Теперь кликаем по ячейке, где размещаются данные с ценой единицы товара. Арифметическая формула готова.

1. Для просмотра ее результата нажмите клавишу Enter.

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

1. Формула скопировалась и общая стоимость автоматически рассчиталась для каждого вида товара,согласно данным о его количестве и цене.

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

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

1. Итак, пишем = в первой ячейке столбца «Сумма». Затем открываем скобку, кликаем по первой ячейке в столбце «1 партия», ставим +, щелкаем по первой ячейке в столбце «2 партия». Далее закрываем скобку и ставим *. Кликаем по первой ячейке в столбце «Цена» — так мы получили формулу.

1. Нажимаем Enter, чтобы узнать результат.

1. Так же, как и в прошлый раз, с применением способа перетягивания копируем данную формулу и для других строк таблицы.

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

Основные операторы Excel

К основным операторам вычислений, которые применяются в Microsoft Excel, относятся следующие:

• = («знак равенства») – равно;
• + («плюс») – сложение;
• - («минус») – вычитание;
• * («звездочка») – умножение;
• / («наклонная черта») – деление;
• ^ («циркумфлекс») – возведение в степень.

 

Функция СЧЁТЕСЛИ

= СЧЕТЕСЛИ(диапазон; критерий), где все критерии обязательные и обозначают:

диапазон – эта определенная вами группа ячеек, в которой вы будете производить подсчёт согласно нужных вам критериев. В диапазон могут входить текстовые ячейки и пустые значения, игнорироваться они не будут. Сам диапазон может в себе содержать массивы, поименованный диапазон, разнообразные числа и ссылки на них;

критерий – это выражение, текстовое значение, ссылка на ячейку или число, то есть любое значение, которое может определить, что же именно нужно найти и какие ячейки будет считать. К примеру, критерий может выглядеть как «40», «=>40», «С65», «менеджер».

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

= СЧЕТЕСЛИ(C2:C10;">=100"), в критериях мы используем символы и числа;

= СЧЕТЕСЛИ(C2:C10;$E$8), здесь в критериях мы используем ссылку на условие.

Задание

       В уже готовой таблице успеваемости с помощью функций посчитайте средний балл и сумму отметок по каждому ученику и по каждому предмету. В качественной и абсолютной успеваемости посчитайте с помощью формул. (пример формул в документе пор работе с MS Word).

       Создайте такую таблицу после таблицы с успеваемостью. И посчитайте с помощью функции СЧЁТЕСЛИ, количество 5, 4 и 3 по каждому предмету

Предмет	5	4	3
Русский
…
Абсолютная успеваемость		Качественная успеваемость

ВИДЕО:

https://youtu.be/pzitXGx5XG4

Выделим ячейки с одинаковыми значениями

Выделим ячейки, которые нужно форматировать. В разделе «Условное форматирование» находим «Правила выделения ячеек» и выбираем «Повторяющиеся значения»:

Появится диалоговое окно, где вы сможете выбрать — выделять повторяющиеся или уникальные значения, также цвет выделения:

Получим такой результат:

Выделим ячейки С1:С7.

Аналогичным способом откроем «Правила выделения ячеек», но на этот раз закрасим те ячейки, значения которых больше 25 000:

Получим результат:

Можно форматировать таблицы сразу по нескольким условиям. Выделим уже отформатированные ячейки С1:С7 и применим к ним правило: меньше 22 800 и выберем отличный от прошлого дизайн выделения.

Так будет выглядеть наша таблица после применения двойного форматирования:

При необходимости изменения параметров форматирования, дизайна ячеек в разделе «Условное форматирование» выбираем «Управление правилами» и в открывшемся диалоговом окне модифицируем правило так, как нам нужно:

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

Выберем «Создать правило». В появившемся окне выделим подходящий тип правила (у нас – форматировать ячейки, которые содержат), указываем необходимые параметры (у нас – значения между 22 000 и 25 000) и задаем формат выделений (у нас – жирный шрифт синего цвета):

После всех преобразований таблица будет выглядеть следующим образом:

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

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

Замечание 2. Условное форматирование применимо не только к числовым данным, но и к текстовым. Для строк есть встроенное правило «Текст содержит», где указывается фраза и выделяются все ячейки, содержащие её.

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

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

В таком случае приходят на помощь «Правила отбора первых и последних значений»:

Задаем количество первых (по величине значения) ячеек и цвет заливки:

Получаем таблицу с выделенными четырьмя наибольшими значениями: