Функции, использованные в лабораторной работе.

 

Функция	Результат	Назначение
=ДЛСТР("Бабочка")		Длина текста
=ЗАМЕНИТЬ("Лампочка";3;2; "ст")	Ласточка	Замена символов внутри текста
=И(ИСТИНА;ЛОЖЬ)	ЛОЖЬ	Логическое И
=EСЛИ(F1>5;10;5)	10 (здесь F1=7); 5 (здесь Fl=3)	Проверяет условие и возвращает одно из двух значений
=ЕТЕКСТ(155)	ИСТИНА	Если текст, возвращает логическое значение ИСТИНА
=ЕЧИСЛО("Текст")	ЛОЖЬ	Если число, возвращает логическое значение ИСТИНА
=НАЙТИ("Н";A2;1)	(A2→“ЛИМОН”) (1-отсчет с первой позиции)	Ищет вхождение одного текста в другой (с учетом регистра).
=ПОИСКПОЗ(39;B2:B5;1)	(B2:B5→25/38/40/41) (1- функция находит наибольшее значение, которое меньше или равно значению аргумента 39)	возвращается позиция ближайшего меньшего элемента (38) из диапазона B2:B5 (2)
=ВПР(1;A2:C10;2;)	(функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равно 1 (если последний аргумент опущен или 1), а затем возвращает значение из столбца B в той же строке)	Ищет значение в первом столбце массива таблицы и возвращает значение в той же строке из другого столбца массива таблицы
=СУММ(A2:A4)	(A2:A4→2/4/6)	Суммирует все числа в интервале ячеек.
=МАКС(A2:A6; 30)	(A2:A6→50/23/40/7)	Возвращает наибольшее значение из набора значений
=СЧЁТЕСЛИ(A2:A5;"яблоки")	(Количество ячеек, содержащих текст «яблоки» в первом столбце таблицы) Например 2	Подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.
=ЕПУСТО(A2)	(A2=5) ИСТИНА(то есть любая цифра >0)	Проверяет, является ли ячейка C2 пустой
=СЧЕТЗ(A2:A6)	(A2→пусто, A3→6,A4→2, A5→5,A6→пусто)	Подсчитывает количество непустых ячеек в приведенном списке

 

 

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

Условное форматирование – это инструмент электронных таблиц Excel, при помощи которого можно изменить форматирование ячеек (цвет заливки, шрифт, границы) в зависимости от заданного условия, не прибегая к помощи встроенного языка программирования Visual Basic for Applications. Условное форматирование может значительно упростить выделение определенных ячеек или диапазона ячеек и визуализацию данных с помощью гистограммы, цветовых шкал и наборов значков. Оно изменяет внешний вид диапазона ячеек на основе указанного условия (или критерия). Если условие выполняется, то диапазон ячеек форматируется в соответствии с заданным для условия форматом; если условие не выполняется, то диапазон ячеек не форматируется.

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

Условное форматирование позволяет автоматизировать выполнение однообразных операций. Например, необходимо в большой таблице данных закрасить красным цветом все ячейки, значение в которых превышает 100. Обычно это делается установкой фильтра→ Больше 100 и отфильтрованные строки закрашиваются. При изменении данных таблицы необходимо повторить фильтрацию таблицы вручную. Если значения этих ячеек формируются при помощи условного форматирования – ячейки будут окрашены красным автоматически всякий раз при изменении данных.

При создании условного форматирования можно ссылаться на другие ячейки только в пределах одного листа; нельзя ссылаться на ячейки других листов одной и той же книги или использовать внешние ссылки на другую книгу;

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

Условное форматирование позволяет применять форматирование ячеек избирательно или автоматически на основании их значений.

К примеру, можно задать условное форматирование таким образом, чтобы все ячейки с отрицательными значениями закрашивались в красный цвет. Когда вы вводите или меняете значение в ячейке, Excel проверяет его и сравнивает с условиями, заданными в правилах. Если значение отрицательное, фон ячейки закрасится, иначе останется без изменений.

Условное форматирование – это простой способ определить ячейки с ошибочными записями или значениями определённого типа. Вы можете использовать формат (например, красная заливка), чтобы легко идентифицировать определенные ячейки.

При нажатии кнопки Условное форматирование, которая находится в группе Стили вкладки Главная, вы появляется выпадающее меню со следующими опциями:

 

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

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

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

Цветовые шкалы позволяет задавать двух- и трехцветовые шкалы для цвета фона ячейки на основе ее значения относительно других ячеек в диапазоне

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

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

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

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

Рассмотрим пример создания правила изменения формата ячейки на красную заливку с темно-красным шрифтом при условии, если значение ячейки содержит слово Нет.

Выделим диапазон ячеек, к которому применяется условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование → Правила выделения ячеек → Текст содержит (Рис 3):

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

Щелкаем ОК, чтобы наше правило вступило в силу.

Рассмотрим еще один пример:

Применить три различных условных форматирования к одному и тому же диапазону ячеек: первый тип формата, когда ячейка содержит целевое значение, второй – когда больше цели и третий – когда меньше. Желая заливка с темно-желтым текстом для ячеек содержащих значение 95, Зеленая заливка с темно-зеленым текстом для ячеек со значениями больше 95 и Светло-красная заливка и темно-красным текстом для ячеек меньше 95.

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

Excel откроет диалоговое окно Равно, где в левом текстовом поле необходимо указать условие 95, а в правом выпадающем списке выбрать формат для этого условия Желтая заливка с темно-желтым текстом.

Далее задаем условное форматирование для значений больше 95. Из меню Условное форматирование → Правила выделения ячеек выбираем Больше, в появившемся диалоговом окне Больше указываем значение, выше которого ячейка будет закрашиваться в зеленый цвет, и сам формат (рис 5).

Аналогичную операцию проделываем для ячеек со значениями меньше 95. На этот раз из списка правил необходимо выбрать Меньше и задать формат с красной заливкой.

По мере того, как вы будете определять все три правила для диапазона, ячейки будут закрашиваться в определенный цвет. В итоге мы получим, примерно такую картину (рис 6).

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

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

Выделяем таблицу с данными, к которой мы хотим применить условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование → Создать правило. В появившемся диалоговом окне Создание правила форматирования в поле Выберите тип правила выбираем Использовать формулу для определения форматируемых ячеек.

В поле Измените описание правила задаем условия и формат для нашего правила.

В нашем случае, условием будет формула: =ИЛИ(ДЕНЬНЕД($A2;2)=6;ДЕНЬНЕД($A2;2)=7).

В качестве формата выбираем темно- красную заливку (рис 7).

Условное форматирование является полезным инструментом для визуализации числовых данных. В ряде случаев условное форматирование является реальной альтернативой создания диаграммы.