- •Введение
- •Лабораторная работа №1 общие приемы работы в среде ms excel
- •Методические указания Структура окна ms Excel.
- •Управление окнами и режимы отображения рабочих книг.
- •Перемещение по рабочей книге и выделение диапазонов ячеек.
- •Загрузка и сохранение документа.
- •Работа со справочной системой ms Excel.
- •Задания для выполнения работы Задание 1. Настройка оконного интерфейса ms Excel.
- •Задание 2. Выделение диапазонов ячеек.
- •Задание 3. Создание, редактирование и сохранение рабочей книги.
- •Задание 4. Работа со справочной системой ms Excel.
- •Контрольные вопросы
- •Лабораторная работа №2 Форматирование и рЕдактирование ячеек и таблиц
- •Методические указания
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №3 Числовые (пользовательские) форматы данных
- •Методические указания
- •Положит.Число; отрицат.Число; нуль; @ "текст".
- •Коды числовых форматов.
- •Коды цветов.
- •Коды условий.
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №4 Реализация арифметических и логических вычислений
- •Методические указания
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №5 Обработка Массивов в ms excel
- •Методические указания
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №6 Адресация в ms Excel
- •Методические указания
- •Абсолютная адресация:
- •Относительная адресация:
- •Смешанная адресация:
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №7 использование встроенных функций
- •Методические указания
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №8 Построение графиков и диаграмм
- •Методические указания
- •Редактирование диаграмм.
- •Применение диаграмм для анализа данных.
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №9 поиск решения средствами ms excel
- •Методические указания
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №10 Работа с базами данных
- •Методические указания Обработка списков данных. Основные понятия.
- •Рекомендации по организации списка.
- •Ввод данных в список.
- •Форматирование и сортировка списка.
- •Фильтрация списков.
- •Автоматическое подведение промежуточных итогов.
- •Сводная таблица.
- •Консолидация.
- •Задания для выполнения работы
- •Исходные данные
- •Контрольные вопросы
- •Лабораторная работа №11 основы программирования в среде vba
- •Методические указания Основные понятия языка vba.
- •Range("f2").Select ‘выделение ячейки f2
- •Редактор vba.
- •Правила задания имен объектов.
- •Типы данных vba.
- •Описание переменных.
- •Использование констант.
- •Организация ввода/вывода информации.
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №12 создание пользовательских функций средствами vba
- •Методические указания Создание пользовательских функций.
- •Основные операторы языка vba.
- •Задания для выполнения работы
- •Контрольные вопросы
- •Список использованной литературы
- •Приложение а Структура отчета
- •Приложение б Пример титульного листа
Контрольные вопросы
Структура пользовательских форматов.
Коды для конструирования пользовательских форматов.
Правила создания пользовательских форматов.
Скрывающий формат.
Лабораторная работа №4 Реализация арифметических и логических вычислений
Цель работы: изучить правила записи формул; приобрести навыки по вводу формул и использованию в формулах функций; изучить методы локализации ошибок, возникающих в результате реализации арифметических и логических вычислений.
Методические указания
MS Excel оперирует с двумя основными типами данных - формулы и константы. К константам относятся числовые и текстовые значения, логические значения, а также значения даты и времени. Формула может содержать различные типы констант, встроенные или пользовательские функции и знаки арифметических, текстовых, адресных операций и операций сравнения.
Формула в MS Excel – это начинающееся со знака равно «=» выражение, состоящее из разного типа констант и (или) встроенных функций MS Excel, а также знаков арифметических, текстовых и логических операций. В формулах можно использовать следующие знаки операций (арифметические операторы в табл. 4.1 расположены в порядке возрастания приоритета).
Таблица 4.1
Виды и приоритет операций
Виды операций |
Знаки |
Арифметические |
+ (сложение) |
|
- (вычитание) |
|
* (умножение) |
|
/ (деление) |
|
% (процент) |
|
^ (возведение в степень) |
|
–Унарный минус (изменение знака) |
Текстовые |
& (конкатенация) |
Сравнения |
= (равно) |
|
< (меньше) |
|
> (больше) |
|
>= (больше или равно) |
|
<= (меньше или равно) |
|
<> (не равно) |
Адресные |
: (двоеточие) |
|
; (точка с запятой) |
|
( ) (пробел) |
Ввод формул в MS Excel отличается от ввода констант. Формулы в MS Excel начинаются с символа "=". Отсутствие лидирующего символа "=" приводит к заданию не формулы, а текстового выражения, которое не подлежит вычислению. В качестве операндов арифметических выражений можно использовать константы, ссылки (адреса ячеек), функции.
Пример 1. Вычислим значение выражения , при .
Решение. В результате вычислений выражение будет равно 1 (рис.4.1).
Рис. 4.1. Ввод исходных данных и расчетные формулы для примера 1
Приоритет унарного минуса выше приоритета возведения в степень. Поэтому правильной должна быть реализация формулы в виде .
Пример 2. Необходимо вычислить значение арифметического выражения .
Решение. Возведение в степень имеет более высокий приоритет по сравнению с делением, а деление — по сравнению со сложением, то для корректного вычисления результата следует приоритет операторов задать с помощью скобок (рис.4.2).
Рис. 4.2. Ввод исходных данных и расчетные формулы для примера 2
Существует возможность создавать более сложные функции, в которых каждый аргумент сам по себе является функцией
Для реализации логических вычислений вложенная функция, используемая в качестве аргумента, должна возвращать соответствующий этому аргументу тип данных. Например, если аргумент должен быть логическим, то есть иметь значение либо ИСТИНА, либо ЛОЖЬ, то вложенная функция в результате вычислений тоже должна давать логическое значение либо ИСТИНА, либо ЛОЖЬ. Иначе появится сообщение об ошибке «#ЗНАЧ!».
Пример 3. Вычислить значение функции , если значение аргумента располагается в ячейке , тогда
Решение. Для того, чтобы вычислить значения при разных условиях, следует воспользоваться функцией ЕСЛИ (рис.4.3). Эта функция имеет три аргумента. Первый аргумент – логическое выражение, задающее условие; второй аргумент – выражение, которое выполняется в том случае, если логическое выражение приобретает значение «истина»; третий аргумент – выражение, которое выполняется в том случае, когда логическое выражение приобретает значение «ложь».
Введенная формула будет иметь следующий вид:
Рис. 4.3. Ввод исходных данных и расчетные формулы для примера 3
В формулах можно использовать до семи уровней вложения функций. Когда функция Б является аргументом функции А, функция Б находится на втором уровне вложенности.
При использовании Мастера функций скобки при её аргументах расставляются автоматически, т.е. автоматически контролируется их баланс. Отредактировать формулу, содержащую функцию, можно как вручную, так и с использованием Мастера функции. Можно повысить читабельность сложных формул, разбивая их на логические блоки и размещая в строке формул в несколько строк (ALT–ENTER).
Список всех доступных функций, а также соответствие между русскими и английскими их названиями можно увидеть, загрузив файл FUNCS.XLS.
Переключение режимов отображения на экране результатов вычислений или формул осуществляется сбросом или установкой флажка Сервис – Параметры… – Вид – Параметры окна – формулы.
При записи формул адреса ячеек можно задавать путем ввода с клавиатуры или щелчком левой клавиши мыши по нужным ячейкам. В последнем случае адреса ячеек подставляются автоматически.
При создании формул в MS Excel пользователь может сталкиваться со стандартными ошибками (рис. 4.4).
Рис. 4.4. Примеры некоторых часто встречающихся ошибок
Значки ### - ошибки как таковой нет, просто текст не вмещается в ячейку, требуется изменить её ширину.
Деление на нуль #ДЕЛ/0.
Ссылка на ячейки с неопределенным значением: #Н/Д.
Excel не распознаёт имя или адрес ячейки: #ИМЯ? Либо собственное имя ячейки написано неверно или ячейки с таким именем не существует.
Наличие пересекающихся областей: #ПУСТО! Задано пересечение двух диапазонов, которые не имеют общих ячеек.
Числовой аргумент, находящийся за пределами допустимых значений или функция не может получить результат при заданных аргументах: # Число!
Неверная ссылка на ячейку: #ССЫЛКА! Ячейки, на которые ссылается формула, были удалены или на их место помещено содержимое скопированных ячеек.
Значение не того типа, что ожидается: #ЗНАЧ! Например, мы ссылаемся на ячейку, в которой вместо числовых данных находится текст.
Локализацию ошибок можно осуществлять следующим образом:
Выделить ячейку с ошибкой.
В строке формул выделить вызывающий сомнение фрагмент.
Нажать F9. Происходит замена выделения значением. Повторять действие до тех пор, пока не будет выявлено в каком месте формулы ошибка.
Клавиша «Esc» – отказ от сделанных изменений, возврат к исходной формуле и исправление ошибки.
При локализации ошибок в формулах можно пользоваться командой трассировки ошибок.
Чтобы проследить зависимость в формулах от адресов ячеек, можно использовать команды, которые находятся: Сервис – Зависимости формул.
«Влияющие ячейки» - стрелками будут показаны ячейки, содержимое которых влияет на значение текущей ячейки. Вторичное использование этой команды укажет ячейки, оказывающие косвенное влияние на содержимое текущей ячейки.
«Зависимые ячейки» - устанавливает стрелки, указывающие на ячейки, на содержимое которых влияет текущая ячейка.
«Источник ошибки» - стрелками указываются ячейки, которые могут быть потенциальными источниками ошибок.
«Убрать все стрелки» - удаляет с экрана отображение всех зависимостей.