Основные ошибки при создании формул

Код ошибки	Причина возникновения
# # # # #	Числовые данные не помещаются в ячейку по ширине или при вычислении по формуле из даты вычисляется более поздняя
#ДЕЛ/О	В знаменателе формулы получается нулевое значение или стоит ссылка на пустую ячейку. Последнее может оказаться связано с неверным использованием относительной ссылки
#ЗНА Ч!	У функции пропущен обязательный параметр или же в качестве параметра использована ссылка на пустую ячейку или ячейку с данными, тип которых не соответствует типу нужного параметра
#ЗНАЧ!	В качестве аргументов функции, которые должны быть единичным значением, использована ссылка на интервал
#ИМЯ?	Опечатка в имени функции или имени ячейки (диапазона) Может быть вызвана пропуском двоеточия при указании диапазона или кавычек при указании тестового параметра. Например, =СУММ(A1D10)
#ЧИСЛО!	Использован неприемлемый аргумент в функции с числовым аргументом. Например, КОРЕНЬ(-1). При вычислении получилось число, слишком большое или слишком маленькое для программы Excel. Как правило, это следствие неправильности формулы Использована функция, при вычислении которой применяются итерации, например, такие функции, как ВНДОХ или НОРМА, и при этом итерации не сходятся и результат не может быть получен
#ПУСТО!	В формуле использовано пересечение диапазонов, не включающее ни одной ячейки
#Н/Д	Значение недоступно функции или формуле: § В функциях ГПР, ПРОСМОТР, ПОИСКПОЗ или ВПР указано неверное значение аргумента «искомое_значение» § Для просмотра значений в несортированной таблице используются функции ВПР, ГПР или ПОИСКПОЗ § В формуле массива используется аргумент, не соответствующий диапазону, определяющемуся числом строк и столбцов, который указан в формуле массива § Не заданы один или несколько необходимых аргументов стандартной или пользовательской функции листа § Используется недоступная пользовательская функция
#ССЫЛКА!	Формула указывает на ячейку, которая отсутствует на рабочем листе. Чаще всего такая ошибка возникает вследствие копирования формулы с относительной адресацией влево или вверх, из-за чего возникают ссылки на столбцы предшествующие столбцу А, и строки, предшествующие первой.

 

Упр. 2. Вычисление n-ого члена и
суммы арифметической прогрессии

Данное упражнение выполняем на соответствующем листе в книге Работа (см. Упр. 1). Вычислите n-ый член и сумму арифметической прогрессии. При расчетах в таблице воспользуйтесь формулой a_n=a_n-1+d, где a_n -n-ый член прогрессии и S_n=(a₁+a_n)*n/2 –сумма n первых членов прогрессии. Разность арифметической прогрессии d=0,725, а первый член а₁=-2. При вводе данных обратите внимание на настройки Excel!

Технология работы:

1. Введите в ячейку А1 заголовок таблицы (Рис. 11).

2. В ячейку A2 введите d, в ячейку B2 – n, в C2 - a_n, в D2- S_n. Примените полужирный стиль начертания символов и выровняйте их по центру. Для набора нижних индексов воспользуйтесь командой Формат, Ячейка, вкладка Шрифт.

3. В ячейку А3 введите величину разности d (0,725) и заполните столбец ячеек тем же числом. Для этого выделите ячейку А3. При этом данная ячейка окаймляется рамкой, в правом нижнем углу которой располагается маркер заполнения. Подведите указатель мыши к маркеру, мышь примет вид черного креста, протяните маркер на несколько ячеек вниз, выделенные ячейки заполнятся данными.

4. В ячейку В3 введите 1 и протяните за маркер заполнения на 10 ячеек вниз, при этом удерживайте клавишу Ctrl.

5. В ячейку С3 введите значение первого члена прогрессии, которое равно -2.

6. При n>1, a_n=a_n-1+d, поэтому в ячейку С4 введите формулу =С3+А3. Для этого в ячейке С4 наберите знак равенства, щелкните мышью по ячейке С3, в строке формул появится ее адрес, затем введите с клавиатуры знак “+” и щелкните мышью по ячейке А3. Зафиксируйте ввод формулы нажатием клавиши Enter, в ячейке окажется результат вычисления по формуле, а в Строке формул - сама формула. Если вы неправильно набрали формулу, исправить ее можно в Строке формул, предварительно выделив ячейку.

7. В расчетах будем использовать формулу s_n=a₁+a₂+a₃+…+a_n, здесь s₁=a₁, поэтому в ячейку D3 введите число равное -2. В ячейку D4 введите формулу = D3+C4. Для ввода формулы воспользуйтесь советами пункта № 6 этого упражнения.

8. Для оформления ячеек с данными выделите эти ячейки и выполните команду Формат, Столбец, Автоподбор ширины.

9. Для оформления заголовка выделите интервал ячеек от А1 до D1 и выполните команду Формат, Ячейка, вкладка Выравнивание и установите переключатели Объединение ячеек и Переносить по словам. Если заголовок таблицы виден не полностью, то увеличьте ширину данной строки (Рис. 12).

10. Установите границы таблицы, выполнив команду Формат, Ячейка, вкладка Граница.

 

Рис. 11. Таблица к упр.2

Рис. 12. Изменение ширины строки

11. Просмотрите таблицу, для этого выполните команду Файл, Предварительный просмотр.

Упр. 3. Создание таблицы
Cведения о товарах и ценах с учетом налога с продаж

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

Технология работы:

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

Рис. 13. Таблица, содержащая сведения о товарах и ценах с учетом налога с продаж

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

1. В ячейку D5 введите формулу =C5*$C$1. Для получения абсолютной ссылки $C$1 нажмите клавишу F4 после того, как поместите в строку формул ссылку C1.

2. В ячейку E5 введите =C5+D5

3. Выделите блок из двух ячеек D5:E5 с формулами для первого товара и скопируйте его на диапазон D5:E9, используя маркер заполнения.

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

Для просмотра таблицы в режиме отображения формул выполните следующие действия:

· выполните команду Сервис/Параметры…,

· в диалоговом окне Параметры выберите вкладку Вид,

· в группе Параметры окна щелчком мыши включите (а после просмотра выключите) переключатель Формулы.

Рис. 14. Таблица в режиме отображения формул

Упр. 4. Создание таблицы умножения

Создайте таблицу умножения чисел от 1 до 10 (Рис. 15).

Для этого в ячейку А1 введите заголовок таблицы. В ячейки В2 по K2 и А3 по А12 введите значения от 1 до 10, используя маркер заполнения. В ячейку В3 введите формулу произведения ячеек В2 и А3, применив нужные ссылки. Растяните данную формулу, используя маркер заполнения по всей площади таблицы.

 

Рис. 15. Бланк для заполнения таблицы умножения

Упр. 5. Создание бланка Счёта

Создайте бланк Счета, используйте как образец, приведенную ниже таблицу (Рис. 16). Бланк Счета заполняется произвольно. Пунктирные линии в примере служат только для наглядной и правильной разметки таблицы, которой не должно быть в таблице. В документе сетку Excel отключить.

Технология работы:

1. Создайте документ по образцу, используя объединение ячеек.

2. Текст: “Грузоотправитель и адрес”, “Грузополучатель и адрес”, “К Реестру №”, “Поставщик:”, “Адрес:”, “Р/счет №”, “Дополнения:”, “ИТОГО”, “Руководитель предприятия”, “Главный бухгалтер” - внесите в ячейки самого левого столбца и примените выравнивание по левому краю.

3. “Фамилии” и “Дата получения” внесите в ячейки самого правого столбца и примените выравнивание по правому краю.

4. Текст “Счет № ” внесите в ячейку самого левого столбца и примените объединение на нужные ячейки и выравнивание по центру.

5. К заголовкам в таблице Бланка примените выравнивание по центру, как по горизонтали, так и по вертикали.

6. К записям таблицы Бланка примените выравнивание по левому краю.

7. Строки в столбце под “№” в таблице Бланка пронумеруйте от 1 до 10, используя маркер заполнения.

8. Заполните бланк своими данными.

9. В столбцах “Цена”, ”Сумма” таблицы Бланка установите денежный формат числа и число десятичных знаков равно 2.

Рис. 16. Бланк Счета c разметкой таблицы

III.РАБОТА СО ВСТРОЕННЫМИ ФУНКЦИЯМИ

III.1. Описание и способы ввода функций

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

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

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

Математические функции используют в качестве аргументов числовые значения. Например, функция СУММ(D1:D10; 23; D15) вычисляет сумму числовых значений диапазона ячеек D1: D10, числа 23 и значения ячейки D15.

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

Если аргументы функции не попадают в область допустимых значений, то функция принимает значение #Н/Д (неопределенные данные, см.Таблица 3).

Функции в Exсel вводятся обычным набором с клавиатуры или более предпочтительным способом - с помощью Мастера функций (Рис. 17), диалоговое окно которого вызывается командой Вставка, Функция или кнопкой

Рис. 17. Диалоговое окно Мастер функций для выбора категории и вида функции

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

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

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

Формулу вводят в ячейку. Для вставки в формулу вложенных функций предусмотрена кнопка вызова функции, которая находится в левой части строки формул (Рис. 19). Глубина вложенности зависит от вида функции. Узнать глубину вложенности функции можно через встроенную справку MS Excel.

Рис. 18. Диалоговое окно для задания аргументов
логической функции ЕСЛИ

Рис. 19. Использование кнопки вызова функции в строке формул

Упр. 6. Расчёт расхода материалов для покраски

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

Рис. 20. Таблица расход материалов для покраски

Рассчитайте расход материалов для двери (R_Д) и подоконника (R_П), воспользовавшись формулами R_Д=S₁*S_Д / 10 и R_П=S₂*S_П / 10, где S₁ и S₂ свои значения. Вычислите максимальный, минимальный и расход материалов для двери и подоконника. Подсчитайте суммарный расход материалов для двери и подоконника в строке Всего, воспользовавшись кнопкой на панели инструментов - Автосумм. Для подсчета общего расхода в строке Итого сложите суммарный расход материалов для двери и подоконника.

Упр. 7. Использование математических и
статистических функций

Вычислите сумму чисел, среднее значение, посчитайте общее количество чисел, количество нулевых элементов, количество положительных и сумму отрицательных для последовательность чисел – 5;-41;0;71;13;-11;20;0;15.

Технология работы:

В ячейки В2:J2 внесите данные.

Для вычисления формул в диапазон В4:В11 введите соответствующие функции:

В4 = СУММ(B2:J2); В5 = СЧЕТ(B2:J2); В6 = СЧЁТЕСЛИ(B2:J2; “=0”); В7 = СЧЁТЕСЛИ(B2:J2; “>0”); В8 = СУММЕСЛИ(B2:J2; “<0”);
В11 = СРЗНАЧ(B2:J2).

В ячейки А4:А11 введите названия функций и проанализируйте работу.

Упр. 8. Изучение итоговых функций

Технология работы:

В ячейки А1:А4 введите исходные данные на основе которых будут вычисляться функции. В столбец С введите значения для проверки результатов, которые получатся в результате выполнения функций приведённых в столбце D (Рис. 21) и проанализируйте их работу.

	A	B	C	D
				=СЧЕТ(A1:A4)
				=СЧЕТЗ(A1:A4)
	куб			=МИН(A1:A4)
				=МИНА(A1:A4)
				=МАКС(A1:A4)
				=МАКСА(A1:A4)
				=СРЗНАЧ(A1:A4)
				=СРЗНАЧА(A1:A4)
				=ДИСП(A1:A4)
				=ДИСПА(A1:A4)
				=ДИСПР(A1:A4)
			7,5	=ДИСПРА(A1:A4)
				=СТАНДОТКЛОН(A1:A4)
			3,162	=СТАНДОТКЛОНА(A1:A4)
				=СРОТКЛ(A1:A4)
				=НАИБОЛЬШИЙ(A1:A4;1)
				=НАИМЕНЬШИЙ(А1:А4;1)

Рис. 21. Список итоговых функций

III.2. Применение условного форматирования для таблиц

Условный формат - формат (например, заливка ячейки или цвет шрифта), который Microsoft Excel автоматически применяет к ячейке, если выполняется указанное условие. Для использования условного форматирования выберите командуФормат, Условное форматирование.

Упр. 9. Показатели производства

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

Технология работы:

1 Заполните таблицу исходными данными (Рис. 22). Обратите внимание на три столбца под общим заголовком Месяцы. Эти столбцы дублируют друг друга, обозначая одни и те же временные промежутки, существенное отличие все же есть. Здесь использованы альтернативные способы работы в режиме Автозаполнения: построение числовых рядов, рядов из дат, использование стандартного списка. Поэтому рекомендуется в учебных целях применить все три способа, отрабатывая навыки работы в этом режиме. Рассмотрим эти способы:

Рис. 22. Показатели производства

· ячейку В5 и за маркер заполнения правой кнопкой мыши протяните до ячейки В16 включительно, выберите Заполнить по месяцам

· для диапазона ячеек С5:С16 воспользуйтесь стандартным списком из названий месяцев: наберите в ячейке С5 текст Январь; выберите ячейку С5 и установите курсор мыши на маркер заполнения, нажмите левую кнопку мыши и протяните ее до ячейки С16 включительно

2 Переходим к расчету и анализу итогов работы предприятия. Введите в ячейку F5 формулу для вычисления процента выполнения плана за месяц: =Е5/D5. Выделите ячейку F5 и выполните автоматическое заполнение формулами диапазона ячеек F5:F16, используя маркер заполнения. Представьте полученные данные в процентном формате с тремя десятичными знаками.

3 В ячейке D18 вычислите значение планового задания по выпуску на год — сумму значений диапазона D5:D16, используя инструмент Автосумма. Выберите ячейку D18 и протяните формулу на ячейку E18.

4 В ячейку F18 скопируйте формулу из ячейки F16.

5 В диапазоне ячеек G5:G16 вычислите для каждого месяца его долю (в процентах) в годовом выпуске, которая вычисляется как отношение выпушенного в каждом месяце к выпущенному за год. Так как во всех формулах диапазона в качестве делителя выступает одна и та же ячейка (сумма за год), то в формуле адрес этой ячейки должен быть задан в виде абсолютной ссылки, чтобы он не изменялся при копировании формулы. Формула в ячейке G5 должна иметь вид: =Е5/$Е$18. Выберите ячейку G5 и протяните формулу до ячейки G16, включительно. Представьте полученные данные в процентном формате с двумя десятичными знаками.

6 В ячейке Е20 с помощью Мастера функций посчитайте максимальное значение диапазона ячеек E5:E16.

7 В ячейке Е21 с помощью Мастера функций посчитайте минимальное значение диапазона ячеек E5:E16.

8 В ячейке Е22 с помощью Мастера функций посчитайте среднее значение диапазона ячеек E5:E16.

9 Выделите диапазон ячеек Е20:Е22 и скопируйте его на диапазон F20:F22, используя маркер заполнения.

10 Для диапазона F20:F22 установите процентный формат с двумя цифрами дробной части.

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

· Можно выполнить условное форматирование для диапазона F5:F16 с целью выделения тех ячеек, в которых значение меньше 1 (или, что тоже самое, меньше 100%). Для этого выделите диапазон F5:F16 и выполните команду Формат, Условное форматирование...; появится диалоговое окно Условное форматирование (Рис. 23.)

Рис. 23. Диалоговое окно Условное форматирование

· Раскройте список во втором поле и выберите строку меньше, в третье поле введите значение 1 или 100%, нажмите кнопку Формат..., появится диалоговое окно Формат ячеек с вкладками Шрифт, Граница, Вид. Определите формат для значений, удовлетворяющих заданному условию, например, измените цвет и начертание. Закройте диалоговые окна, нажмите кнопку ОК.

· Значения в диапазоне ячеек С5:С16 можно выровнять по центру.

· Расположите заголовок таблицы Показатели производства по центру таблицы. Для этого выделите диапазон А1:G1 и нажмите кнопку Объединить и поместить в центре. Можно изменить размер шрифта заголовка и его начертание.

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

· Можно изменить цвет фона и цвет шрифта отдельных элементов таблицы.