Гдз. Лабораторная работа 6 MS Excel-1. 1. Функции в формулах
 Скачать 0.52 Mb.
|
|
1. Функции в формулах Функция – это имеющая имя, заранее созданная формула, которая выполняет операции над заданными значениями и возвращает одно или несколько значений. Функция − составная часть формулы; в частном случае формула может состоять из одной функции. Значения, над которыми функции выполняют операции, называются аргументами. Последовательность символов, с помощью которых вводится формула, называется синтаксисом. Все функции имеют одинаковый основной синтаксис: сначала идет имя функции, затем в круглых скобках список аргументов, разделенных точками с запятой (или иным символом, определенным как разделитель элементов списка). Аргументами функций могут быть числа, текст, логические значения, массивы, ссылки, формулы, функции. Некоторые функции не имеют аргументов, круглые скобки тем не менее сохраняются, например, функция СЕГОДНЯ() возвращает текущую дату. Ввод функций Функции можно вводить в формулу с клавиатуры, но удобнее вставлять функции в формулу, используя диалоговое окно Аргументы функции. Это окно можно вывести на экран несколькими способами: I способ: 1. На вкладке Формулы открыть список Библиотека функций. 2. В открывшемся списке выбрать категорию, к которой относится нужная функция. 3. В открывшемся списке функций выбрать нужную. II способ: 1. На вкладке Формулы открыть список Библиотека функций. 2. Щелкнуть по кнопке Вставить функцию. Откроется диалоговое окно Мастера функций. 3. В поле списка Категория выбрать категорию, к которой относится нужная функция. 4. В поле списка Функция выбрать имя функции и щелкнуть по кнопке ОК. III способ: щелкнуть по кнопке Вставить функцию, которая находится левее строки формул, после этого откроется диалоговое окно Мастера функций, в котором надо выбрать категорию и функцию. IV способ: нажать клавиши Shift + F3 и в открывшемся окне Мастера функций выбрать нужную функцию. 2 После любого из этих действий откроется диалоговое окно Аргументы функции. В этом окне имеется отдельное поле для каждого аргумента функции. Если в какое-либо поле необходимо ввести формулу или указать интервал ячеек, можно щелкнуть по кнопке свертывания окна, расположенной справа от этого поля, чтобы временно свернуть диалоговое окно и перейти в таблицу (диалоговое окно свернется до размеров этого поля). Тогда можно вводить формулу, указывая ячейки щелчком мыши, или с помощью мыши выделить нужный интервал ячеек. Для восстановления диалогового окна надо снова щелкнуть по этой кнопке. После ввода каждого аргумента справа от поля отображается его значение. В средней части окна выводится текст, объясняющий назначение функции и смысл её аргументов. В нижней части окна отображается значение функции, а также дается ссылка для вызова справки для данной функции. После ввода всех аргументов щелкнуть по кнопке ОК. Автозаполнение формул При вводе формулы с клавиатуры отображаются списки функций и имен диапазонов ячеек, названия которых совпадают с уже введенными буквами. Для выбора нужного названия из такого списка и вставки его в формулу надо с помощью клавиши со стрелкой вниз выделить нужную строку и нажать клавишу Tab. После выбора функции появляется всплывающая подсказка с характеристикой её аргументов. Вложенные функции Если аргумент функции сам является функцией, то такая функция называется вложенной, а формула называется формулой с двумя уровнями вложенных функций. Microsoft Excel допускает до семи уровней вложения функций в одной формуле. Чтобы в поле диалогового окна Аргументы функции в качестве аргумента ввести функцию, надо щелкнуть мышью по этому полю, а затем – по кнопке раскрытия списка функций, расположенного слева в строке формул. После выбора из этого списка нужной функции откроется диалоговое окно для ввода аргументов вложенной функции. Для продолжения ввода данных во внешнюю функцию надо щелкнуть мышью по ее имени в строке формул. Редактирование функций Чтобы отредактировать функцию в существующей формуле, надо выделить ячейку с формулой, а затем выполнить одно из трех действий: на вкладке Формулы открыть список Библиотека функций, затем щелкнуть по кнопке Вставить функцию; 3 щелкнуть по кнопке Вставка функции в строке формул; нажать клавиши Shift + F3. После этого откроется диалоговое окно Аргументы функции, в которое можно внести нужные изменения. Автосуммирование Для суммирования содержимого ячеек предназначена функция СУММ. Синтаксис функции: СУММ(аргументы) Функция СУММ может иметь до 255 аргументов. Аргументами могут быть отдельные ячейки, диапазоны ячеек, в том числе, несмежные, строки, столбцы, а также значения. Для суммирования содержимого строки или столбца в Excel имеется инструмент автосуммирования, упрощающий работу с этой функцией. Для ввода функции суммирования надо выделить ячейку, прилегающую к строке или столбцу чисел, которые нужно сложить, а затем выполнить одно из следующих действий: на вкладке Главная в группе Редактирование выбрать команду Сумма; на вкладке Формулы в группе Библиотека функций выбрать команду Автосумма; нажать комбинацию клавиш Alt + =. Чтобы ввести сразу несколько функций суммирования, надо выделить интервал ячеек, прилегающий к строкам или столбцам, содержащим значения, которые надо сложить, а затем вызвать функцию автосуммирования. Можно выделять не те ячейки, куда надо ввести формулу, а те, содержимое которых надо сложить. В этом случае после вызова функции автосуммирования формулы будут вставлены в первую пустую строку после выделенного интервала. Чтобы вставить функции суммирования одновременно в строки и столбцы, надо выделить интервал ячеек, содержащих значения, которые нужно просуммировать, и пустые ячейки, в которые надо поместить формулы, а затем вызвать функцию автосуммирования. Если выделенный интервал ячеек содержит строки или столбцы с промежуточными итогами, то будут просуммированы только промежуточные итоги (без учета остальных значений блока). Кроме суммы кнопка Автосумма содержит список наиболее часто используемых статистических функций: Среднее, Число, Максимум, Минимум. Для ввода этих функций используются те же приемы, что и для функции суммирования. 4 2. Редактирование рабочего листа Редактирование в ячейке Редактировать данные в ячейке можно прямо в ячейке или в строке формул. Чтобы редактировать прямо в ячейке, надо включить режим Разрешить редактирование в ячейках на вкладке Дополнительно диалогового окна кнопки Параметры Excel, которая находится в окне кнопки Office. Для редактирования внутри ячейки надо дважды щелкнуть по ней или нажать клавишу F2. В ячейке появится курсор ввода. Чтобы выделить символы в ячейке, надо протащить указатель мыши через эти символы. Для копирования или перемещения выделенного фрагмента данных из ячейки надо: 1. Скопировать или переместить выделенный фрагмент в буфер обмена одним из следующих способов: на вкладке Главная в группе Буфер обмена выбрать команды Копировать(для копирования) или Вырезать(для перемещения); в контекстном меню выделенного фрагмента выбрать команды Копировать или Вырезать; нажать клавиши Ctrl + C – для копирования, Ctrl + X – для перемещения. 2. Установить курсор в место вставки фрагмента. 3. Выполнить вставку фрагмента одним из следующих способов: на вкладке Главная в группе Буфер обмена выбрать команду Вставить; выбрать команду Вставить в контекстном меню ячейки; нажать клавиши Ctrl + V. Для удаления выделенных символов из ячейки без сохранения в буфере обмена на вкладке Главная в группе Редактирование выбрать команду Очистить или нажать клавишу Del. Очистка ячеек При очистке ячеек их содержимое удаляется, а окружающие их ячейки остаются на прежних местах. 1. Выделить ячейки, которые нужно очистить. 2. Выполнить одно из следующих действий: на вкладке Главная в группе Редактирование выбрать команду Очистить; в контекстном меню ячеек выбрать команду Очистить содержимое; нажать клавишу Del. При выборе команды Очистить на вкладке Главная появляются дополнительные возможности: можно удалить все данные полностью, выбрав вариант Очистить все, или удалить только содержимое, только форматы или только примечания, выбрав соответствующий вариант. 5 Удаление ячеек, строк и столбцов При удалении ячейки устраняются, а окружающие их ячейки смыкаются, заполняя освободившееся пространство. 1. Выделить диапазон ячеек, которые нужно удалить. Для удаления строки или столбца выделить любую ячейку из удаляемой строки или столбца. 2. Выполнить одно из следующих действий: на вкладке Главная в группе Ячейки в списке Удалить выбрать команду Удалить ячейки; в контекстном меню ячеек выбрать команду Удалить: нажать клавиши Ctrl + -. 3. В диалоговом окне выбрать направление, в котором должны переместиться окружающие ячейки (ячейки со сдвигом влево или ячейки со сдвигом вверх). Для удаления строки или столбца выбрать вариант строку или столбец. Удалить строки или столбцы можно еще одним способом: 1. Выделить любую ячейку в строке (столбце), которую нужно удалить. 2. На вкладке Главная в группе Ячейки в списке Удалить выбрать команду Удалить строки с листа (Удалить столбцы с листа). Можно удалить сразу несколько столбцов: для этого надо выделить по одной ячейке в каждом из удаляемых столбцов. Аналогично, для удаления сразу нескольких строк надо выделить по одной ячейке из каждой удаляемой строки. Вставка пустых ячеек, строк и столбцов При вставке пустых ячеек, строк и столбцов существующие ячейки раздвигаются, освобождая место для новых. Для вставки ячеек нужно: 1. В том месте таблицы, куда надо вставить пустые ячейки, выделить столько ячеек, строк или столбцов, сколько нужно вставить пустых. При этом следует учитывать, что при вставке строк все строки таблицы, начиная с выделенных, сдвинутся вниз, при вставке столбцов – все столбцы, начиная с выделенных, сдвинутся вправо, при вставке блока ячеек возможны варианты. 2. Выполнить одно из следующих действий: на вкладке Главная в группе Ячейки в списке Вставить выбрать команду Вставить ячейки; в контекстном меню ячеек выбрать команду Вставить…. 3. В диалоговом окне выбрать направление, в котором должны переместиться окружающие ячейки (ячейки со сдвигом вправо или ячейки со сдвигом вниз). Для удаления строки или столбца выбрать вариант строку или столбец. Вставить строки или столбцы можно еще одним способом: 1. Выделить любую ячейку в строке (столбце), которую нужно удалить. 6 2. На вкладке Главная в группе Ячейки в списке Вставить выбрать команду Вставить строки на лист (Вставить столбцы на лист). Копирование и перемещение ячеек с помощью буфера обмена Ячейки сначала перемещаются или копируются в буфер обмена, а затем из буфера обмена вставляются в другое место этого же листа, в другой рабочий лист или в другую рабочую книгу (файл). Копируемые или перемещаемые ячейки можно либо вставить среди существующих, либо поместить поверх них. В первом случае происходит раздвижка ячеек, во втором − замещение содержимого существующих ячеек. Копирование и перемещение ячеек поверх существующих 1. Выделить ячейки, которые нужно скопировать или переместить. 2. Скопировать или переместить выделенные ячейки в буфер обмена одним из следующих способов: на вкладке Главная в группе Буфер обмена выбрать команды Копировать(для копирования) или Вырезать(для перемещения); в контекстном меню выделенных ячеек выбрать команды Копировать или Вырезать; нажать клавиши Ctrl + C – для копирования, Ctrl + X – для перемещения. 3. Выделить ячейку, расположенную в левом верхнем углу области вставки (область вставки – диапазон, куда вставляются копируемые или перемещаемые ячейки). 4. Выполнить вставку фрагмента одним из следующих способов: на вкладке Главная в группе Буфер обмена выбрать команду Вставить; выбрать команду Вставить в контекстном меню ячейки; нажать клавиши Ctrl + V. Копирование и перемещение ячеек среди существующих Этот случай отличается от предыдущего только операцией вставки ячеек (п. 4). Для вставки ячеек с раздвижкой надо выполнить одно из следующих действий: на вкладке Главная в группе Ячейки в списке Вставить выбрать команду Вставить скопированные ячейки (Вставить вырезанные ячейки); в контекстном меню области вставки выбрать команду Вставить скопированные ячейки (Вставить вырезанные ячейки). Затем в открывшемся диалоговом окне выбрать направление сдвига окружающих ячеек. Область задачБуфер обмена В версии Microsoft Excel 2007 можно выполнить вставку любого из последних 24 элементов, помещенных в буфер обмена. Чтобы воспользоваться этой возможностью, надо вывести на экран область задач Буфер обмена, для чего на вкладке Главная в 7 группе Буфер обмена щелкнуть по пиктограмме диалогового окна (кнопка с маленькой стрелкой, направленной по диагонали). Если включен соответствующий режим, буфер обмена можно вывести на экран двойным нажатием комбинации клавиш Ctrl + C (флажок для включения этого режима находится в списке кнопки Параметры в буфере обмена). Чтобы вставить фрагмент из буфера обмена, надо щелкнуть по его пиктограмме. Чтобы удалить фрагмент из буфера обмена, надо подвести указатель мыши к этому фрагменту, щелкнуть по кнопке раскрытия списка и выбрать вариант Удалить (эта операция необратима). Кроме того, в области задач Буфер обмена имеются кнопки Вставить все для вставки в документ всего содержимого буфера обмена и Очистить все для удаления всей информации из буфера обмена (эта операция также необратима). Копирование и перемещение ячеек с помощью мыши Использовать мышь для копирования и перемещения ячеек возможно, если включен режим Разрешить маркеры заполнения и перетаскивания ячеек на вкладке Дополнительно диалогового окна кнопки Параметры Excel, расположенной в окне кнопки Office. Чтобы переместить ячейки поверх существующих, следует: 1. Выделить нужные ячейки. 2. Установить указатель мыши на рамку выделения. 3. Перетащить рамку в нужное место. Чтобы вставить ячейки с раздвижкой существующих ячеек, надо при перетаскивании удерживать клавишу Shift. Чтобы скопировать ячейки со вставкой поверх существующих, надо перетаскивать их мышью, удерживая клавишу Ctrl. Чтобы скопировать ячейки с раздвижкой существующих, надо при перетаскивании удерживать клавиши Ctrl + Shift. Замечание: клавиши нажимаются раньше, чем кнопка мыши, а отпускаются позднее. Копирование и перемещение ячеек с помощью правой кнопки мыши Еще один способ копирования и перетаскивания ячеек: 1. Выделить нужные ячейки. 2. Установить указатель мыши на рамку выделения. 3. Перетащить рамку в нужное место с помощью правой кнопки мыши. 4. В открывшемся контекстном меню выбрать нужную команду: для вставки поверх существующих: Копировать, Переместить, Копировать только форматы или Копировать только значения; для вставки с раздвижкой существующих ячеек: Сдвинуть вниз и скопировать, Сдвинуть вправо и скопировать, Сдвинуть вниз и переместить или Сдвинуть вправо и переместить. 8 Специальная вставка В Microsoft Excel предусмотрена операция специальной вставки, позволяющая: скопировать не все содержимое ячейки, а отдельную его составляющую (например, возвращаемое формулой значение без копирования самой формулы, форматы ячейки); выполнить арифметические операции с копируемыми данными и содержимым ячейки, в которую производится копирование; транспонировать блок ячеек и др. Специальную вставку можно выполнить одним из следующих способов: на вкладке Главная в группе Буфер обмена в списке Вставить выбрать команду Специальная вставка; в контекстном меню ячейки, в которую производится копирование, выбрать команду Специальная вставка; выбрать нужную операцию из раскрывающегося списка кнопки Вставка на вкладке Главная в группе Буфер обмена; воспользоваться смарт-тегом Параметры вставки, появляющимся при вставке из буфера обмена. Изменение ссылок в формулах при удалении, перемещении и копировании ячеек При копировании ячеек изменение координат относительной ссылки равно разности между координатами ячейки-получателя и ячейки-источника. При этом относительное расположение ссылки и ячейки, в которую введена формула, остается неизменным. Абсолютные ссылки при копировании ячеек не меняют свои координаты. При перемещении ячеек ссылки на перемещенные ячейки во всех формулах таблицы меняют свои координаты на новые. То же самое происходит при перемещении ячеек, являющемся результатом удаления или вставки ячеек. Поиск и замена текста и чисел Чтобы найти на рабочем листе ячейки, содержащие заданные значения (текст или числа), надо: 1. На вкладке Главная в группе Редактирование в списке кнопки Найти и выделить выбрать команду Найти или нажать клавиши Ctrl + F. 2. В открывшемся диалоговом окне в поле Найти ввести искомое значение. При этом можно использовать символы «?» (обозначает один любой символ) и «*» (обозначает последовательность любых символов). 3. Для задания дополнительных условий поиска щелкнуть по кнопке Параметры. 4. В списке Искать выбрать место поиска: на листе или в книге. 9 5. В списке Просматривать выбрать порядок просмотра: по строкам или по столбцам. 6. В списке Область поиска выбрать тип искомых данных: формулы, значения или примечания. Если выбрать вариант формулы и при этом оставить пустым поле Найти, буду найдены все ячейки, содержащие формулы. 7. Чтобы при поиске различать строчные и прописные буквы, включить режим Учитывать регистр. 8. Если искомое значение должно составлять все содержимое ячейки, включить режим Ячейка целиком, если искомое значение может быть частью содержимого ячейки, выключить этот режим. 9. Если требуется найти данные, которые также имеют конкретное форматирование, щелкнуть по кнопке Формат задать характеристики формата. Если при этом оставить пустым поле Найти, будут найдены все ячейки, имеющие заданный формат. 10. Чтобы выделить первую найденную ячейку, щелкнуть по кнопке Найти далее. При повторных щелчках по этой кнопке будут последовательно выделяться ячейки, удовлетворяющие заданным условиям. 11. Чтобы вывести в диалоговом окне список всех найденных ячеек, щелкнуть по кнопке Найти все. Для поиска на рабочем листе ячеек, содержащих заданные значения (текст или числа) и замены их другими значениями, надо: 1. На вкладке Главная в группе Редактирование в списке кнопки Найти и выделить выбрать команду Заменить или нажать клавиши Ctrl + H. 2. В открывшемся диалоговом окне в поле Найти ввести значение, которое подлежит замене. 3. В поле Заменить ввести значение, на которое надо заменить искомое значение. 4. При необходимости задать характеристики формата заменяемого и заменяющего значений с помощью соответствующих кнопок Формат. Если при этом поля Найти и Заменить оставить пустыми, будет выполнена замена форматов. 5. Установить параметры поиска и замены (аналогично параметрам поиска). 6. Для замены с подтверждением щелкнуть по кнопке Найти далее. Будет выделена первая ячейка с искомым значением. Для замены данных в этой ячейке щелкнуть по кнопке Заменить. Если в этой ячейке замену производить не надо, снова щелкнуть по кнопке Найти далее. Повторить эти действия для каждой найденной ячейки. Для автоматической замены заданного значения во всех ячейках щелкнуть по кнопке Заменить все. Чтобы выполнить операцию поиска и замены на нескольких листах рабочей книги, надо выделить эти листы. 10 3. Форматирование рабочего листа Инструменты форматирования Excel Инструменты форматирования имеются на вкладке Главная; в диалоговом окне Формат ячеек. на мини-панелях контекстных меню ячеек; На вкладке Главная расположены наиболее часто используемые команды для форматирования ячеек. Они находятся в группах Шрифт, Выравнивание, Число, Стили, Ячейки. Диалоговое окно Формат ячеек можно вывести на экран одним из следующих способов: щелкнуть мышью по кнопке открытия диалогового окна в группах Шрифт, Выравнивание или Число; выбрать команду Формат ячеек в контекстном меню ячейки; нажать клавиши Ctrl + 1. Мини-панель вместе с контекстным меню появляется после щелчка правой кнопкой мыши по ячейке. Перед назначением формата надо выделить тот диапазон ячеек, который необходимо отформатировать. Ширина столбца Изменить ширину столбца можно одним из следующих способов: на вкладке Главная в группе Ячейки в списке Формат выбрать команду Ширина столбца и в открывшемся диалоговом окне ввести числовое значение ширины столбца (единица измерения – количество символов); перетащить мышью правую рамку заголовка столбца. Чтобы настроить столбец на автоматический подбор ширины по самой длинной записи, надо выделить любую ячейку этого столбца и на вкладке Главная в группе Ячейки в списке Формат выбрать команду Автоподбор ширины столбца. Другой способ – дважды щелкнуть мышью по правой рамке заголовка столбца. Высота строки Изменить высоту строки можно одним из следующих способов: на вкладке Главная в группе Ячейки в списке Формат выбрать команду Высота строки и в открывшемся диалоговом окне ввести числовое значение высоты строки (единица измерения – пункт); перетащить мышью нижнюю рамку заголовка строки. Чтобы настроить строку на автоматический подбор высоты под самый крупный шрифт, надо выделить любую ячейку этой строки и на вкладке Главная в группе Ячейки в списке Формат выбрать команду Автоподбор высоты строки. Другой способ – дважды щелкнуть мышью по нижней рамке заголовка строки. Выравнивание чисел и текста в ячейках Первоначально все ячейки имеют основной формат, в котором числа выравниваются по правому краю, а текст – по левому. 11 Команды для изменения выравнивания содержимого ячеек располагаются: на вкладке Выравнивание диалогового окна Формат ячеек; в группе Выравнивание на ленточной вкладке Главная. Выравнивание по горизонтали определяет расположение содержимого ячейки относительно ее горизонтальных границ. Вертикальное выравнивание определяет расположение содержимого ячейки относительно ее вертикальных границ. Изменение ориентации текста Текст в ячейке может располагаться горизонтально, вертикально и под любым углом. Изменить ориентацию текста можно одним из следующих способов: на вкладке Выравнивание диалогового окна Формат ячеек в поле Ориентация задать угол в диапазоне от -90° до +90°; на вкладке Выравнивание диалогового окна Формат ячеек в поле Ориентация перетащить указатель на нужный угол; на вкладке Главная в группе Выравнивание в списке Ориентация выбрать нужный вариант поворота (если угол кратен 45°). Расположение текста в ячейке в несколько строк Если текст не помещается в ячейке по ширине, можно расположить его в несколько строк одним из способов: . на вкладке Выравнивание диалогового окна Формат ячеек включить режим переносить по словам; на вкладке Главная в группе Выравнивание щелкнуть по кнопке Перенос текста. Объединение ячеек Объединение ячеек используется, если нужно, чтобы текст занимал несколько соседних ячеек, например, для форматирования общего заголовка над несколькими столбцами. Этот формат можно назначить одним из следующих способов: .на вкладке Выравнивание диалогового окна Формат ячеек включить режим объединение ячеек; на вкладке Главная в группе Выравнивание в списке Объединить и поместить в центре выбрать нужный вариант; щелкнуть по кнопке Объединить и поместить в центре на мини-панели контекстного меню выделенных ячеек. Можно объединить любое количество ячеек, занимающих произвольное значение строк и столбцов. При этом сохраняется содержимое только левой верхней ячейки. Форматирование шрифтов Перед форматированием необходимо выделить нужное: это могут быть отдельные символы в ячейке (только для текста), ячейка целиком или интервал ячеек. 12 Назначить характеристики шрифтов можно одним из следующих способов: на вкладке Шрифт диалогового окна Формат ячеек; на вкладке Главная в группе Шрифт; на мини-панели контекстного меню ячейки; быстрыми клавишами. В диалоговом окне Формат ячеек можно выбрать гарнитуру шрифта, размер, цвет, вид подчеркивания, начертание (обычный, курсив, полужирный), а также оформить символ как надстрочный (верхний индекс) или подстрочный (нижний индекс). В группе Шрифт вкладки Главная и на мини-панели контекстного меню ячейки имеются кнопки со списками для выбора гарнитуры шрифта, размера, цвета, вида подчеркивания, начертания, а также кнопки, щелчок по которым увеличивает и уменьшает размер шрифта. Кроме того, для быстрого форматирования можно использовать следующие комбинации клавиш: Ctrl + B – полужирное начертание; Ctrl + I – курсив; Ctrl + U – подчеркивание; Ctrl + 5 – перечеркивание. Оформление ячеек рамками и линиями Оформить выделенную ячейку или диапазон ячеек рамкой можно одним из следующих способов: I способ 1. Выделить диапазон ячеек, который нужно оформить границей. 2. Вывести на экран диалоговое окно Формат ячеек. 3. Открыть вкладку Граница. 4. В списке тип линии выбрать тип линии. 5. В поле со списком Цвет выбрать цвет линии. 6. Указать положение линии около выделенного диапазона ячеек, щелкнув мышью по пиктограмме с нужным вариантом или по нужной стороне образца. Если для отдельных сторон границы нужно задать линии различных типов и цветов, надо для каждой стороны последовательно выбирать тип, цвет и расположение линии. II способ 1. Выделить диапазон ячеек, который нужно оформить границей. 2. На ленточной вкладке Главная в группе Шрифт открытьсписок Границы. 3. Выбрать команду Цвет линии и в открывшемся списке выбрать нужный цвет. 4. Выбрать команду Тип линии и в открывшемся списке выбрать нужный тип. 5. Выбрать из списка Границы группы Шрифт или мини-панели контекстного меню выделенных ячеек нужный вариант расположения линии относительно выделенного диапазона ячеек. 13 III способ 1. На ленточной вкладке Главная в группе Шрифт открытьсписок Границы. 2. Выбрать команду Цвет линии и в открывшемся списке выбрать нужный цвет. 3. Выбрать команду Тип линии и в открывшемся списке выбрать нужный тип. 4. Использовать команды из группы Нарисовать границы: для оформления диапазона ячеек внешней рамкой выбрать команду Граница рисунка и указать мышью диапазон ячеек; для вычерчивания внутренних и внешних границ диапазона выбрать команду Сетка по границе рисунка и указать мышью диапазон ячеек; для удаления линии выбрать команду Стереть границу и протащить указатель мыши вдоль линии, которую нужно удалить; для удаления всех линий в диапазоне ячеек выбрать команду Стереть границу и указать мышью диапазон ячеек. Применение узора и цвета Назначить цвет фона для ячеек можно одним из следующих способов: на вкладке Заливка диалогового окна Формат ячеек; кнопкой Цвет заливки, расположенной на вкладке Главная в группе Шрифт; кнопкой Цвет заливки, расположенной на мини-панели контекстного меню выделенных ячеек. Кроме того, на вкладке Заливка диалогового окна Формат ячеек можно выбрать узор для оформления ячеек. Числовые форматы По умолчанию числа выводятся на экран в виде целых, десятичных дробей или в экспоненциальном представлении, если число не помещается в ячейке. Назначить выделенным ячейкам числовой формат можно: в диалоговом окне Формат ячеек; кнопками группы Число вкладки Главная; кнопками мини-панели контекстного маню ячейки. Для установки формата в диалоговом окне Формат ячеек надо в списке Числовые форматы выбрать нужный формат: общий, числовой, денежный, финансовый, дата, время, процентный, дробный, экспоненциальный, текстовый. После этого установить параметры (для каждого вида формата – свои), например, например, число десятичных знаков для числового, денежного, финансового, процентного и экспоненциального форматов, обозначение – для денежного и финансового, тип и язык для даты и времени и т.д.. В группе Число ленточной вкладки Главная имеются следующие кнопки: 14 Числовой формат – для выбора формата; Финансовый числовой формат – для выбора альтернативного формата денежной единицы; Процентный – для отображения значения ячейки как процента; Формат с разделителем – использование разделителя тысяч; Увеличить разрядность, Уменьшить разрядность – соответственно для увеличения или уменьшения количества отображаемых знаков в десятичной части числа. На мини-панели контекстного маню ячейки имеются кнопки Финансовый числовой формат, Процентный, Формат с разделителем, Увеличить разрядность, Уменьшить разрядность. Копирование форматов Копирование форматов – быстрый способ форматирования ячеек, если в таблице уже имеются ячейки, отформатированные нужным образом. Наиболее простой способ копирования формата – с помощью кнопки Формат по образцу: 1. Выделить ячейку, формат которой надо скопировать. 2. Щелкнуть по кнопке Формат по образцу, расположенной на вкладке Главная в группе Буфер обмена. Чтобы скопировать формат в несколько диапазонов, дважды щелкнуть по этой кнопке. 3. Указать мышью ячейки, в которые надо скопировать формат. Другой способ – с помощью специальной вставки: 1. Выделить ячейку, формат которой надо скопировать. 2. Выполнить копирование этой ячейки в буфер обмена (любым способом). 3. Выделить ячейки, в которые надо скопировать формат. 4. На вкладке Главная в группе Буфер обмена в списке Вставить выбрать команду Специальная вставка. 5. В диалоговом окне выбрать вариант Форматы. Применение стилей Стиль – это сохраненная комбинация форматов. В стиль могут входить следующие форматы: числовые, шрифт, выравнивание, рамки, узоры, защита. Применение стилей – еще один способ быстрого форматирования ячеек. В Excel 2007 включена большая палитра встроенных стилей. Кроме того, пользователи могут создавать собственные стили. Чтобы применить к ячейкам существующий стиль, следует: 1. Выделить ячейки, которые необходимо отформатировать с помощью стиля. 2. На вкладке Главная в группе Стили открыть список Стили ячеек. Откроется палитра стилей. 3. Выбрать из палитры нужный стиль. 15 После применения к ячейкам стиля можно дополнительно применить к ним другие форматы. При этом изменение форматов этих ячеек не повлияет на форматы других ячеек, к которым применен этот же стиль. Создание стиля I способ: 1. Назначить ячейке нужные форматы и выделить эту ячейку. 2. На вкладке Главная в группе Стили щелкнуть по кнопке Стили ячеек. 3. Щелкнуть по кнопке Создать стиль. 4. В открывшемся диалоговом окне в поле Имя стиля ввести имя создаваемого стиля. 5. Щелкнуть по кнопке ОК. II способ: 1. На вкладке Главная в группе Стили щелкнуть по кнопке Стили ячеек. 2. Щелкнуть по кнопке Создать стиль. 3. В открывшемся диалоговом окне в поле Имя стиля ввести имя создаваемого стиля. 4. Щелкнуть по кнопке Формат. 5. В открывшемся окне Формат ячеек установить нужные форматы. 6. Щелкнуть по кнопке ОК окна Формат ячеек. 7. Щелкнуть по кнопке ОК окна Стиль. Изменение стиля 1. На вкладке Главная в группе Стили открыть список Стили ячеек. 2. Щелкнуть правой кнопкой мыши по имени стиля, который нужно изменить. 3. В открывшемся контекстном меню выбрать команду Изменить. 4. В открывшемся окне щелкнуть по кнопке Формат. 5. В окне Формат ячеек установить нужные форматы. 6. Щелкнуть по кнопке ОК окна Формат ячеек. 7. Щелкнуть по кнопке ОК окна Стиль. После изменения стиля форматы всех ячеек, к которым был применен этот стиль, изменятся. Удаление стиля 1. На вкладке Главная в группе Стили открыть список Стили ячеек. 2. Щелкнуть правой кнопкой мыши по имени стиля, который нужно удалить. 3. В открывшемся контекстном меню выбрать команду Удалить. После удаления стиля все ячейки, к которым он был применен, возвращаются к обычному стилю. Форматы, назначенные 16 дополнительно после применения стиля, сохраняются после его удаления. Темы документов Тема – это встроенный набор форматов документа, включающий цвета, шрифты и графические эффекты. Темы применяются ко всей рабочей книге. Тема не изменяет форматов, которые были заданы до ее применения. Чтобы применить тему к рабочей книге, надо на вкладке Разметка страницы в группе Темы открыть список Темы и выбрать нужное. С помощью кнопок Цвета, Шрифты и Эффекты группы Тема можно изменить форматы текущей темы. Практическая работа 3 Функции в формулах 1. Открыть новый лист и составить таблицу для расчета зарплаты при сдельно-прогрессивной системе оплаты труда. Столбцы таблицы: Фамилия И.О., Выработка, Стоимость работы, Пенсионный фонд, Налог, К выдаче. Значения столбца Фамилия И.О. получить из столбцов Фамилия, Имя и Отчество таблицы на листе Расчет зарплаты с помощью операции конкатенации и функции ЛЕВСИМВ. Столбец Выработка (количество изделий) заполнить произвольными значениями от 8 до 12. Стоимость работы рассчитывается по следующим правилам: работа в пределах нормы выработки оплачивается по установленным расценкам, работа сверх нормы – по повышенным на 20% расценкам. Норма выработки равна 10 изделиям. Расценки: 1 000 руб. за 1 изделие. При вводе формулы использовать функцию ЕСЛИ. Замечание: по повышенным расценкам оплачивается только произведенное сверх нормы, например, если произведено 12 изделий, то 10 из них будет оплачено по обычным расценкам и только 2 по повышенным. Отчисления в пенсионный фонд составляют 1% от стоимости работы. Налог = (Стоимость работы – Пенсионный фонд – МРОТ) × 13%. К выдаче = Стоимость работы – Пенсионный фонд – Налог. Все константы (норму выработки, расценки, процент налога) разместить во второй строке таблицы (под «шапкой»). 2. Добавить итоговую строку, содержащую суммы столбцов Выработка, Стоимость работы, Пенсионный фонд, Налог, К выдаче. 3. Присвоить листу имя Сдельная оплата. 17 Форматирование таблицы 4. Ячейкам, содержащим денежные величины, назначить денежный формат. 5. Открыть лист Повременная оплата. Создать стиль, основанный на формате ячейки Оклад (шрифт – Times New Roman 12, полужирный, выравнивание по горизонтали и по вертикали – по центру, фон – светло-серый, рамки). Присвоить стилю имя «Шапка». Использовать этот стиль для форматирования «шапки» таблицы на листе Сдельная оплата. 6. Отформатировать итоговую строку встроенным стилем «Итог» 7. Оформить таблицу утолщенной рамкой, столбцы разделить тонкими вертикальными линиями; «шапку», итоговую строку и строку с константами отделить тонкими горизонтальными линиями, строки таблицы линиями не разделять. Вложенные функции 1. Открыть новый лист и составить таблицу для расчета стоимости телефонных разговоров. Столбцы таблицы: Номер по порядку, Дата, Время начала разговора, Время окончания разговора, Время разговора, День недели, Час суток, Тариф, Стоимость разговора. Столбец Дата заполнить произвольно датами одного месяца. Столбцы Время начала разговора и Время окончания разговора заполнить произвольными значениями, причем разница между временем начала и окончания разговора должна быть меньше часа. Значения остальных столбцов рассчитываются: Стоимость разговора равна произведению времени разговора на тариф. Время разговора рассчитывается как разность между окончанием и началом разговора в минутах. Для определения нужного компонента времени (минут) использовать функцию МИНУТЫ. Используется следующая система оплаты (тариф): в выходные дни − 0,15 долл. за 1 минуту разговора, в будни − 0,39 долл. с 8:00 до 21:00 и 0,10 долл. – в остальное время суток. Для определения дня недели использовать функцию ДЕНЬНЕД. Для определения часа суток использовать функцию ЧАС. Час суток определять по времени начала разговора. Добавить итоговую строку, содержащую сумму столбца Стоимость разговора. Листу с таблицей присвоить имя Телефон. 2. Отформатировать таблицу: ячейкам, содержащим денежные величины, назначить денежный формат; 18 «шапку» отформатировать с помощью стиля «Шапка», созданного в предыдущей работе; итоговую строку отформатировать с помощью встроенного стиля «Итог»; расчертить таблицу линиями; внешнюю рамку оформить утолщенной линией; столбцы разделить тонкими черными линиями; добавить фон. |