Рубрика: Основы

Функции это специальные текстовые команды, которые реализуют сложные математические операции.
Каждая функция соответствует какой-либо формуле. Например функция СРЗНАЧ сокращает формулу: =(А1+А2+А3+А4+А5+А6+А7)/7 = СРЗНАЧ (А1:А7).

Ввод функций

Функция состоит:

• знак равенства (=),
• имя функции – аббревиатура, указывающая значение функции,
• набор скобок, внутри которых помещают аргументы,
• аргументы – значения, применяемые в расчетах.

Аргументами могут быть:

• отдельное значение,
• отдельная ссылка,
• серия ссылок на значения или ячейки,
• диапазон ячеек.

Простейшая функция аргументов не содержит. Например, =СЕГОДНЯ() возвращает текущую дату.
Если в функции используется несколько аргументов, каждый из них отделяется точкой с запятой.

Форматы функций

Большинство функций используют в качестве аргументов числа, но могут принимать и аргументы других типов.
Числовой. Любое целое или дробное число.
Время и дата. Любой допустимый формат времени и даты.
Текст. Текст, содержащий любые символы, заключенные в кавычки.
Логический тип. ИСТИНА/ЛОЖЬ, ДА/НЕТ, 1/0 и вычисляемые логические значения: 1+2=3
Ссылки на ячейки. Большинство аргументов представляют собой ссылки на результаты других ячеек (или групп ячеек) вместо использования явных значений.
Функции. В качестве аргумента можно использовать функцию, если она возвращает тип данных, который необходим для вычисления функции более высокого уровня.

Мастер функций

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

или через Ленту на вкладке формулы

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

Вложенные функции

В качестве аргументов могут быть использованы формулы или другие функции. Например: =ЕСЛИ(ЕНД(ВПР(B5;справочник!$A$3:$E$199;2;ЛОЖЬ));””;ВПР(B5;справочник!$A$3:$E$199;2;ЛОЖЬ)). Можно использовать до семи уровней вложенности функций. Если этот предел превысить,Excel выдаст ошибку.

Правила построения формул

Формула состоит из нескольких частей.

1. Знак равенства (=) – с него обязательно начинается любая формула.
2. Значения или ссылки на ячейки, с которыми выполняются расчеты.
3. Операторы – простые математические операции (сложение, деление и т.д.)

Например нужно сложить два числа
B2+C2
И записать результат в ячейку D2
Для этого нужно встать в ячейку D2 и нажать [=]. Затем встать на ячейку B2, нажать [+] и встать на ячейку C2.
При этом формула будет отображаться и в ячейке и в строке формул.

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

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

При этом Excel сам заменит ячейки B2 и С2 на ячейки B3 и C3 и т.д.
Для того, чтобы просуммировать весь столбец или строку можно воспользоваться кнопочкой Сумма в Ленте на вкладке Главная. Диапазон ячеек для суммирования будет обозначаться (D2:D5). Формулу можно прописать и вручную в строке формул. Для завершения операции нажмите Enter.

Excel сам предложит диапазон ячеек для суммирования, но его можно изменить просто выделив любой другой диапазон (например в ячейке E6 просуммируются два столбца B и С, диапазон для суммирования будет (B2:C5).

Адреса ячеек в разных листах

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

Абсолютные и относительные адреса ячеек

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

Иногда бывает необходимо при протягивании формул ссылаться на одну и ту же ячейку. Для этой цели применяют абсолютную ссылку. Абсолютная ссылка остается неизменной при протягивании или копировании формулы. Чтобы сделать ссылку абсолютной нужно поставить знак $ перед названием строки и столбца ячейки.

Также можно сделать абсолютной часть ссылки, т.е. постоянными будет либо столбец, либо строка. Такие ссылки называются смешанными.
$A1 всегда ссылается на столбец А, ссылка на строку будет меняться.
A$1 всегда ссылается на строку 1, ссылка на столбец будет меняться.
$A$1 всегда ссылается на ячейку А1.
Ввести абсолютную ссылку можно дважды щелкнув по ссылке в строке формул, так чтобы она выделилась, и нажимать клавишу F4. Повторно нажимая клавишу F4, можно циклически переходить от одного типа ссылок к другому.
Если у Вас ноутбук нажимайте одновременно клавиши Fn+F4. Абсолютную или смешанную ссылку можно ввести вручную вставив в нужном месте знак $.

Переименование листов

По умолчанию файл Excel запускается с тремя рабочими листами: Лист 1, Лист 2, Лист 3. Благодаря возможности использовать несколько листов в одной рабочей книге можно в одном файле создать таблицы, связанные между собой формулами. Каждый лист можно переименовать нажав правой кнопкой мыши на листе и выбрать команду Переименовать или дважды щелкнуть на листе левой кнопкой мыши. После ввода имени листа нажмите Enter.

Добавление и копирование листов

Добавить новый лист в рабочую книгу можно несколькими способами.
Самый быстрый это комбинация клавиш [Shift + F11].
Так же можно щелкнуть правой кнопкой мыши на листе и в появившемся меню выбрать команду Вставить — Лист.
Новые листы всегда добавляются слева от активного листа, но их можно перетащить в любое удобное место. Для этого нажав на листе левую кнопку мыши, не отпуская ее тащить лист в нужную сторону.
Листы точно так же можно и удалять, но важно помнить, что если на листе есть данные связанные формулами, они считаться не будут.
Рабочий лист можно так же скопировать. Для этого нужно на ярлычке листа правой кнопкой мыши и в появившемся меню выбрать команду Переместить/скопировать. В появившемся меню поставить галочку Создать копию, выбрать, куда скопируется новый лист и нажать ОК.

Вставка и удаление строк и столбцов

Для того чтобы вставить строку или столбец, сначала их надо выделить. Для того чтобы выделить столбец нужно мышкой щелкнуть на букве заголовка столбца или нажать комбинацию клавиш [Ctrl + пробел]. Для выделения строки нужно мышкой щелкнуть на номере заголовка строки или нажать [Shift + пробел].
Далее щелкнуть правой кнопкой мыши, для вызова контекстного меню и выбрать команду Вставить или Удалить.

Скрытие строк и столбцов

Иногда требуется убрать на время какие-либо столбцы и строки, но в них может содержаться информация, связанная с другими ячейками. Для того чтобы скрыть столбцы или строки нужно выделить их в контекстном меню выбрать команду Скрыть. Когда информация снова понадобится выбрать команду Отобразить.

Группировка строк и столбцов

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

После этого сгруппированные строки или столбцы легко открыть и закрыть [+,-].

Изменение размеров строк и столбцов

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

Если нужно чтобы все столбцы или строки имели одинаковый размер нужно их выделить и курсором мышки установить нужный размер.

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

Меню формата ячеек можно также вызвать клавишами [Ctrl + 1], или через меню ленты на вкладке Главная в группе Число.

В появившемся окне необходимо выбрать нужный формат ячеек.
Общий – ячейки могут содержать как текстовую, так и цифровую информацию.
Числовой – для цифровой информации.
Денежный – для отображения денежных величин в заданной валюте.
Финансовый – для отображения денежных величин с выравниванием по разделителю и дробной части.
Дата
Время
Процентный
Дробный
Экспоненциальный
Текстовый
Дополнительный – используется для составления базы данных или списка адресов, для ввода индексов, номеров телефонов, табельных номеров.
Все форматы – дополнительные форматы. Можно выбрать из предложенных или ввести свой в поле Тип.
Пользовательский формат

Все типы данных условно можно разделить на три группы:
Числовые
Текстовые
Формулы.

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

Текстовые данные Excel будут пониматься как текст. С текстовыми данными нельзя производить расчеты.

Формулы – тип данных, который Excel воспринимает как команды выполнить расчеты в ячейке. Формулы всегда начинаются со знака «=».При введении формула будет отображаться в строке формул и в ячейке. При нажатии на клавишу Enter в строке будет выводится формула, а в ячейке будет отображаться ее результат.

Ввод данных

Для ввода данных щелкните по необходимой ячейке и начинайте ввод в «тело» ячейки. Завершив ввод данных необходимо их зафиксировать, нажав клавишу Enter, Tab или щелкнув мышкой на другой ячейке.
По умолчанию текст всегда выравнивается по левому краю, а числа по правому.
При вводе данных в строке формул появятся три кнопки:
Отмена
Ввод
Вставить функцию

Выделение ячеек (Диапазон)

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

Объединение ячеек

При оформлении таблицы бывает необходимо объединить несколько ячеек. Для этого необходимо выделить диапазон ячеек и нажать на кнопку [Объединить и поместить в центре] в меню Главная.

Далее можно форматировать данные на свое усмотрение.

Автозаполнение

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

После этого на последней ячейке появятся параметры автозаполнения.

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

Добавление примечаний к ячейке

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

Примечания можно так же изменять и удалять.

Рабочее поле Excel состоит из прямоугольных ячеек.
Ячейки, объединенные по вертикали, называют столбцами. Столбцы обозначаются буквами латинского алфавита A, B, C… Z, AA, AB и т.д.

 

Ячейки, объединенные по горизонтали, называют строками. Строки пронумерованы целыми числами 1, 2, 3 …

Таблица, образованная строками и столбцами составляет рабочий лист.
Ячейка, которая в данный момент выделена, называется активной. Она всегда окружена темной линией. Чтобы перейти в другую ячейку, достаточно щелкнуть по ней мышкой.
Каждая отдельная ячейка имеет свой собственный адрес, который состоит из буквы столбца и номера строки. Адрес активной ячейки будет отображаться в поле имени. В строке формул будут отображаться данные активной ячейки.

Для просмотра всего листа можно воспользоваться полосами прокрутки. Если активная ячейка находится за пределами листа, найти ее можно с помощью комбинации клавиш [Ctrl + Backspase].
Файлы Excel называются рабочими книгами. По умолчанию Excel запускается с тремя рабочими листами, которые сохраняются в одном файле. Листы можно добавлять, удалять или переименовывать на свое усмотрение. Благодаря такой структуре в одной рабочей книге можно разместить связанные между собой таблицы и быстро переключаться между ними с помощью мышки.

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

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