Методические указания к выполнению лабораторных работ по теме «Табличный редактор MS Excel»

Методические указания к выполнению лабораторных

Работ по теме

«Табличный редактор MS Excel»

Часть 1

Для студентов 1 курса очной формы обучения

Специальности 6.050 100 - "Бухгалтерский учет и аудит"

Симферополь 2009г.

Методические указания к выполнению лабораторных работ по теме «Табличный редактор MS Excel» курса «Экономическая информатика» для студентов 1 курса очной формы обучения специальности 6.050 100 - "Бухгалтерский учет и аудит".

СОДЕРЖАНИЕ

Введение

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

Приложение MS Excel. Общие сведения

1. Общие сведения о табличном редакторе.

2. Работа с листами в Microsoft Excel.

3. Работа с меню и панелями инструментов.

4. Ввод и форматирование данных.

1. Общие сведения о табличном редакторе

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

При запуске Microsoft Excel появляется рабочая книга «Книга 1», содержащая несколько листов. Окно приложения состоит из нескольких частей:

1. Строка заголовка.

На ней отображается имя активного документа, а также находятся кнопки управления окном.

2. Меню.

Основная обработка данных осуществляется при помощи команд из строки меню.

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

Как правило, по умолчанию установлены две панели инструментов – «Стандартная» (выше) и «Форматирование» (ниже).

На панелях инструментов расположены кнопки для наиболее употребляемых команд меню.

4. Строка формул.

В данной строке производится набор и редактирование формул. Для удаления или восстановления строки формул необходимо воспользоваться командой «Вид | Строка формул».

5. Поле имени.

В этом поле отображается имя ячейки или диапазона ячеек. При необходимости имя можно заменить.

6. Строка состояния.

В данной строке отображается текущее состояние документа. Эту строку можно подключить или убрать с помощью команды «Вид | Строка состояния».

Контрольные вопросы:

1. Что такое табличный редактор?

2. Объясните назначение панелей инструментов.

3. Для чего нужна строка формул?

4. Как убрать строку состояния?

2. Работа с листами в Microsoft Excel

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

При перемещении или копировании листа его можно вставить как в текущую книгу, так и в любую другую открытую книгу Microsoft Excel.

Для удобства работы с документом ярлыки листов можно выделять любым цветом. Для этого необходимо кликнуть правой кнопкой мыши по ярлыку нужного листа и в появившемся контекстном меню выбрать команду «Цвет ярлычка…».

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

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

Рабочий лист в Microsoft Excel содержит 256 столбцов и 65536 строк. На пересечении строк и столбцов расположены ячейки. Каждая ячейка на листе имеет свое имя. Имя ячейки может зависеть от выбранного стиля ссылок. Существует два стиля ссылок: ссылки типа «А1» и ссылки типа «R1C1».

По умолчанию имя ячейки состоит из имени столбца и номера строки, на пересечении которых ячейка расположена (например, «А5» или «BK28»). Но в некоторых случаях удобно давать ячейке или диапазону ячеек другое имя. Чтобы переименовать ячейку, необходимо ее выделить, а затем в поле имени вписать необходимое имя и нажать клавишу «Enter». Например, назовем ячейку «А1» «Курс_доллара».

Для удаления имени воспользуйтесь командой «Вставка | Имя | Присвоить». В появившемся диалоговом окне выберите нужное имя и нажмите на кнопку «Удалить».

Одной из основных операций с ячейками в Microsoft Excel является выделение необходимого диапазона данных (диапазон – блок ячеек). Диапазон ячеек бывает смежным, несмежным или смешанным.

Примеры диапазонов:

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

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

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

Контрольные вопросы:

1. Как добавить/удалить лист MS Excel?

2. Как изменить цвет ярлычка листа?

3. Как задать ячейке/диапазону ячеек имя?

4. Назовите виды диапазонов ячеек?

3. Работа с меню и панелями инструментов

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

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

Для наиболее комфортной работы с приложением используют панели инструментов.

На эти панели вынесены кнопки вызова самых распространенных команд, таких как «Сохранить», «Печать», «Масштаб» и др. Как правило, по умолчанию используются панели инструментов «Стандартная» и «Форматирование». Кроме них, можно подключать/убирать и другие панели. Для этого необходимо выбрать «Вид»-«Панели инструментов».

При необходимости на панель инструментов можно добавить кнопки на недостающие команды. Для этого необходимо по любой из панелей кликнуть правой кнопкой мыши и в появившемся контекстном меню выбрать пункт «Настройка…». В окне настройки перейти на вкладку «Команды», выбрать нужную категорию и, удерживая искомую команду левой кнопкой мыши, перетащить ее на панель инструментов.

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

Чтобы добавить или убрать панель инструментов, необходимо вызвать контекстное меню панелей и выбрать нужный пункт. При необходимости, кроме уже существующих панелей инструментов можно создавать свои. Для этого в окне «Настройка» необходимо перейти на вкладку «Панели инструментов» и кликнуть на кнопке «Создать». После чего ввести имя панели и метод сохранения. Далее на появившуюся пустую панель установить кнопки нужных команд методом описанным выше. Чтобы удалить панель инструментов, в окне настройки выделяем соответствующую панель и нажимаем кнопку «Удалить».

Контрольные вопросы:

1. Как добавить недостающие панели инструментов?

2. Как добавить на панель инструментов недостающую кнопку?

3. Как создать/удалить панель инструментов?

3. Ввод и форматирование данных

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

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

Пример:

В ячейках А2:А10 заполнить порядковые числа от1 до 9. Для этого в ячейку А2 вводим единицу, выделяем ячейки А2:А10 и заполняем окно «Прогрессия» следующим образом:

Пример:

В строку начиная с ячейки С1 ввести все рабочие даты с 26 декабря 2008 года по 31 января 2009года. Для этого в ячейку С1 вводим первую дату «26.12.2008» и заполняем окно «Прогрессия» следующим образом:

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

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

- В меню «Правка» выбрать команду «Найти»/«Заменить».

- В диалоговом окне необходимо заполнить поле «Найти» или поля «Найти» - «Заменить на». При необходимости заполнить нужные «Параметры».

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

«Найти все»: поиск всех ячеек в документе, удовлетворяющих условиям поиска.

«Найти далее»: поиск следующей ячейки, содержащей строку знаков, введенную в поле «Найти». Для поиска предыдущей ячейки нажмите клавишу SHIFT и, не отпуская ее, нажмите кнопку «Найти далее».

«Заменить Все»: производит замену во всех ячейках документа, удовлетворяющих условиям поиска.

«Заменить все»: производит замену в выбранной ячейке, удовлетворяющей условию, заданному в поле «Найти», находит следующую ячейку, удовлетворяющую этому условию, и прекращает поиск. Для автоматической замены во всех ячейках документа, удовлетворяющих условиям поиска, нажмите кнопку «Заменить все».

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

Описание выбранного формата приведено на той же вкладке ниже.

Кроме изменения формата данных можно изменить и другие настройки.

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

- изменить ширину столбца. Для этого необходимо выделить один или несколько столбцов (выделение производится по заголовку столбца) и в контекстном меню выбрать пункт «ширина столбца».

Результат:

По аналогии можно изменить и высоту строк.

- на вкладке «Выравнивание» окна «Формат ячеек» установить флажок на пункте «переносить по словам».

Результат:

- на вкладке «Выравнивание» окна «Формат ячеек» установить флажок на пункте «автоподбор ширины».

Результат:

- объединить ячейку с несколькими смежными. Для этого необходимо выделить эти ячейки и на вкладке «Выравнивание» окна «Формат ячеек» установить флажок на пункте «объединение ячеек».

Результат:

Окно «Формат ячеек» содержит также набор настроек шрифта (вкладка «Шрифт»).

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

Кроме настроек шрифта, в окне «Формат ячеек» можно задать и формат самой ячейки – установить границы, залить ячейку цветом. Для этого необходимо воспользоваться вкладками «Граница» и «Вид» или кнопками панели инструментов «Форматирование».

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

Контрольные вопросы:

1. Можно ли вводить данные в ячейку через строку формул?

2. Можно ли автоматически вывести все рабочие дни текущего месяца?

3. Как заменить в документе один набор символов другим?

4. Как изменить формат числа в диапазоне ячеек?

5. Можно ли у диапазона ячеек установить волнистые границы?

6. Как сделать заливку диапазона ячеек узором.

Задания:

1. Создать документ «Лаб_1.xls», состоящий из 2 листов. Первый лист назвать «Лабораторная», установить красный цвет ярлыка листа.

2. В диапазоне A1:J12 разместить следующую таблицу:

3. В таблице поле «Табельный номер» заполнить автозаполнением.

4. Заменить в таблице все слова «Фонд» на «Ф.».

Формулы и функции в MS Excel.

1. Формулы и ссылки в MS Excel.

2. Работа с функциями.

3. Категории функций.

4. Значения ошибок в формулах.

1. Формулы и ссылки в MS Excel

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

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

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

- в ячейке или строке формул ввести знак «=».

- записать формулу;

- нажать «Enter».

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

Например, чтобы рассчитать поле «Начислено» зная «Оклад» и «Ставку», необходимо выделить ячейку С2, поставить знак «=», мышкой выделить ячейку А2, ввести знак «*», мышкой выделить ячейку В2 и нажать «Enter». Если при вводе формулы использовать ссылки на ячейки, то при изменении исходных данных, изменятся и значение в зависимой ячейке (ячейке результата). Если же при вводе формулы данные вводятся с клавиатуры, то при изменении исходных данных значение зависимой ячейки останется прежним.

Ссылки в MS Excel бывают относительными, абсолютными и смешанными.

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

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

Смешанная ссылка (ссылка типа $A1 или A$1) – адрес ячейки с фиксированными столбцом или строкой. При копировании ячейки в смешанной ссылке автоматически изменяется только незафиксированная часть.

Чтобы изменить тип ссылки, необходимо выделить в формуле эту ссылку и несколько раз (в зависимости от типа ссылки) нажать клавишу «F4».

Примеры использования ссылок:

Копировать формулу можно обычным способом копирования (команды «копировать» и «вставить») или выделив ячейку с формулой и растянув ее в нужном направлении за нижний правый угол (указатель превратится в черный крестик).

Контрольные вопросы:

1. Опишите процесс ввода формул в ячейку.

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

3. Назовите 3 типа ссылок.

4. В чем разница между относительными и абсолютными ссылками?

5. Как скопировать формулу на диапазон ячеек?

2. Работа с функциями

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

Функции - заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления. Синтаксис функции включает в себя имя функции, открывающую скобку, список аргументов через «;» и закрывающую скобку. Функции можно использовать как отдельно, так и внутри формул и других функций.

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

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

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

=ПИ( )

=СЕГОДНЯ( )

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

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

Кроме поиска функции по категории можно также найти функцию, кратко описав действие, которое она выполняет в поле «Поиск функции».

После вызова функции появляется окно этой функции:

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

Например, вычисляя шестую степень числа, которое находится в ячейке В3, необходимо заполнить функцию «Степень» категории «Математические» следующим образом:

Справа от полей для ввода аргументов отражается значение каждого из аргументов. Результат возведения в степень указан здесь же чуть ниже (11,390625). В строке формул данная функция будет иметь следующий вид: .

Значение формулы выводится в числовом формате той ячейки, в которой она находится. Если же формат числа для данной ячейки не установлен, то в этом случае значение формулы выводится в числовом формате первой из тех ячеек, на которые ссылается формула. Например, если для ячейки, содержащей формулу "=А1+А2" не установлен формат числа, то значение формулы выводится в числовом формате, установленном для ячейки А1.

Иногда бывает необходимо в качестве аргумента функции использовать другую функцию. Например, необходимо вычислить шестую степень округленного числа, лежащего в ячейке А1 (значение из А1 округлить до целого). Для этого в ячейке В1 вызываем функцию «Степень». В поле «Степень» вводим число 6, а в поле «Число» вызываем функцию «Округл» (функция вызывается с помощью выпадающего списка «Вызов функции» в строке формул).

Если в приведенном списке нет нужной функции, необходимо выбрать пункт «Другие функции». После вызова функции «Округл» заполняем ее поля и нажимаем «ОК».

Так как значение в ячейке А1 необходимо округлить до целого числа, то в поле «Число_разрядов» функции «Округл» вводим 0. После введения формулы в строке формул получится следующее выражение: =СТЕПЕНЬ(ОКРУГЛ(A1;0);6).

Контрольные вопросы:

1. Дайте определение функции в MS Excel.

2. Что такое «аргументы функции»?

3. Как вставить функцию в ячейку?

4. Можно ли в качестве аргумента функции использовать другую функцию?

3. Категории функций

В Excel для удобства все функции разбиты на категории.

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

Функции даты и времени. Представление даты и времени в Excel имеют одну особенность – помимо текстового представления существует и числовое. За точку отсчета принята дата 1 января 1900 года (т. е. 1 января 1900 г. соответствует числу 1; 2 января 1900 г. соответствует числу 2 и т. д.). Благодаря этому даты можно сравнивать, прибавлять, вычитать и т. д. Функции даты и времени позволяют вычислять день недели, текущую дату и время и др.

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

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

Функции из категории «Ссылки и массивы» предназначены для нахождения номеров строк и столбцов таблицы по заданной ссылке, нахождения значений в справочных таблицах и др.

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

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

Логические функции используются для проверки или постановки каких-либо условий.

Категория «Проверка свойств и значений» необходима при определении типа значений, выводе информации об операционной системе, определении типа возникшей ошибки.

Кроме вышеперечисленных категорий существует еще и функции, определенные пользователем. Данная категория предназначена для хранения функций, написанных пользователями на языке VBA (Visual Basic for Application).

Контрольные вопросы:

1. Какие категории существуют в MS Excel?

2. Что такое числовой формат дат?

3. Можно ли создавать пользовательские функции в MS Excel?

4. Значения ошибок в формулах

Ехсеl выводит в ячейку значение ошибки, когда формула для этой ячейки не может быть правильно вычислена. Если формула содержит ссылку на ячейку, в которой находится значение ошибки, то данная формула также будет выводить значение ошибки (за исключением тех случаев, когда используются специальные функции рабочих листов ЕОШ, ЕОШИБКА или ЕНД, которые проверяют наличие значений ошибок). При работе с электронной таблицей может возникнуть необходимость проследить зависимости для ряда ячеек со ссылками с целью определения источника ошибки, для чего могут быть полезны названия кодов ошибок, а также возможные причины их возникновения.

Коды ошибок и их возможные причины

Контрольные вопросы:

1. Какие функции MS Excel проверяют наличие ошибок в формулах?

2. Расшифруйте коды ошибок: «#ДЕЛ/0!», «#ИМЯ?», «#ССЫЛКА!», «#ЗНАЧ!».

Задания:

1. Вычислить текущую дату и рассчитать максимальную и минимальную заработную плату сотрудников (в $). Результаты представить в следующей форме:

Для расчета поля «Начислено» воспользоваться формулой:

«Начислено = Оклад * Ставку»

Минимальную и максимальную заработную плату рассчитать, используя функции «МИН» и «МАКС» категории «Статистические».

2. Рассчитать синусы углов от -2П до 2П с шагом П/2. Результаты представить в следующей форме:

Для перевода угла из радиан в градусы использовать функцию «ГРАДУСЫ». Для вычисления синуса угла применить функцию «SIN».

3. Найти корни квадратного уравнения 2х2+26х-7=0. Результаты представить в следующей форме:

Решение задач с использованием функций.

1. Функции даты и времени.

2. Математические функции.

3. Статистические функции.

4. Функции для работы с текстом.

5. Логические функции.

1. Функции даты и времени

Функция ВРЕМЗНАЧ(время_как_текст) возвращает время в числовом формате для времени, заданного текстовой строкой. Время в числовом формате — это десятичная дробь в интервале от 0 до 0,99999999, представляющая время от 0:00:00 (12:00:00 ночи) до 23:59:59 (11:59:59 вечера).

Аргументы функции:

Время_как_текст — это текстовая строка, задающая время в любом из форматов времени Microsoft Excel. Например, текстовые строки в кавычках "18:45" и "18:45:00" представляют время.

Пример:

=ВРЕМЗНАЧ("22-авг-2008 6:35 AM") возвратит часть суток, представленную десятичным числом для времени (Результат: 0,274305556).

Функция ДАТАЗНАЧ(дата_как_текст) возвращает числовой формат даты, представленной в виде текста. Функция «ДАТАЗНАЧ» используется для преобразования даты из текстового представления в числовой формат.

Аргументы функции:

Дата_как_текст — это текст, представляющий дату в формате даты Microsoft Excel. Например, "30.01.2008" или "30-янв-2008" являются текстовыми строками в кавычках, которые представляют даты.

Пример:

=ДАТАЗНАЧ("22.08.2008") или =ДАТАЗНАЧ("22-авг-2008") возвратит числовой формат даты, представленной в виде текста, с использованием системы дат 1900 (39682).

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

Аргументы функции:

Год — аргумент, который может иметь от одной до четырех цифр.

Месяц — число, представляющее месяц года.

День— число, представляющее день месяца.

Пример:

=ДАТА(2009;3;30) вернет либо «30.03.2009», либо 39902 (дата в числовом формате) если формат ячейки «Общий».

Функция ДАТАМЕС(нач_дата; мес) возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты.

Аргументы функции:

Нач_дата — дата в числовом формате, которая представляет начальную дату.

Мес — число месяцев до или после начальной даты.

Пример:

Если ячейка А1 содержит дату «30.03.2009», то функция =ДАТАМЕС(A1;1) вернет дату «30.04.2009».

Функция ДНЕЙ360(нач_дата; кон_дата; метод) возвращает количество дней между двумя датами на основе 360-дневного года (двенадцать 30-дневных месяцев). Эта функция используется для расчета платежей, если бухгалтерия основана на двенадцати 30-дневных месяцах.

Аргументы функции:

Нач_дата И кон_дата — Это две даты, количество дней между которыми необходимо вычислить.

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

Пример:

Если в ячейке А1 лежит значение «21.05.2004», а в ячейке В1 – «15.07.2006», то формула =ДНЕЙ360(A1;B1) вернет результат 774 (количество дней между этими датами).

Функция РАБДЕНЬ(нач_дата; дни; выходные) вычисляет дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней.

Аргументы функции:

Нач_дата — Дата в числовом формате, которая представляет начальную дату.

Дни — это количество не выходных и не праздничных дней до или после начальной даты.

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

Пример:

Если ячейка А1 содержит дату «10.04.09» (пятница) и требуется найти рабочий день, отстоящий на 5 дней назад от указанной даты, то функция примет следующий вид: =РАБДЕНЬ(A1;-5). Результатом выполнения данной функции будет дата «3.04.09» (пятница предыдущей недели).

Функция СЕГОДНЯ() Возвращает текущую дату в формате даты. Не содержит аргументов.

Функция ЧИСТРАБДНИ(нач_дата; кон_дата; выходные) возвращает количество рабочих дней между датами.

Аргументы функции:

Нач_дата и кон_дата — Даты в числовом формате, количество рабочих дней между которыми необходимо вычислить.

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

Пример:

Если ячейка А1 содержит дату «3.04.09» (пятница), а ячейка В1 - дату «10.04.09» (следующая пятница), то функция = ЧИСТРАБДНИ(A1;B1) возвратит число 6.

Контрольные вопросы:

1. Какая функция MS Excel возвращает количество дней между двумя указанными датами?

2. Что возвращает функция «СЕГОДНЯ»?

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

4. Какая функция возвращает количество рабочих дней между указанными датами?

2. Математические функции

Функция ABS(число) возвращает модуль (абсолютную величину) числа. Абсолютная величина числа - это число без знака.

Аргументы функции:

Число — это действительное число, модуль которого требуется найти.

Пример:

Если ячейка А1 содержит число «-5», то функция = ABS(A1) возвратит число 5.

Функции COS(число) и SIN(число) возвращают соответственно косинус и синус заданного угла.

Аргументы функции:

Число — это угол в радианах, для которого определяется косинус.

Пример:

Функция =COS(1,047) вернет косинус угла 1,047, равный 0,500171. Функция =SIN(ПИ()/2) вернет синус угла 90°, равный 1.

Функция LOG(число; основание) вОзвращает логарифм числа по заданному основанию.

Аргументы функции:

Число — положительное вещественное число, для которого вычисляется логарифм.

Основание — основание логарифма. Если основание опущено, то оно полагается равным 10.

Пример:

Функция =LOG(8; 2) даст результат 3.

Функция КОРЕНЬ(число) возвращает положительное значение квадратного корня.

Аргументы функции:

Число — число, для которого вычисляется квадратный корень.

Пример:

Если в ячейке А1 лежит число 9, то функция =КОРЕНЬ(A1) возвратит число 3.

Функция МОПРЕД(Массив) Возвращает определитель матрицы (матрица хранится в массиве).

Аргументы функции:

Массив — числовой массив с равным количеством строк и столбцов.

Пример:

Если в диапазоне ячеек А1:С3 лежит следующая матрица

,

То функция =МОПРЕД(A1:С3) вернет ее определитель, равный 258.

Функция МУМНОЖ(Массив1; Массив2) возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив1 и с таким же числом столбцов, как массив2.

Аргументы функции:

Массив1, массив2 — перемножаемые массивы.

Пример:

Если в диапазоне ячеек А1:В3 содержится первый массив, а в диапазоне D1:F2 - второй, то функция =МУМНОЖ(A1:B3;D1:F2),вызванная в ячейке А6 вернет матрицу-произведение первых двух массивов.

Так как результатом функции является массив чисел, то после расчета формулы в ячейке А6 необходимо выделить массив А6:С8, встать в строку формул и нажить сочетание клавиш CTRL+SHIFT+ENTER.

Функция ОКРУГЛ(Число; Число_разрядов) округляет число до указанного количества десятичных разрядов.

Аргументы функции:

Число — Округляемое число.

Число_разрядов — количество десятичных разрядов, до которого нужно округлить число.

Пример:

Если в ячейке А1 лежит число 9,2567, то функция =ОКРУГЛ(A1;2) возвратит число 9,26.

Функция СУММЕСЛИ(диапазон; Критерий; Диап_суммирования) Суммирует ячейки, заданные критерием.

Аргументы функции:

Диапазон — диапазон вычисляемых ячеек.

Критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, "32", ">32", "Иванов".

Диап_суммирования — фактические ячейки для суммирования.

Пример:

В диапазоне А1:С7 находятся данные продаж предприятия:

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

=СУММЕСЛИ(B2:B7;"Телевизор";C2:C7),

Где B2:B7 – диапазон типов товара, а C2:C7 – диапазон стоимостей.

Функция СУММКВ(число1; число2; ...) возвращает сумму квадратов аргументов.

Аргументы функции:

Число1, число2,... — от 1 до 30 аргументов, квадраты которых суммируются. Можно использовать отдельный массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.

Пример:

=СУММКВ(3; 4) возвратит сумму квадратов чисел 3 и 4, равную 25.

Функция ФАКТР(число) Возвращает факториал числа. Факториал числа — это значение, равное 1*2*3*...* число.

Аргументы функции:

Число — это неотрицательное число, факториал которого вычисляется. Если число не целое, то производится усечение.

Пример:

=ФАКТР(5) вернет факториал числа 5, равный 120.

Контрольные вопросы:

1. Какая функция возвращает модуль указанного числа?

2. Перечислите функции для работы с матрицами в MS Excel.

3. Что возвращает функция «ОКРУГЛ»? Перечислите все аргументы этой функции.

4. Перечислите тригонометрические функции MS Excel.

3. Статистические функции

Функции МАКС(число1; число2; ...) и МИН(число1; число2; ...) возвращают соответственно наибольшее и наименьшее значение из набора значений.

Аргументы функции:

Число1, число2,... — от 1 до 30 чисел, среди которых требуется найти наибольшее (наименьшее). Числа можно задать массивом (диапазоном ячеек).

Пример:

Если задан следующий диапазон

,

То функция =МАКС(A1:D1) вернет число 28, а функция =МИН(A1:D1) – число 1.

Функция НАИБОЛЬШИЙ(Массив; K) возвращает k-ое по величине значение из множества данных. Эта функция позволяет выбрать значение по его относительному местоположению. Например, функцию НАИБОЛЬШИЙ можно использовать для определения наилучшего, второго или третьего результатов тестирования в баллах.

Аргументы функции:

Массив — массив или интервал данных, для которых определяется k-ое наибольшее значение.

K — позиция (начиная с наибольшей) в массиве или интервале ячеек данных.

Пример:

Если задан следующий диапазон

,

То функция =НАИБОЛЬШИЙ(A1:D1;2) вернет второе по величине значение, равное 23.

Функция НАИМЕНЬШИЙ(Массив; K) возвращает k-ое наименьшее значение в множестве данных.

Аргументы функции:

Массив — массив или диапазон числовых данных, для которого определяется k-ое наименьшее значение.

K — позиция (начиная с наибольшей) в массиве или интервале ячеек данных.

Пример:

Если задан следующий диапазон

,

То функция =НАИМЕНЬШИЙ(A1:D1;2) вернет предпоследнее по величине значение, равное 4.

Функция СРЗНАЧ(число1; число2; ...) возвращает среднее (арифметическое) своих аргументов.

Аргументы функции:

Число1, число2, ... — это от 1 до 30 аргументов, для которых вычисляется среднее.

Пример:

Если задан следующий диапазон

,

То функция =СРЗНАЧ(A1:D1) вернет число 14.

Контрольные вопросы:

1. Объясните разницу между функциями «МАКС» и «НАИБОЛЬШИЙ»?

2. Можно ли в функциях «МАКС» и «МИН» в качестве аргументов использовать диапазоны данных?

3. Сто возвращает функция «СРЗНАЧ»?

4. Функции для работы с текстом

Функция ДЛСТР(Текст) возвращает количество знаков в текстовой строке.

Аргументы функции:

Текст — текст, длину которого нужно определить. Пробелы также учитываются.

Пример:

Если в ячейке А1 лежит строка «Иванов Иван», то функция =ДЛСТР(A1) вернет результат 11.

Функция ЗАМЕНИТЬ(Старый_текст; Нач_поз; Число_знаков; Новый_текст) замещает указанную часть знаков текстовой строки другой строкой текста.

Аргументы функции:

Старый_текст — текст, в котором желательно заменить некоторые знаки.

Нач_поз — позиция знака в тексте старый_текст, начиная с которой знаки заменяются текстом нов_текст.

Число_знаков — число знаков в тексте старый_текст, которые заменяются текстом новый_текст.

Новый_текст — текст, который заменяет знаки в тексте старый_текст.

Пример:

Если в ячейке А1 лежит строка «Иванов Иван», то функция =ЗАМЕНИТЬ(A1;8;4;"Петр") вернет результат «Иванов Петр».

Функция ЛЕВСИМВ(текст; количество_знаков) возвращает указанное число знаков с начала текстовой строки.

Аргументы функции:

Текст — текстовая строка, содержащая извлекаемые знаки.

Количество_знаков — количество знаков, извлекаемых функцией ЛЕВСИМВ.

Пример:

Если ячейка А1 содержит текст «Иван», то функция =ЛЕВСИМВ(A1;1) вернет первую букву имени - «И».

Функция ПОДСТАВИТЬ(текст; стар_текст; нов_текст; номер_вхождения) подставляет текст нов_текст вместо текста стар_текст в текстовой строке. Функция ПОДСТАВИТЬ используется, когда нужно заменить определенный текст в текстовой строке; функция ЗАМЕНИТЬ используется, когда нужно заменить любой текст, начиная с определенной позиции.

Аргументы функции:

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

Стар_текст — заменяемый текст.

Нов_текст— текст, на который заменяется стар_текст.

Номер_вхождения— определяет, какое вхождение текста стар_текст нужно заменить на нов_текст. Если номер_вхождения определен, то заменяется только это вхождение текста стар_текст. В противном случае, каждое вхождение текста стар_текст в текстовой строке заменяется на текст нов_текст.

Пример:

Если в ячейке А1 лежит строка «Иванов Иван», то

=ПОДСТАВИТЬ(A1;"Иван";"Петр") вернет результат «Петров Петр»,

=ПОДСТАВИТЬ(A1;"Иван";"Петр";1) вернет результат «Петров Иван»

=ПОДСТАВИТЬ(A1;"Иван";"Петр";2) вернет результат «Иванов Петр».

Функция СЦЕПИТЬ(текст1; Текст2;...) объединяет несколько текстовых строк в одну.

Аргументы функции:

Текст1, текст2, ... — это от 1 до 30 элементов текста, объединяемых в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку.

Пример:

Если в ячейке А1 находится текст «Иванов», в ячейке В1 – «Иван», а в С1 – «Иванович», то результатом функции =СЦЕПИТЬ(A1;" ";B1;" ";C1) будет строка «Иванов Иван Иванович».

Контрольные вопросы:

1. Какая функция возвращает указанное количество левых символов строки?

2. Что возвращает функция «ДЛСТР»?

3. Опишите принцип работы функции «ПОДСТАВИТЬ».

4. Какая функция объединяет несколько текстовых строк в одну?

5. Логические функции

Функция ЕСЛИ(лог_выражение; значение_если_истина ; значение_если_ложь) возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Функция ЕСЛИ используется при проверке условий для значений и формул.

Аргументы функции:

Лог_выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10 = 100— это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ.

Значение_если_истина — это значение, которое возвращается, если лог_выражение равно ИСТИНА. Значение_если_истина может быть формулой.

Значение_если_ложь — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Значение_если_ложь может быть формулой.

Пример:

Пусть имея статью «Расходы», необходимо определить, укладываемся ли мы в бюджет (укладываемся, если расходы < 5000).

Для этого в ячейку В2 необходимо поместить следующую формулу: =ЕСЛИ(A2<5000; "В пределах бюджета"; "Перерасход") и растянуть ее до ячейки В3.

Пример:

Пусть имея статью «Начислено», необходимо рассчитать отчисления в фонд социального страхования (если «Начислено» < 669 грн, то в фонд перечисляется 0,5% от суммы начисленного, а иначе - 1%).

Для этого в ячейку В2 необходимо ввести следующую формулу: =ЕСЛИ(A2<669; A2*0,005; A2*0,01) и растянуть ее до ячейки В4.

Функция И(логическое_значение1; логическое_значение2; ...) Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Аргументы функции:

Логическое_значение1, логическое_значение2, ... — это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

Пример:

Рассчитать надбавку за стаж работы. Надбавка за стаж от 5 до 15 лет – 10% от суммы оклада.

Для этого в ячейку С2 необходимо ввести следующую формулу: =ЕСЛИ(И(B2>=5;B2<=15); A2*0,1; 0). Где неравенство вида 5<=B2<=15 задается с помощью функции И(B2>=5;B2<=15). После этого функцию необходимо распространить на ячейки С3 и С4.

Функция ИЛИ(логическое_значение1; логическое_значение2; ...) Возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Аргументы функции:

Логическое_значение1, логическое_значение2, ... — это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

Пример:

Определить тип класса учащихся (выпускной класс – 11 или 9; переводной класс – все остальные).

Для этого в ячейку С2 необходимо поместить следующую формулу: =ЕСЛИ(ИЛИ(B2=9;B2=11); "Выпускной"; "Переводной"). Где условие ИЛИ(B2=9;B2=11) определяет, является ли класс выпускным.

Контрольные вопросы:

1. Опишите принцип работы функции «ЕСЛИ».

2. В чем разница между логическими функциями «И» и «ИЛИ»?

Задания:

1. В ячейках A1:F8 находятся исходные данные для расчета заработной платы сотрудникам предприятия:

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

- Для расчета поля «ФИО» - функции «СЦЕПИТЬ» и «ЛЕВСИМВ».

- Для расчета поля «ВОЗРАСТ» - функцию «СЕГОДНЯ».

- При расчете поля «ИТОГОВАЯ СУММА» использовать функцию «СУММЕСЛИ».

- Формулы для расчета фондов и подоходного налога:

ФЗ = Итоговая сумма (ЗП) ´ 0,5%

- «ВСЕГО УДЕРЖАНО» - сумма всех фондов и подоходного налога.

- «К ВЫДАЧЕ» = «ИТОГОВАЯ СУММА» - «К ВЫДАЧЕ».

2. Определить количество букв в фамилии каждого сотрудника.

3. Рассчитать количество рабочих дней с начала года до текущей даты.

4. Решить систему линейных уравнений (параметры системы задать самостоятельно):

Для расчета корней использовать метод Крамера и функцию «МОПРЕД».

Список литературных источников

1. Гврнаев А. Ю., Матросов А. В., Новиков Ф. А., Усаров Г. И. и др. Microsoft Office 2000. Разработка приложений. – СПб., БХВ, 2000. – 656 с.

2. Колесников А. Excel 2000 (русифицированная версия). – К.: Изд. группа ВНУ, 1999. – 496 с.

3. Лавренов С. М. Excel: Сборник примеров и задач. – М.: Финансы и статистика, 2003. – 336 с.: ил. – (Диалог с компьютером).