Материал: 64
11
только в том виде, как оно было введено, но и в виде символов ##### или числа 1,23Е+08. Такие ситуации возникают, когда размеры введённого числа больше ширины ячейки (столбца), но в первом случае к ячейке применён числовой формат (для воспроизведения числа полностью нужно увеличить ширину столбца), а во втором – общий или экспоненциальный форматы (число интерпретируется как 1,23, умноженное на 10 в восьмой степени).
Формат ячеек определяет вид отображения введённых в них дат, времени, денежных и других значений данных. Например, дата может быть представлена различным образом: 12.05.2012, 12 май 12, 12 мая 2012 г., 2012, 12 мая и т. д.
Для изменения формата отображения данных в ячейках рабочего листа нужно выделить эти ячейки, затем воспользоваться инструментами вкладки Главная ленты (рисунок 1) или диалогового окна Формат ячеек (рисунок 2). Для вывода диалогового окна Формат ячеек на экран следует выполнить команду Формат ячеек… в контекстном меню или нажать маленькую кнопку со стрелкой в правом нижнем углу любой из групп Шрифт, Выравнивание, Число на вкладке ленты Главная (рисунок 1).
Рисунок 2 – Диалоговое окно для форматирования ячеек
12
На вкладке Число диалогового окна Формат ячеек можно выбрать один из имеющихся форматов, изменить количество десятичных знаков в записи числа или создать свой нестандартный формат, используя шаблоны пункта (все форматы), На вкладке Выравнивание определяется характер отображения данных в ячейках рабочего листа, объединяются ячейки, устанавливается возможность вывода текстовой информации в ячейках в нескольких строках. На вкладке Шрифт формируются характеристики шрифта, применяемого при отображении данных, с помощью вкладки Граница создаётся обрамление ячеек, вкладки Заливка – цвет фона ячеек. Инструменты вкладки Защита используются в рамках технологий защиты данных от изменения или несанкционированного доступа.
После выделения ячеек и нажатия клавиши Delete или выполнения команды контекстного меню Очистить содержимое из этих ячеек удаляются только данные, форматы ячеек сохраняются. Для удаления данных, форматов и примечаний (если они были созданы) в выделенных ячейках нужно выбрать команду
Очистить всё в списке кнопки Очистить группы Редактирование на вкладке ленты Главная.
Условное форматирование
Условное форматирование в основном используется для выделения ячеек, значения данных в которых соответствуют некоторым условиям.
Диапазон ячеек, к которому планируется применить условное форматирование, необходимо выделить. Затем следует нажать кнопку Условное форматирование в группе Стили на вкладке ленты Главная и исходя из условий решаемой задачи выбрать один из пунктов: Правила выделения ячеек или Пра-
вила отбора первых и последних значений. Далее выполняется нужная команда (например, Больше…, Текст содержит…, Первые 10 %..., Выше средне-
го…). В появившемся после выполнения этих действий диалоговом окне создаются критерии, которые будут использованы для условного форматирования, и определяется формат, применяемый для выделения ячеек (рисунок 3).
Рисунок 3 – Окно для создания параметров условного форматирования
13
Создание формул
Основные сведения о формулах
Табличные процессоры в первую очередь предназначены для выполнения расчётов с помощью формул. После создания формулы в некоторой ячейке рабочего листа в этой ячейке будет отображаться результат вычислений, саму формулу можно увидеть в строке формул (рисунок 1). При изменении значений данных, используемых в формуле, результаты расчётов автоматически обновляются (если другие варианты вычислений не предусмотрены дополнительными настройками MS Excel).
Ячейку, в которую вводится формула, предварительно нужно активизировать. Любая формула должна начинаться со знака равенства ( = ), поэтому процесс создания формулы следует начать с ввода этого символа. Если данные в ячейке начинаются со знаков минус ( – ) или плюс ( + ), MS Excel интерпретирует их как формулу, перед которой создаст знак равенства автоматически. При необходимости ввода текста, начинающегося со знаков равенства, плюс или минус, перед ним нужно поставить апостроф.
Формула может состоять из констант, ссылок на адреса отдельных ячеек и диапазонов ячеек, функций, операторов, круглых скобок.
Вводить в формулы ссылки на адреса ячеек следует с помощью щелчков мышью на этих ячейках. Это позволит ускорить процесс ввода формул и избежать ошибок, неизбежных при наборе адресов ячеек на клавиатуре.
При выполнении однотипных расчётов рационально скопировать созданную формулу в другие ячейки рабочего листа. Это можно сделать с помощью команд Копировать и Вставить контекстного меню. В ситуации, когда формулу нужно скопировать в ячейки, прилегающие к ячейке, из которой копируется формула, удобно воспользоваться технологией автоматического заполнения ячеек, выделяя мышью ячейки, в которые копируется формула, с помощью небольшого квадрата в правом нижнем углу исходной ячейки.
При реализации данного процесса следует иметь в виду, что в формулах могут использоваться относительные, абсолютные и смешанные ссылки на адреса ячеек. При копировании или перемещении формул входящие в них относительные ссылки (имеющие, например, вид А5) автоматически изменяются, абсолютные ссылки ($A$5) остаются неизменными. Смешанные ссылки вида $A5 или A$5 позволяют зафиксировать при копировании или перемещении формул название столбца или номер строки соответственно. Для циклического измене-
14
ния типов ссылок на адреса ячеек достаточно выделить в формуле нужные ссылки и нажать клавишу <F4> необходимое количество раз.
В ячейках рабочего листа можно отобразить формулы, а не результаты расчётов по ним. Для этого следует нажать кнопку Показать формулы в группе
Зависимости формул на вкладке ленты Формулы.
Операторы
С помощью операторов в формулах указываются действия, которые реализуются над операндами (элементами) формул. Операторы в формуле выполняются в соответствии с их приоритетом, представленным в таблице 1 (приоритет уменьшается сверху вниз). Для изменения порядка выполнения операторов используются круглые скобки. Действия, имеющие одинаковый приоритет, реализуются в порядке слева направо.
Таблица 1 – Порядок выполнения операторов в формулах
Оператор |
Назначение оператора |
Примеры |
|||
|
|
|
|||
Арифметические операторы (результатом их выполнения является число) |
|||||
|
|
|
|
|
|
|
– |
|
Изменение знака |
–А7 |
–6 |
|
|
|
|
|
|
|
% |
|
Вычисление процентов |
D4% |
25% |
|
|
|
|
|
|
|
^ |
|
Возведение в степень |
А4^2 |
7^3 |
|
|
|
|
|
|
* |
и |
/ |
Умножение и деление |
R3*12,5 4/7 |
|
|
|
|
|
|
|
+ |
и |
– |
Сложение и вычитание |
G2+D1 |
R5–4 |
|
|
|
|
|
|
Текстовый оператор конкатенации (результатом выполнения является текст)
& |
Слияние текстовых значений |
Пере & оценка |
|
|
A2&C3 |
|
|
|
Операторы отношения (результат – логические значения ИСТИНА либо ЛОЖЬ)
= |
Равно |
D5=7,6 |
> |
Больше |
G1>H6 |
< |
Меньше |
J2<4^2 |
>= |
Больше или равно |
E3>=2 |
<= |
Меньше или равно |
F7<=G1+G2 |
< > |
Не равно |
H5< > Костюм |
|
|
|
15
Функции
Функции в MS Excel применяются для реализации наиболее часто выполняемых расчётов (например, суммирования данных). Формула может включать одну или несколько (иногда вложенных друг в друга) функций.
Каждая функция имеет имя, после которого в круглых скобках приводится список аргументов, разделённых символами точка с запятой. В качестве аргументов обычно используются числовые или текстовые константы, логические значения, ссылки на адреса отдельных ячеек или диапазонов ячеек, формулы или функции. Например, функция СРЗНАЧ(D5;A5:B10;МАКС(F1:F10)) вычисляет среднее арифметическое значение чисел для трёх заданных аргументов.
Так как в MS Excel имеется несколько сотен встроенных функций, для облегчения поиска нужной из них функции сгруппированы по категориям: Стати-
стические, Математические, Дата и время, Финансовые, Текстовые и др.
Функции в формулы могут быть вставлены различными способами. Универсальным методом является использование Мастера функций. Для начала работы с Мастером функций следует нажать кнопку 
в строке формул или кнопку Вставить функцию на вкладке ленты Формулы.
На первом шаге работы с Мастером функций необходимо выбрать имя нужной функции (рисунок 4).
Рисунок 4 – Диалоговое окно первого шага Мастера функций