Редактирование рабочего листа

Функции в формулах

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

Функция − составная часть формулы; в частном случае формула может состоять из одной функции.

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

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

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

Аргументами функций могут быть числа, текст, логические значения, массивы, ссылки, формулы, функции.

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

Ввод функций

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

Это окно можно вывести на экран несколькими способами:

I способ:

1. На вкладке Формулы открыть список Библиотека функций.

2. В открывшемся списке выбрать категорию, к которой относится нужная функция.

3. В открывшемся списке функций выбрать нужную.

II способ:

1. На вкладке Формулы открыть список Библиотека функций.

2. Щелкнуть по кнопке Вставить функцию. Откроется диалоговое окно Мастера функций.

3. В поле списка Категория выбрать категорию, к которой относится нужная функция.

4. В поле списка Функция выбрать имя функции и щелкнуть по кнопке ОК.

III способ: щелкнуть по кнопке Вставить функцию, которая находится левее строки формул, после этого откроется диалоговое окно Мастера функций, в котором надо выбрать категорию и функцию.

IV способ: нажать клавиши Shift + F3 и в открывшемся окне Мастера функций выбрать нужную функцию.

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

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

После ввода каждого аргумента справа от поля отображается его значение.

В средней части окна выводится текст, объясняющий назначение функции и смысл её аргументов.

В нижней части окна отображается значение функции, а также дается ссылка для вызова справки для данной функции.

После ввода всех аргументов щелкнуть по кнопке ОК.

Автозаполнение формул

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

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

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

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

Для продолжения ввода данных во внешнюю функцию надо щелкнуть мышью по ее имени в строке формул.

Редактирование функций

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

- на вкладке Формулы открыть список Библиотека функций, затем щелкнуть по кнопке Вставить функцию;

- щелкнуть по кнопке Вставка функции в строке формул;

- нажать клавиши Shift + F3.

После этого откроется диалоговое окноАргументы функции, в которое можно внести нужные изменения.

Автосуммирование

Для суммирования содержимого ячеек предназначена функция СУММ. Синтаксис функции:

СУММ(аргументы)

Функция СУММ может иметь до 255 аргументов. Аргументами могут быть отдельные ячейки, диапазоны ячеек, в том числе, несмежные, строки, столбцы, а также значения.

Для суммирования содержимого строки или столбца в Excel имеется инструмент автосуммирования, упрощающий работу с этой функцией.

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

- на вкладке Главная в группе Редактирование выбрать команду Сумма;

- на вкладке Формулы в группе Библиотека функций выбрать команду Автосумма;

- нажать комбинацию клавиш Alt + =.

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

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

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

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

Кроме суммы кнопка Автосумма содержит список наиболее часто используемых статистических функций:Среднее,Число,Максимум,Минимум.

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

Редактирование рабочего листа

Редактирование в ячейке

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

Для редактирования внутри ячейки надо дважды щелкнуть по ней или нажать клавишу F2. В ячейке появится курсор ввода.

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

Для копирования или перемещения выделенного фрагмента данных из ячейки надо:

1. Скопировать или переместить выделенный фрагмент в буфер обмена одним из следующих способов:

- на вкладке Главная в группе Буфер обмена выбрать команды Копировать(для копирования) или Вырезать(для перемещения);

- в контекстном меню выделенного фрагмента выбрать команды Копировать или Вырезать;

- нажать клавиши Ctrl+ C – для копирования, Ctrl + X – для перемещения.

2. Установить курсор в место вставки фрагмента.

3. Выполнить вставку фрагмента одним из следующих способов:

- на вкладке Главная в группе Буфер обмена выбрать команду Вставить;

- выбрать команду Вставитьв контекстном меню ячейки;

- нажать клавиши Ctrl+ V.

Для удаления выделенных символов из ячейки без сохранения в буфере обмена на вкладке Главная в группе Редактирование выбрать команду Очистить или нажать клавишу Del.

Очистка ячеек

При очистке ячеек их содержимое удаляется, а окружающие их ячейки остаются на прежних местах.

1. Выделить ячейки, которые нужно очистить.

2. Выполнить одно из следующих действий:

- на вкладке Главная в группе Редактирование выбрать команду Очистить;

- в контекстном меню ячеек выбрать команду Очистить содержимое;

- нажать клавишу Del.

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

Удаление ячеек, строк и столбцов

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

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

2. Выполнить одно из следующих действий:

- на вкладке Главная в группе Ячейки в списке Удалить выбрать команду Удалить ячейки;

- в контекстном меню ячеек выбрать команду Удалить:

- нажать клавиши Ctrl + ‑.

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

Удалить строки или столбцы можно еще одним способом:

1. Выделить любую ячейку в строке (столбце), которую нужно удалить.

2. На вкладке Главная в группе Ячейки в списке Удалить выбрать команду Удалить строки с листа (Удалить столбцы с листа).

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

Вставка пустых ячеек, строк и столбцов

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

Для вставки ячеек нужно:

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

2. Выполнить одно из следующих действий:

- на вкладке Главная в группе Ячейки в списке Вставить выбрать команду Вставить ячейки;

- в контекстном меню ячеек выбрать команду Вставить….

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

Вставить строки или столбцы можно еще одним способом:

1. Выделить любую ячейку в строке (столбце), которую нужно удалить.

2. На вкладке Главная в группе Ячейки в списке Вставить выбрать команду Вставить строки на лист (Вставить столбцы на лист).

Копирование и перемещение ячеек с помощью буфера обмена

Ячейки сначала перемещаются или копируются в буфер обмена, а затем из буфера обмена вставляются в другое место этого же листа, в другой рабочий лист или в другую рабочую книгу (файл).

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

Копирование и перемещение ячеек поверх существующих

1. Выделить ячейки, которые нужно скопировать или переместить.

2. Скопировать или переместить выделенные ячейки в буфер обмена одним из следующих способов:

- на вкладке Главная в группе Буфер обмена выбрать команды Копировать(для копирования) или Вырезать(для перемещения);

- в контекстном меню выделенных ячеек выбрать команды Копировать или Вырезать;

- нажать клавиши Ctrl+ C – для копирования, Ctrl + X – для перемещения.

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

4. Выполнить вставку фрагмента одним из следующих способов:

- на вкладке Главная в группе Буфер обмена выбрать команду Вставить;

- выбрать команду Вставитьв контекстном меню ячейки;

- нажать клавиши Ctrl+ V.

Копирование и перемещение ячеек среди существующих

Этот случай отличается от предыдущего только операцией вставки ячеек (п. 4). Для вставки ячеек с раздвижкой надо выполнить одно из следующих действий:

- на вкладке Главная в группе Ячейки в списке Вставить выбрать команду Вставить скопированные ячейки (Вставить вырезанные ячейки);

- в контекстном меню области вставки выбрать команду Вставить скопированные ячейки (Вставить вырезанные ячейки).

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

Область задачБуфер обмена

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

Чтобы воспользоваться этой возможностью, надо вывести на экран область задач Буфер обмена, для чего на вкладке Главная в группе Буфер обмена щелкнуть по пиктограмме диалогового окна (кнопка с маленькой стрелкой, направленной по диагонали).

Если включен соответствующий режим, буфер обмена можно вывести на экран двойным нажатием комбинации клавиш Ctrl + C (флажок для включения этого режима находится в списке кнопки Параметры в буфере обмена).

Чтобы вставить фрагмент из буфера обмена, надо щелкнуть по его пиктограмме.

Чтобы удалить фрагмент из буфера обмена, надо подвести указатель мыши к этому фрагменту, щелкнуть по кнопке раскрытия списка и выбрать вариант Удалить (эта операция необратима).

Кроме того, в области задач Буфер обмена имеются кнопки Вставить все для вставки в документ всего содержимого буфера обмена и Очистить все для удаления всей информации из буфера обмена (эта операция также необратима).

Копирование и перемещение ячеек с помощью мыши

Использовать мышь для копирования и перемещения ячеек возможно, если включен режим Разрешить маркеры заполнения и перетаскивания ячеек на вкладке Дополнительно диалогового окна кнопки Параметры Excel, расположенной в окне кнопки Office.

Чтобы переместить ячейки поверх существующих, следует:

1. Выделить нужные ячейки.

2. Установить указатель мыши на рамку выделения.

3. Перетащить рамку в нужное место.

Чтобы вставить ячейки с раздвижкой существующих ячеек, надо при перетаскивании удерживать клавишу Shift.

Чтобы скопировать ячейки со вставкой поверх существующих, надо перетаскивать их мышью, удерживая клавишу Ctrl.

Чтобы скопировать ячейки с раздвижкой существующих, надо при перетаскивании удерживать клавиши Ctrl+ Shift.

Замечание: клавиши нажимаются раньше, чем кнопка мыши, а отпускаются позднее.

Копирование и перемещение ячеек с помощью правой кнопки мыши

Еще один способ копирования и перетаскивания ячеек:

1. Выделить нужные ячейки.

2. Установить указатель мыши на рамку выделения.

3. Перетащить рамку в нужное место с помощью правой кнопки мыши.

4. В открывшемся контекстном меню выбрать нужную команду:

- для вставки поверх существующих: Копировать, Переместить, Копировать только форматы или Копировать только значения;

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

Специальная вставка

В Microsoft Excel предусмотрена операция специальной вставки, позволяющая:

- скопировать не все содержимое ячейки, а отдельную его составляющую (например, возвращаемое формулой значение без копирования самой формулы, форматы ячейки);

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

- транспонировать блок ячеек и др.

Специальную вставку можно выполнить одним из следующих способов:

- на вкладке Главная в группе Буфер обмена в списке Вставить выбрать команду Специальная вставка;

- в контекстном меню ячейки, в которую производится копирование, выбрать команду Специальная вставка;

- выбрать нужную операцию из раскрывающегося списка кнопки Вставка на вкладке Главная в группе Буфер обмена;

- воспользоваться смарт-тегом Параметры вставки, появляющимся при вставке из буфера обмена.

Изменение ссылок в формулах при удалении, перемещении и копировании ячеек

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

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

Поиск и замена текста и чисел

Чтобы найти на рабочем листе ячейки, содержащие заданные значения (текст или числа), надо:

1. На вкладке Главная в группе Редактирование в списке кнопки Найти и выделить выбрать команду Найти или нажать клавиши Ctrl + F.

2. В открывшемся диалоговом окне в поле Найти ввести искомое значение. При этом можно использовать символы «?» (обозначает один любой символ) и «*» (обозначает последовательность любых символов).

3. Для задания дополнительных условий поиска щелкнуть по кнопке Параметры.

4. В списке Искать выбрать место поиска: на листе или в книге.

5. В списке Просматривать выбрать порядок просмотра: по строкам или по столбцам.

6. В списке Область поиска выбрать тип искомых данных: формулы, значения или примечания. Если выбрать вариант формулы и при этом оставить пустым поле Найти, буду найдены все ячейки, содержащие формулы.

7. Чтобы при поиске различать строчные и прописные буквы, включить режим Учитывать регистр.

8. Если искомое значение должно составлять все содержимое ячейки, включить режим Ячейка целиком, если искомое значение может быть частью содержимого ячейки, выключить этот режим.

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

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

11. Чтобы вывести в диалоговом окне список всех найденных ячеек, щелкнуть по кнопке Найти все.

Для поиска на рабочем листе ячеек, содержащих заданные значения (текст или числа) и замены их другими значениями, надо:

1. На вкладке Главная в группе Редактирование в списке кнопки Найти и выделить выбрать команду Заменитьили нажать клавиши Ctrl + H.

2. В открывшемся диалоговом окне в поле Найти ввести значение, которое подлежит замене.

3. В поле Заменить ввести значение, на которое надо заменить искомое значение.

4. При необходимости задать характеристики формата заменяемого и заменяющего значений с помощью соответствующих кнопок Формат. Если при этом поля Найти и Заменить оставить пустыми, будет выполнена замена форматов.

5. Установить параметры поиска и замены (аналогично параметрам поиска).

6. Для замены с подтверждением щелкнуть по кнопке Найти далее. Будет выделена первая ячейка с искомым значением. Для замены данных в этой ячейке щелкнуть по кнопке Заменить. Если в этой ячейке замену производить не надо, снова щелкнуть по кнопке Найти далее. Повторить эти действия для каждой найденной ячейки.

Для автоматической замены заданного значения во всех ячейках щелкнуть по кнопке Заменить все.

Чтобы выполнить операцию поиска и замены на нескольких листах рабочей книги, надо выделить эти листы.