Разработка электронных таблиц

Типы полей электронной таблицы

Таблица в Excel независимо от ее назначения, имеет четыре поля:

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

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

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

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

Расположение полей зависит от содержания решаемой задачи, объема вычислений, объема исходных данных и способа их ввода и ряда других факторов. При этом необходимо исходить из общего принципа: расположение полей должно обеспечивать наглядность представления материала и доступность данных, удобство их ввода, использования и корректировки. Пример размещения полей приведен на рис. 2.1.

 

1/1
1/2			4/2
	4/1
Рис. 2.1. Размещение полей электронной таблицы

Функции электронной таблицы

Excel имеет 11 категорий различных функций: математические/тригонометриче­ские; инженерные; логические; текстовые; статистические; функции категории дата/время; функции для работы с базами данных/списками; финансовые; информационные и функции категории ссылки/массивы; функции проверки свойств и значений. Кроме того Excel содержит большое число надстроечных функций, которые используются для создания компьютерных программ в Excel, а также имеется возможность создания пользовательских функций и программ на Visual Basic for Applications. Можно написать программы на других языках программирования высокого уровня, например, С, FORTRAN и потом вызвать их в Excel.

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

Окно диалога имеет два списка. В левом списке приведены категории функций, а в правом – функции. В списке категорий последней в списке будет категория “Пользовательские функции”. В эту категорию попадают функции, созданные пользователем с помощью встроенного языка программирования Visual Basic for Application.

Ниже окон списков выводятся текстовые строки, в которых отображается синтаксис выделенной функции и ее назначение. Слева в нижней части окна диалога расположена кнопка, которая выводит контекстную подсказку по выбранной функции. При щелчке по кнопке ОК на экран выводится окно диалога для ввода данных в шаблон функции.

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

Некоторые функции приведены в табл.2.1.

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

 

Таблица 2.1
Функции электронной таблицы
	Математические
ABS()	Абсолютное значение числа
ФАКТР()	Факториал числа
ЦЕЛОЕ()	Число, округленное до ближайшего меньшего целого
ОСТАТ()	Модуль(остаток от деления двух чисел)
СЛЧИС()	Случайное число от 0 до 1
КОРЕНЬ()	Квадратный корень из числа
СУММА()	Сумма чисел в списке
СУММЕСЛИ()	Сумма значений в ячейках, соответствующих заданному критерию
СУММПРОИЗВ()	Сумма произведений элементов массивов
СУММКВ()	Сумма квадратов чисел в списке
СУММРАЗНКВ()	Сумма разностей квадратов элементов в двух массивах
СУММСУММКВ()	Сумма сумм квадратов элементов в двух массивах
СУММКВРАЗН()	Сумма квадратов разностей значений в двух массивах
	Логарифмические функции
EXP()	Число е, возведенное в степень
LN()	Натуральный логарифм числа (основание “е”)
LOG()	Логарифм числа по заданному основанию LOG(число,основание)
LOG10()	Логарифм числа по основанию 10
	Тригонометрические функции
ПИ()	Возвращает значение числа π
COS()	Косинус числа
SIN()	Синус числа
TAN()	Тангенс числа
	Обратные тригонометрические функции
ACOS()	Арккосинус числа
ASIN()	Арксинус числа
ATAN()	Арктангенс числа от -p/2 до p/2
ATAN2()	Арктангенс отношения двух чисел (от -p до p)
	Функции преобразования угла
ГРАДУСЫ()	Показатель величины угла в градусах
РАДИАНЫ()	Показатель величины угла в радианах
	Матричные функции
МОПРЕД()	Определитель матрицы
МОБР()	Матрица, обратная заданной
МУМНОЖ()	Произведение двух матриц
ТРАНСП()	Транспонирование матрицы

 

Генерирование данных

Часто бывает необходимо сгенерировать последовательность чисел, дат.

Для этой цели можно использовать механизм автозаполнения. Чтобы заполнить несколько ячеек прогрессией, необходимо записать в смежные ячейки данные, отличающиеся на величину шага, выделить эти ячейки, и перетащить маркер заполнения выделенного диапазона ячеек. Можно также воспользоваться командой Прогрессия программы Excel. Внесите в ячейку начальное значение ряда чисел; выделите область для заполнения, выберите пункт Заполнить в меню Правка, а затем щелкните пункт Прогрессия. На экран выводится диалоговое окно Прогрессия (рис. 2.3). Выберите Тип прогрессии, Расположение в соответствии с выделенной областью, Шаг и щелкните по кнопке ОК.

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

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

Для заполнения ячеек часто используемыми текстовыми записями можно создавать пользовательские прогрессии. Для этого выполните следующее: выберите команду Параметры в меню Сервис и откройте затем закладку Списки. В окне списки выберите строку ” Новый список ” и введите данные в поле “ Элементы списка ”. После ввода каждой новой записи нажимайте клавишу Enter. Для завершения работы щелкните по кнопке Добавить.

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

- введите в ячейку А1 текст “Начальное значение”, а в ячейку В1 начальное значение ряда;

- введите в ячейку А2 текст “Шаг табуляции”, а в ячейку В2 значение шага табуляции (приращение аргумента);

- запишите в ячейку А4 начальное значение ряда путем ссылки на ячейку В1: выделите ячейку В4 и запишите в нее формулу: = В1;

- запишите в ячейку А5 формулу арифметической прогрессии A4+$B$2;

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

- скопируйте формулу из ячейки А5 в остальные ячейки диапазона.

Табулирование функций

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

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

Пример табулирования функции одной переменной приведено на Рис.2.4. Для выполнения операции табулирования необходимо:

- сгенерировать ряд значений аргумента на заданном интервале;

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

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

Пример табулирование функции двух переменных приведен на Рис.2.5.

 

	А	B	C	D	E
	Шаг 1-го аргумента	0,5	Шаг 2-го аргумента	0,2
		0,1	B2+$D$1	C2+$D$1	D2+$D$1
		2*$A3+B$2^2	2*$A3+C$2^2	2*$A3+D$2^2	2*$A3+E$2^2
	A3+$B$1	2*$A4+B$2^2	2*$A4+C$2^2	2*$A4+D$2^2	2*$A4+E$2^2
	A4+$B$1	2*$A5+B$2^2	2*$A5+C$2^2	2*$A5+D$2^2	2*$A5+E$2^2
	A5+$B$1	2*$A6+B$2^2	2*$A6+C$2^2	2*$A6+D$2^2	2*$A6+E$2^2
	A6+$B$1	2*$A7+B$2^2	2*$A7+C$2^2	2*$A7+D$2^2	2*$A7+E$2^2
	A7+$B$1	2*$A8+B$2^2	2*$A8+C$2^2	2*$A8+D$2^2	2*$A8+E$2^2
	A8 +$B$1	2*$A9+B$2^2	2*$A9+C$2^2	2*$A9+D$2^2	2*$A9+E$2^2

Рис. 2.5. Табулирование функции двух переменных

 

Порядок выполнения операции следующий:

- запишите в ячейку А3 начальное значение аргумента Х;

- запишите в ячейку В2 начальное значение аргумента Y;

- запишите в ячейки A4 и C2 формулы для генерирования рядов значений аргументов;

- скопируйте в ячейки А5:А9 формулу для вычисления аргумента Х из ячейки А4;

- скопируйте в ячейки D2:E2 формулу для вычисления Y из ячейки C2;

- запишите в ячейку В3 таблицы расчетную формулу с использованием смешанных адресов ячеек: У первого аргумента зафиксируйте столбец, а у второго аргумента - строку;

- скопируйте формулу во все ячейки блока.

Совет: Общее правило при копировании формул со смешанными адресами: Если данные находятся в строке, то фиксируется номер строки, а если данные находятся в столбце то фиксируется номер столбца.

 

Для табулирования функций одной и двух переменных можно использовать средства Excel: команду Таблица подстановки из меню Данные. В ходе выполнения Лабораторной работы №2 будут рассмотрен алгоритм использования команды Таблица подстановки.

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

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

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

3. Как протабулировать функцию одной переменной?

4. Как протабулировать функцию двух переменных?

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

6. Опишите алгоритм табулирования функции двух переменных с использованием команды Таблица подстановки.

Лабораторная работа №2

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

 

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

Функция ЕСЛИ

Функция ЕСЛИ имеет две синтаксические формы. Синтаксис 1 можно использовать для рабочих листов и листов макросов. Синтаксис 2 можно использовать только для листов макросов вместе с функциями ИНАЧЕ, ИНАЧЕ.ЕСЛИ и КОН.ЕСЛИ.

Функция ЕСЛИ используется для проверки значений формул и орга­низации переходов в зависимости от результатов этой проверки. Результат проверки определяет значение, возвращаемое функцией ЕСЛИ. Данная функция имеет следующий синтаксис 1 для рабочих листов и листов мак­росов:

= ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь)

Лог_выражсние — это любое значение или выражение, которое при вы­числении дает значение ИСТИНА или ЛОЖЬ.

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

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

При составлении формулы необходимо помнить, что до 7 функций ЕС­ЛИ могут быть вложены друг в друга в качестве значений аргументов значе-ние_если_истина и значение_если_ложь, чтобы конструировать более сложные проверки; если функция ЕСЛИ используется в макросе, то значение_если_истина и значение_если_ложь могут быть функциями ПЕРЕЙТИ, другими макроса­ми или действиями.

Если какой-либо аргумент функции ЕСЛИ является массивом, то при выполнении функции ЕСЛИ вычисляется каждый элемент массива.

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

Примеры синтаксиса функции ЕСЛИ рассмотрены ниже в сочетании с другими логическими функциями для создания более сложных условий.

 

Функция И

Функция И возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и возвращает значение ЛОЖЬ, если хотя бы один ар­гумент имеет значение ЛОЖЬ. Синтаксис этой функции следующий:

= И (логическое_значение1; логическое_значенис2;...)

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

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

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

Если указанный интервал не содержит логических значений, то И воз­вращает значение ошибки #ЗНАЧ!

Примеры синтаксиса функции и результат:

= И(ИСТИНА; ИСТИНА) равняется ИСТИНА;

=И(ЛОЖЬ; ИСТИНА) равняется ЛОЖЬ;

=И(2+2=4; 2+3=5) равняется ИСТИНА.

Если интервал В1:В3 содержит значения ИСТИНА, ЛОЖЬ и ИСТИНА, то

=И(В1:ВЗ) равняется ЛОЖЬ.

Если ячейка В4 содержит число между 1 и 100, то

=И(1<В4; В4<100) равняется ИСТИНА.

Предположим, что нужно вывести на экран содержимое ячейки В4, ес­ли она содержит число строго между 1 и 100 и сообщение "Значение вне ин­тервала" в противном случае. Тогда если ячейка В4 содержит число 104, то выражение:

=ЕСЛИ(И(КВ4; В4<100); В4; "Значение вне интервала") равняется "Значение вне интервала", а если ячейка В4 содержит число 50, то выра­жение:

=ЕСЛИ(И(КВ4; В4<100); В4; "Значение вне интервала") равняется 50.

 

Функция ИЛИ

Функция ИЛИ возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА, и возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ. Данная функция имеет следующий синтаксис:

= ИЛИ (логическое_значение1; логическое_значение2;...)

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

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

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

Если заданный интервал не содержит логических значений, то функция ИЛИ возвращает значение ошибки #ЗНАЧ!

Можно использовать функцию ИЛИ как формулу массива, чтобы про­верить, имеются ли значения вмассиве. Для того чтобы ввести функцию ИЛИ как формулу массива, нажмите CTRL+SHIFT.

Примеры синтаксиса:

=ИЛИ (ИСТИНА) равняется ИСТИНА;

=ИЛИ (1+1=1; 2+2=5) равняется ЛОЖЬ.

Если ячейки А1:АЗ содержат значения ИСТИНА, ЛОЖЬ и ИСТИНА, то
=ИЛИ (А1:АЗ) равняется ИСТИНА.

Следующая макроформула проверяет содержимое активной ячейки. Если ячейка содержит единственный символ "с" или "s", то функция ИЛИ вернет значение ИСТИНА и макрос переходит в область Конец-Очистки:

=ЕСЛИ(ИЛИ(АКТИВНАЯ.ЯЧЕЙКА()="с";АКТИВНАЯ.ЯЧЕЙКА()="s"); ПЕРЕЙТИ(КонецОчистки))

 

Функция НЕ

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

= НЕ (логическое_значение)

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

Примеры:

=НЕ (ЛОЖЬ) равняется ИСТИНА;

=НЕ(1 + 1=2) равняется ЛОЖЬ.

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

1. Что является результатом работы функции ЕСЛИ?

2. Что является результатом работы функции И?

3. Что является результатом работы функции ИЛИ?

4. Что является результатом работы функции ЕСЛИ?

Лабораторная работа №3