Материал: 1599

Например, формула =ЕСЛИ(В56/С15 150<0;В56;С15) возвратит значение из ячейки В56, если после вычислений получиться отрицательное число. Если после вычислений получится положительное число или 0, формула возвратит значение из ячейки С15.

В качестве аргументов функции ЕСЛИ можно использовать другие функции, например формула

=ЕСЛИ(ПРОИЗВЕД(А23;В23;С23)>2,5; ПРОИЗВЕД(А23;В23;С23);0)

возвратит произведение значений в ячейках А23, В23 и С23, если это произведение больше 2,5. В противном случае формула возвратит 0.

В функции ЕСЛИ можно использовать текстовые аргументы. Например, обработка результатов тестирования находится в ячейке А1. В ячейку В1 введена формула

=ЕСЛИ(А1>75%; ”Сдал”;”Не сдал”),

которая проверяет балл в ячейке А1. Если этот балл больше 75 %, функция возвращает в ячейку В1 текст Сдал. Если балл в ячейке А1 меньше или равен 75 %, функция возвращает в ячейку В1 текст Не сдал.

С помощью текстового аргумента можно очистить ячейку, вставив пустую строку вместо 0. Например, вышеприведённая формула

=ЕСЛИ(ПРОИЗВЕД(А23;В23;С23)>2,5; ПРОИЗВЕД(А23;В23;С23);””)

возвратит пустую строку (””), если первый аргумент будет иметь значение ЛОЖЬ, то есть произведение А23, В23 и С23 получится меньше или равно

2,5.

Первый аргумент, логическое выражение, может также быть текстовым. Например, формула

=ЕСЛИ(А1=”Тест”;100;200)

возвратит значение 100, если ячейка А1 содержит строку Тест, и 200, если в А1 находится любое другое значение.

Функции И, ИЛИ, НЕ позволяют создавать сложные логические выражения, являясь дополнительными функциями. Они работают в сочетании с операторами сравнения. Синтаксис функций И и ИЛИ идентичный:

=И(логическое_значение1;логическое_значение2;…логическоезначение30)

и

=ИЛИ(логическое_значение1;логическое_значение2;…логическоезначение30).

81

Эти функции могут иметь от 1 до 30 аргументов, которые являются проверяемыми условиями.

Если аргумент, который является массивом или ссылкой, содержит тексты или пустые ячейки, такие значения игнорируются.

Если один из аргументов не является логическим значением, то функции возвращают ошибочное значение #ЗНАЧ!.

Функция И возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Функция ИЛИ возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА, и значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Функция НЕ имеет только один аргумент:

=НЕ(логическое_значение).

Эта функция меняет значение своего аргумента на противоположное логическое значение. Она возвращает значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и значение ЛОЖЬ, если аргумент имеет значение ИСТИНА.

Функции И, ИЛИ, НЕ часто используются в сочетании с функцией ЕСЛИ. Например, студент Сдал зачёт, если имеет меньше 3 пропусков (проставляются в ячейке В1) и за тест получил балл более 75 % (балл хранится в ячейке С1). Это можно записать следующим образом:

=ЕСЛИ(И(В1<3;C1>75%);”Сдал”;”Не сдал”).

При использовании функции ИЛИ формула будет записана так же:

=ЕСЛИ(ИЛИ(В1<3;C1>75%);”Сдал”;”Не сдал”).

Однако такая формула возвратит значение Сдал, если либо балл более 75 %, либо пропусков меньше 3.

На примере тестирования студентов можно показать работу функции НЕ. Допустим в ячейку С1 заносится балл по 5ти бальной шкале, полученный студентом на экзамене, тогда формула

=ЕСЛИ(НЕ(С1=2);”Сдал”;”Не сдал”),

возвратит значение Сдал, если значение в ячейке С1 не равно 2. Некоторые задачи трудно решить, используя только вышеперечислен-

ные логические функции. В таких случаях применяют вложенные функции ЕСЛИ. То есть до семи функций ЕСЛИ могут быть вложены друг в друга в качестве аргументов значение_если_истина, значение_если_ложь.

82

Для пояснения приведу пример с тремя вложенными функциями ЕСЛИ. В ячейке А1 фиксируются показания прибора

=ЕСЛИ(А1=100;”Ok”;ЕСЛИ(И(А1>=80;A1<100);”Норма”;

ЕСЛИ(И(А1>=60;A1<80);”Проблема”;”Тевога”))).

Комментируется эта формула следующим образом: если значение в ячейке А1 равно 100, формула возвратит текст Ok. Если значение в ячейке А1 находится в пределах от 80 до 99, формула возвратит текст Норма. Если значение в ячейке А1 находится между 60 и 79, формула возвратит текст Проблема. И если в ячейке А1 значение будет 59 и меньше, то есть ни одно из предыдущих условий не выполняется, формула возвратит текст

Тревога.

Функции ИСТИНА, ЛОЖЬ. Эти функции не имеют аргументов и записываются следующим образом:

=ИСТИНА() =ЛОЖЬ().

ВMicrosoft Excel кроме перечисленных логических функций имеются Е-функции, которые могут быть отнесены к логическим: ЕОШ, ЕОШИБКА, ЕНД, ЕЛОГИЧ, ЕЧИСЛО, ЕТЕКСТ, ЕНЕТЕКСТ, ЕССЫЛКА, ЕПУСТО. Они используются, например, для поиска ячеек с ошибочными значениями, с целью предотвращения их распространения по рабочему листу; для нахождения ячеек с логическими значениями; для определения является ли значение ячейки числом, текстом, ссылкой или ячейка пустая.

Статистические функции Microsoft Excel позволяют отыскивать в диапазоне максимальное и минимальное значение, рассчитывать среднее арифметическое значение, определять количество ячеек в диапазоне и т.д.

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

Диаграммы

Диаграмма является графическим объектом, с помощью которого можно представлять данные рабочего листа. Располагать диаграммы можно на листе вместе с исходными данными или на отдельном листе, являющимся частью книги. Диаграммы, расположенные непосредственно на рабочем листе, называются внедрёнными. Диаграммы как любые графические объекты можно размещать в любом месте листа, изменять их размеры и различным образом модифицировать.

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

83

грамме, например, количество опрокидываний, произошедших в январе или число ТО-1, проводимых в разными бригадами слесарей. Каждый ряд данных может иметь до 4000 значений (точек данных). На диаграмме можно отобразить до 255 рядов данных, но точек данных на диаграмме может быть не более 32000.

Категории задают положение конкретных значений в ряде данных. В примере с количеством опрокидываний это дни месяца (1 января, 2 января и т.д.). А в случае с ТО-1 категориями являются бригады слесарей.

Для создания диаграмм удобнее всего воспользоваться Мастером диаграмм, включаемым кнопкой на панели инструментов Стандартная. Запустить Мастер диаграмм так же можно, выбрав команду Диаграмма… в пункте меню Вставка. После появления окна Мастер диаграмм (шаг 1 из 4) останется выполнить ряд действий и для перехода к следующему шагу нажимать кнопку Далее. Для исправления своих действий и возвращения к предыдущему шагу необходимо нажать кнопку Назад. Можно нажать кнопку Готово для пропуска оставшихся шагов. На первом шаге осуществляется выбор типа диаграмм, на втором – задание диапазона данных, если вы не выделили этот диапазон заранее на рабочем листе, или исправление этого диапазона. Третий шаг позволяет добавить легенду, название диаграммы, название осей. На четвёртом шаге вы выбираете расположение диаграммы: на рабочем или на отдельном листе.

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

Microsoft Excel содержит несколько типов плоских и объёмных диаграмм, которые разделяются на стандартные и нестандартные.

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

Назначение каждой из диаграмм можно определить следующим образом:

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

84

механизма по видам отказов (рис. 27) (данные всех статистических диаграмм взяты с потолка).

Рис. 27. Причины отказов кривошипно-шатунного механизма

Графики – отображают изменения ряда данных во времени или по категориям. Например, количество дорожно-транспортных происшествий по месяцам одного года (рис. 28).

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

Точечная диаграмма – используется для сравнения рядов данных или определения зависимости между рядами данных. Например, зависимость тормозного пути от коэффициента сцепления шины с дорогой (рис. 30). То есть в большинстве типов диаграмм (кроме круговой, кольцевой, лепестковой) вдоль одной оси изменяются значения ряда данных, вдоль другой идут названия категорий. Как правило, ось ординат (ось Y) является осью значений ряда данных, ось абсцисс (ось X) – осью категорий. В точечной диаграмме обе оси являются осями значений ряда данных.

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

85