Работа с формулами в Excel

Любая формула в Excel начинается со знака «=» и может включать константы, знаки операторов, адреса ячеек и функции. Например: =2+А2/SIN(B2). Операторы делятся на арифметические, текстовые, операторы отношений.

Арифметические операторы (в порядке приоритета):

- - унарный минус (умножение на -1);

- % - процент;

- ^ - возведение в степень; *,/ - умножение, деление;

- +, - - сложение, вычитание.

Приоритет изменяется с помощью круглых скобок ( )

Если в ячейку введено число со знаком %, то фактическое его значение будет в 100 раз меньше. Например: в ячейку введено 5%; Excel запомнит число 0,05.

Текстовый оператор: &. Используется для сцепление двух и более текстовых строк в одну. Например: = ―Петр‖& ― Кузнецов‖; в результате в ячейке будет фраза «Петр Кузнецов». Операторы отношения: = - равно; <> - не равно; < -меньше; > - больше; <= - меньше или равно; >= - больше или равно.

Данная группа операторов может использоваться как с числовыми, так и с текстовыми данными. Результатом использования операторов отношения в формулах будут слово ИСТИНА, если результат сравнения истинный и ЛОЖЬ, если результат сравнения ложный.

Адресация ячеек

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

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

Абсолютная адресация. Абсолютный адрес ячейки – это адрес, который не изменяется при копировании. При этом при имени столбца и номере строки стоит знак $ (доллар).

Смешанная адресация содержит как абсолютный, так и относительный адрес, т.е. знак доллара может стоять либо перед именем столбца, либо перед номером строки.

Для более удобного изменения адреса ячейки (в режиме ввода и редактирования формул) используется функциональная клавиша F4. Последовательное нажатие этой клавиши влечет поочередный ввод знака $ перед элементами адреса. Так для адреса В5 получим цепочку преобразований: В5 → $B$5 →B$5 → $B5 → B5. Т.е. для получения желаемого вида ссылки следует последовательно нажимать клавишу F4.

Ввод формул

Ссылки на ячейки и названия функций могут вводиться непосредственно с клавиатуры (при этом необходимо отслеживать раскладку клавиатуры (для адресов ячеек она должна быть английской). Лучше всего адреса ячеек и названия функций вводить строчными буквами (например а1, сумм()), т.к. если адрес ячейки (название функции) введены правильно, то после нажатия Enter или кнопки в строке формул они автоматически преобразуются в прописные. Если же адрес ячейки (название функции) введены неправильно, то они останутся записанными строчными буквами и обнаружить неправильно записанные элементы в формуле будет значительно проще.

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

Для более сложных вычислений в Excel используются функции (более 500). Причем часть из них пишется латинскими буквами, а другая – русскими. Запомнить все функции не представляется возможным. Однако в процессе решения задачи надо четко понимать, как должен строится вычислительный процесс, т.е. четко представлен алгоритм решения задачи, и исходя из этого выбирать функции для решения задачи.

Список всех функций можно найти в окне Мастера функций, воспользовавшись командой из вкладки «Формулы» или щелкнуть левой кнопкой мыши в строке формул по значку .

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

В общем виде любая функция может быть записана в виде: =<имя_функции> (аргументы).

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

Существуют следующие правила ввода функций:

Если формула начинается с функции, то вызывается Мастер функций. Если функция вводится вручную, то вводится знак «=», далее Имя функции.
Аргументы заключаются в круглые скобки, указывающие на начало и конец списка аргументов.
Между именем функции и знаком «(» пробел не ставится.
Вводить имя функции и ее аргументов рекомендуется строчными буквами. Если ввод осуществлен правильно, Excel сам преобразует строчные буквы в прописные.

Для проверки правильности работы формулы во вкладке «Формулы» в группе команд «Зависимости формул» щелкнуть по кнопке «Вычислить формулу» (предварительно ячейка с формулой должна быть выделена). В открывшемся диалоговом окне, щелкая по кнопке вычислить, можно отследить весь процесс вычисления формулы по шагам.

Например: Пусть ячейка С10 содержит формулу для расчета текущего бюджета. Если результат вычислений по формуле в ячейке A10 меньше или равен 100, то в ячейке С10 отображается сообщение «В пределах бюджета», в противном случае отображается сообщение «Превышение бюджета».

Решение: Решить эту задачу можно с помощью функции ЕСЛИ( ). =ЕСЛИ(A10<=100;"В пределах бюджета";"Превышение бюджета") Например: Предположим, что в таблице Excel ведется учет баллов студентов по балльно-рейтинговой системе. В итоговом столбце необходимо выводить итоговые результаты, в зависимости от набранной студентом за семестр суммы баллов. Отлично – 85 – 100 баллов, Хорошо – 65 - 84, Удовлетворительно – 51 – 64, Плохо – 50 и менее.

Решение: Эту задачу также как и предыдущую можно решить с помощью функции ЕСЛИ( ). При этом понадобиться использовать вложенные функции ЕСЛИ ( ). Итак, в качестве проверяемого условия будет выступать Сумма баллов. Тогда итоговая формула будет выглядеть следующим образом:

=ЕСЛИ(Сумма_баллов<=50;‖Плохо‖;ЕСЛИ(Сумма_баллов<65;‖Удовлетворительно‖; ЕСЛИ(Сумма_баллов<85;‖Хорошо‖;‖Отлично‖). Например. Дан список названий месяцев. Определить, какой по счету в данном списке месяц «мар»?

Для определения порядкового номера «мар» воспользуемся функцией ПОИСКПОЗ( ).

ПОИСКПОЗ( ) возвращает относительную позицию в массиве элемента, соответствующего указанному значению с учетом указанного порядка.

В нашем случае формула будет имеет вид: =ПОИСКПОЗ("мар";A2:E2;0), где "мар" – искомое_значение (что ищем); A2:E2 – Просматриваемый_массив (диапазон ячеек для поиска (обязательно либо строка, либо столбец)); 0 – тип_сопоставления, при котором функция ПОИСКПОЗ находит первое значение, равное аргументу искомое_значение.

Просматриваемый_массив может быть не упорядочен.

Например. В исходной таблице, найти число, расположенное под цифрой 5 в третьей строке таблицы. Для решения этой задачи воспользуемся функцией ГПР( ).

Функция ГПР( ) предназначена для поиска исходного значения (в нашем примере цифра 5) в самой верхней строке таблицы (диапазон B7:F9) и изъятие значения из ячейки, находящейся в строке с заданным номером того же столбца, что и исходное значение (в нашем примере строка 3).

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

Ответом в данном примере будет число 45.

Например. В исходном диапазоне найти символ, стоящий в одной строке с цифрой 3.

Для решения данного примера воспользуемся функцией ВПР( ).

Функция ВПР( ) является транспонированной функцией ГПР( ), т.е. исходное значение ищется в самом первом столбце диапазона, и значение извлекается из столбца с заданным номером той же строки, что и исходное значение.

В результате получим значение «а».

Функции ГПР( ) и ВПР( ) являются аналогами многократного использования в формуле функции ЕСЛИ( ).

Например. Найти значение, находящееся на пересечении 2-ой строки и 4-ого столбца.

Для решения данной задачи воспользуемся функцией ИНДЕКС( ).

Функция ИНДЕКС( ) осуществляет поиск ячейки в заданном диапазоне на пересечении строки и столбца с заданными номерами.

В результате применения в нашем примере функции ИНДЕКС( ) получим число 40.

Диаграммы

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

В версиях MS Excel 2007, 2010, 2013, 2016 добавление диаграммы на рабочий лист осуществляется через вкладку Ленты Вставка → Диаграммы. К основным типам диаграмм относятся:

Гистограмма – один из наиболее распространенных типов диаграмм. Используется для сравнения значений по категориям.
Линейчатая – это гистограмма, повернутая на 900 по часовой стрелке. Преимущество данного типа диаграмм состоит в том, что метки категорий читаются в них проще.
График отображает зависимость данных (ось Y) от величины значения X (ось Х) с постоянным шагом. Метки по оси Х должны располагаться по возрастанию или убыванию.
Круговая отображает соотношение между частями единого целого и строится только по одному ряду.
Точечная используется в тех случаях, когда необходимо отображать тенденции изменения донных при неравных интервалах времени или других интервалах изменения, отложенных по оси категорий.
С областями отслеживает непрерывное изменение суммы значений всех рядов данных и вклад каждого ряда в эту сумму.
Кольцевая– аналог круговой. Отличие состоит в том, что круговая диаграмма может отражать два и более ряда данных.
Лепестковая используется для отображения соотношений отдельных рядов данных, а также одного определенного ряда данных и всех остальных. При этом каждая точка данных имеет свою собственную ось координат.
Поверхность показывает низкие и высокие точки поверхности.

Используется для набора данных, зависящих от двух переменных.

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

И др.

На рисунке ниже представлены основные элементы диаграммы.

Для создания диаграмм следует подготовить данные для еѐ построения и определить тип. Необходимо учитывать следующее:

количество рядов данных (ось Y) должно быть меньше, чем число категорий (ось X).
- Если диаграмма строится для диапазона ячеек, имеющего большее или равное число столбцов, чем строк, то рядами данных считаются строки.
- Если диапазон ячеек имеет больше строк, то рядами данных считаются столбцы
названия, связанные с рядами данных, считаются их именами и составляют легенду диаграммы.
Данные, интерпретируемые как категории, считаются названиями категорий и выводятся вдоль оси X.
Если в ячейках, которые Excel будет использовать как название категорий, содержатся числа (не текст и не даты), то Excel разместит эти данные на графике в качестве рядов данных. Изменить данные построения можно во вкладке Работа с диаграммами → Конструктор → Данные → Выбрать данные→ Подписи горизонтальной оси (категорий) Изменить.

Операции над диаграммами:

Добавлять или удалять ряды данных. Их можно удалять непосредственно даже на графике.
Редактировать данные в диаграмме и на рабочем листе.
Переставлять ряды данных на диаграмме.
Вставлять текст в любом месте диаграммы.
Редактировать, форматировать и добавлять различные элементы диаграмм с помощью контекстного меню для необходимого элемента диаграммы.
Изменять пространственную ориентацию трѐхмерных диаграмм.
Добавлять различные графические объекты в диаграмму.
Настраивать оси и выбирать шкалы.
Строить составные диаграммы.
Использовать нестандартные типы диаграмм.
Применять диаграммы для анализа данных, то есть, строить различные линии тренда и делать прогнозы.

Рекомендуемая методика построения диаграмм:

Подготовить диапазоны данных для диаграммы (создать таблицу).
Выделить подготовленные данные.
Открыть вкладку Вставка.
В разделе Диаграммы выбрать нужный тип диаграммы.

Отформатировать полученную диаграмму.