Электронные таблицы MS Excel

Альшевская О.В.

Галай Т.А.

 

 

Электронные таблицы MS Excel

 

Учебное электронное издание

 

Минск 2012

УДК 004.67

 

 

Рецензент: Н.А.Разоренов

 

 

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

 

Белорусский национальный технический университет

пр-т Независимости, 65, г. Минск

 

 

ã Альшевская О.В.

ã Галай Т.А., 2012

Содержание

Тема 1. Общее управление в MS Excel. Категории и форматы данных. Создание и сохранение таблиц MS Excel. Загрузка и редактирование таблиц. 5

Теоретические сведения. 5

Практические задания. 13

Лабораторная работа № 1 (4 часа) 13

Контрольные вопросы к теме. 15

Тема 2. Форматирование таблиц MS Excel 17

Теоретические сведения. 17

Создание числового формата. 17

Символы-заменители. 17

Форматирование даты и времени суток. 17

Пользовательский формат. 18

Примеры.. 18

Практические задания. 20

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

Вариант 1. 20

Контрольные вопросы к теме. 21

Тема 3. Способы адресации. Математические функции. 23

Теоретические сведения. 23

Адресация в Excel 23

Связывание листов. 23

Применение имен. 23

Вставка функций. 24

Функции округления. 24

Табличные формулы.. 24

Примеры.. 25

Практические задания. 28

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

Контрольные вопросы к теме. 30

Тема 4. Использование функций. 32

Теоретические сведения. 32

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

Логические и статистические функции. 32

Текстовые функции. Функции ссылок и массивов. 33

Примеры.. 34

Практические задания. 38

Лабораторная работа № 4 (4 часа) 38

Вариант 1. 38

Вариант 2. 40

Контрольные вопросы к теме. 42

Тема 5. Построение и форматирование диаграмм. 44

Теоретические сведения. 44

Практические задания. 52

Лабораторная работа № 5 (4 часа) 52

Контрольные вопросы к теме. 57

Темы 6. Обработка списков данных. Вычисление промежуточных итогов. Сводные таблицы 58

Теоретические сведения. 58

Создание списка. 58

Практические задания. 65

Лабораторная работа № 6 (4 часа) 65

Контрольные вопросы к теме. 68

Тема 7. Подбор параметра, поиск оптимального решения. Поиск экстремума функции. 69

Теоретические сведения. 69

Практические задания. 77

Лабораторная работа № 7 (4 часа) 77

Контрольные вопросы к теме. 78

Тема 8. Обмен данных между MS Excel и другими приложениями MS Office. 79

Теоретические сведения. 79

Связанные и внедренные объекты.. 79

Создание и редактирование связанных и внедренных объектов. 79

Практические задания. 80

Лабораторная работа № 8. 80

Тема 9. Автоматизация работы в Excel. 83

Теоретические сведения. 83

Запись и выполнение макросов. 83

Практические задания. 86

Лабораторная работа № 9. 87

Контрольные вопросы к теме. 88

Тема 10. Моделирование данных. Оценка частотного распределения случайной величины. Сглаживание экспериментальных данных. 89

Теоретические сведения. 89

Практические задания. 93

Лабораторная работа № 10 (4 часа) 93

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

Тема 11. Средства регрессионного анализа в Excel. 96

Теоретические сведения. 96

Практические задания. 103

Лабораторная работа № 11. 103

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

Литература. 104

 

 

Тема 1. Общее управление в MS Excel. Категории и форматы данных. Создание и сохранение таблиц MS Excel. Загрузка и редактирование таблиц.

 

Теоретические сведения

На оглавление

Электронные таблицы MS Excel предназначены для обработки таблично организованной информации. Особенностью электронных таблиц является структу­рирование информации непосредственно на этапе ввода данных — данные и формулы хранятся в ячейках рабочего листа (рис. 1.1). Рабочий лист Excel 2007 состоит из 1 048 576 строк и 16 384 столбцов, т.е. из более 17 миллиардов ячеек. Совокупность листов составляет рабочую книгу, которая сохраняется как целостный объект в одном файле с расширением xlsx.

Рис. 1.1. Слои ячейки

Настройки окна MS Excel

Рис. 1.2. Основные элементы окна MS Excel

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

Настройки окна Excel производятся с помощью кнопок на закладке Вид, а также при изменении параметров Excel. Для этого нужно нажать кнопку Office и щелкнуть по кнопке .Параметры Excel разделены на 9 категорий (рис. 1.3). В этом окне можно изменить количество листов в книге, стандартный размер и шрифт в ячейках (категория Основные), способ пересчета формул и стиль ссылок (категория Формулы), установить режим показа формул в ячейках листа, показать/убрать линии сетки, заголовки строк и столбцов, полосы прокрутки (категория Дополнительно), изменить параметры автосохранения (категория Сохранение) и др. С помощью кнопки (категория Основные) можно открыть диалог для создания пользовательского списка. Для этого нужно в левой части этого диалогового окна выбрать строку НОВЫЙ СПИСОК, а в правой части ввести элементы списка, нажимая после каждого элемента Enter.

 

Рис. 1.3. Параметры настроек Excel

Форматирование ячеек

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

Диалог Формат ячеек (рис. 1.4) состоит из шести закладок:

Рис. 1.4. Диалоговое окно Формат ячеек

ü Число — предназначена для форматирования значений ячеек;

ü Выравнивание — позволяет:

- расположить содержимое ячейки в любом положении по отношению к ее границам;

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

- объединить несколько предварительно выделенных ячеек в одну (флажок Объединение ячеек);

ü Шрифт — дает возможность изменять шрифт, размер и цвет шрифта, начертание, подчеркивание, эффекты (верхний и нижний индекс, зачеркивание);

ü Граница — позволяет задавать обрамление ячеек, цвет и тип линии обрамления;

ü Заливка — можно изменять фон и узор ячеек;

ü Защита — используется при создании шаблонов.

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

Создание шаблонов

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

Чтобы создать шаблон, нужно:

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

– выделить ячейки, в которые будет вноситься переменная информация, и снять с них защиту (в диалоге Формат ячеек на закладке Защита снять флажок Защищаемая ячейка);

– если нужно скрыть формулы, выделить ячейки с формулами и в диалоге Формат ячеек на закладке Защита установить флажок Скрыть формулы;

– установить защиту, нажав кнопку Защитить лист на закладке Рецензирование панели инструментов. Можно также указать пароль;

– нажать кнопку Office , щелкнуть по команде Сохранить как и выбрать Тип файла: Шаблон Excel (имеет расширение xltx) или Шаблон Excel с поддержкой макросов (имеет расширение xltm). Обратить внимание, в какой папке будет сохранен шаблон, чтобы при необходимости его можно было открыть для редактирования;

– закрыть созданный шаблон.

Чтобы воспользоваться шаблоном, нужно нажать кнопку Office , щелкнуть по команде Создать. В появившемся окне Создание книги выбрать категорию Мои шаблоны, найти нужный шаблон и дважды щелкнуть по его пиктограмме. При этом на основе шаблона создается новый файл, а шаблон остается в своей папке неизменным.

Практические задания

На оглавление

Лабораторная работа № 1 (4 часа)

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

Задания:

I. Ввод данных и формул.

1. В ячейки столбца А ввести следующие данные:

1.1. Число в виде десятичной дроби.

1.2. Число в виде простой дроби.

1.3. Число 7000000000000 (ширину столбца не увеличивать). Просмотреть содержимое ячеек с введенными данными в строке формул.

1.4. Отрицательное число.

1.5. Число 30.

1.6. Число 30%.

1.7. Время окончания пары.

1.8. Дату своего рождения.

1.9. Свою фамилию.

1.10. Слово истина.

2. Увеличить ширину столбца A. Сделать вывод о типах введенных данных и их выравнивании в ячейке.

III. Автозаполнение.

1. На новом листе в ячейки А1:Е1 ввести соответственно следующие данные: в A1 - любое число, B1 - дату, C1 - время, D1 - текст, E1 - формулу =В1+7.

2. С помощью маркера заполнения протянуть вниз: число на 10 строк, дату и время на 8, текст на 5, формулу на 8. Сделать вывод о правилах работы маркера с разными типами данных и формулами.

3. В ячейки А11:А19 ввести данные: январь, вторник, фев, чт, 1 кв, 2 квартал, 1 кв. 2004, 1 вопрос (между цифрой и текстом есть пробел), 1вопрос (между цифрой и текстом пробела нет).

4. С помощью маркера заполнения протянуть введенные данные вправо на 12 столбцов.

5. В ячейку А20 ввести число 4, выделите диапазон A20:E20, выполнить команду вкладка Главная – Редактирование – кнопка Заполнить – Прогрессия. В диалоге выбрать Геометрическая прогрессия, с шагом 3.

6. В ячейку А21 ввести дату своего рождения. Установить курсор на ячейку А21 выполнить команду вкладка Главная – Редактирование – кнопка Заполнить – Прогрессия. В диалоге выбрать единицы Год, шаг - 5, предельное значение - Текущая дата.

7. В ячейку А22 ввести время 8:00, в ячейку А23 ввести время 8:30. Выделить эти две ячейки и маркером автозаполнения заполнить пять строк вниз. Оценить результат.

8. Открыть диалог кнопка Офис — Параметры Excel — Основные —кнопка Изменить списки. Создать новый список из фамилий студентов своей подгруппы. Закрыть диалог.

9. По аналогии с пунктами 3, 4 заполнить ячейки списком фамилий.

IV. Форматирование.

1. На новом листе в ячейку В2 ввести какое-либо слово, например, Весна. С помощью маркера скопировать его в ячейки С2:Е2, а затем в ячейки B3:D5.

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

VIII. Создание шаблона.

1. На листе «Треугольник» очистить содержимое ячеек B2, D2, F2 и снять с них защиту.

2. Скрыть все формулы.

3. Установить защиту листа.

4. Сохранить книгу как шаблон.

5. Закрыть шаблон. Создать файл на основе шаблона и заполнить его.

 

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

1. Назовите правила ввода даты и времени в ячейки рабочего листа Excel.

2. Как добавить новый лист в конец рабочей книги, как переместить лист?

3. Какие команды служат для управления видом окна Excel?

4. Как настраивать ширину столбцов и высоту строк?

5. Как ввести число в виде денежной единицы, в виде натуральной дроби?

6. Как вводятся формулы, каково назначение строки формул?

7. Как изменить стиль ссылок с обычного на R1C1 и обратно?

8. Что означает появление символов ##### в ячейке рабочего листа?

9. Как осуществляется копирование и перемещение ячеек рабочего листа?

10. Как очистить весь рабочий лист?

11. Как завершить ввод данных в ячейку? Каким образом можно редактировать содержимое ячеек?

12. Как переименовать, скопировать и удалить рабочий лист?

13. Перечислите возможности форматирования ячеек в Excel.

14. Как создать пользовательский список?

15. Как создать ряд для поквартального отчета (1 квартал 1999 и т.д.)?

16. Как создать ряд 1 раздел, 2 раздел, 3 раздел и т.д.

17. Как вставить в колонтитул текущую дату; текущее время; имя файла; имя листа; номер страницы; количество страниц?

18. Можно ли в режиме предварительного просмотра изменять:

a) данные рабочего листа,

b) ширину столбцов,

c) высоту строк,

d) колонтитулы,

e) поля?

Если можно, то каким образом?

19. Каким образом можно разместить большую таблицу на одной странице?

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

21. Назовите арифметические операторы, применяемые в Excel, и порядок их обработки.

22. Как можно вставить в формулу ссылку на ячейку?

23. Для чего применяются шаблоны, где они хранятся и какое имеют расширение?

24. Как осуществляется защита шаблона?

Теоретические сведения

На оглавление

Создание числового формата

Для форматирования числовых данных используется закладка Число диалогового окна Формат ячеек (рис. 2.1). Чтобы отформатировать число, можно выбрать встроенный формат. Если ни один из встроенных форматов не подходит, можно создать пользовательский. Для этого в списке Числовые форматы следует выбрать (все форматы) и затем в поле Тип написать формат, используя символы-заменители.

Символы-заменители

# выводятся только значащие числа, незначащие нули не отображаются;

0 отображаются незначащие нули, если количество разрядов числа меньше количества нулей в формате;

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

V (пробел) разделяются тысячи, но если пробел стоит в конце форматного кода, то число масштабируется в тысячу раз;

Основной числа и текст выводятся в неизменном виде, а даты и время — во внутреннем формате;

() число заключается в скобки;

" " выводятся символы, содержащиеся между кавычками;

\ выводится символ, следующий за обратным слэшем;

_ оставляется место на ширину символа следующего за подчеркиванием;

* ячейка заполняется символом следующим за звездочкой;

@ применяетсяв текстовой секции для вывода текста.

/ применяетсядля вывода рациональных дробей;

р. для вывода чисел в денежном формате;

% для вывода чисел в процентном формате;

Е+ Е- е+ е- для вывода чисел в экспоненциальном формате.

Пользовательский формат

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

Можно изменить назначение по умолчанию числовых секций формата. Для этого в секцию следует ввести условие в квадратных скобках, состоящее из оператора сравнения и значения, например [<=50]. Допускается вводить условие в первую и вторую секцию формата, третья будет работать для всех остальных чисел.

Для отображения чисел какой-либо секции другим цветом нужно ввести в эту секцию название цвета в квадратных скобках: [Зеленый], [Красный]. Чтобы числа какой-либо секции или текст не отображались, вместо кода в секцию вводится пробел.

Общий вид кода формата:

[Цвет][Условие]Код;[Цвет][Условие]Код;[Цвет]Код;@

Примеры

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

Выполнение:

1. Выделить исходные данные и открыть диалог Формат — Ячейки …

2. На закладке Число в списке Числовые форматы выбрать строку (все форматы) и в поле Тип написать формат, состоящий из четырех секций, разделенных точкой с запятой:

- положительные числа à # V ##0,0

- отрицательные числа à [Красный](0)

- ноль à 0* V

1. После нажатия ОК исходные данные отобразятся так, как показано на рис. 2.1.

Рис. 2.1. Создание пользовательского формата

Пример 2.2. Создать пользовательский формат, который отобразит исходные данные так, как показано в правом столбце (рис. 2.2).

Выполнение:

1. После внимательного рассмотрения рисунка видно, что в положительных числах разделяются тысячи, они имеют один обязательный десятичный знак, второй десятичный знак выводится или на него оставляется место, т.е. числа выровнены по запятой. Таким образом, для положительных чисел нужно назначить формат — # V ##0,0?

2. Отрицательные числа выровнены к левой границе ячейки, в них также разделяются тысячи и имеется выравнивание по запятой, они имеют один обязательный десятичный знак, для чисел имеющих больше одного десятичного знака выводится второй знак. Таким образом, для отрицательных чисел нужно назначить формат — -?? V??0,0#

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

4. Вместо текстовых значений выводится слово «ошибка», выровненное к правой границе ячейки. Таким образом, формат, подходящий ко всему столбцу исходных данных имеет вид: # V ##0,0?; -?? V??0,0#; V;* V "ошибка"

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

Выполнение:

1. Как видно из рисунка, числа от 0 до 1000 не изменились, значит они имеют формат Основной

2. Отрицательные числа не отображаются, поэтому в секции, которая их описывает, вводится пробел.

3. Числа > 1000 масштабируются в 1000 раз, выводятся с двумя обязательными знаками после запятой и словом «тысяч», поэтому для них подходит формат 0,00 V " V тысяч"

4. Вместо текста вся ширина ячейки заполняется знаком ~, т.е. формат имеет вид *~

5. Для создания формата с условиями необходимо соблюдать порядок расстановки условий. Нельзя задавать формат интервалу [0; 1000]. Условие в первой секции ставится так, чтобы не захватить два промежутка. Таким образом, возможны четыре варианта объединения форматов п.п. 1—4 для создания формата с условиями, подходящего ко всему столбцу с исходными данными:

- [<0] V;[<=1000]Основной;0,00 V " V тысяч";*~

- [<0] V;[>1000]0,00 V " V тысяч";Основной;*~

- [>1000]0,00 V " V тысяч";[<0] V;Основной;*~

- [>1000]0,00 V " V тысяч";[>=0]Основной;;*~

Практические задания

На оглавление

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

Цель работы: научиться разрабатывать многосекционные числовые форматы.

Вариант 1

1.1. Загрузить шаблон Excel_Lab_2_1.xltx. Скопировать исходные данные в соседние столбцы и назначить соответствующие форматы:

Примечание. В формате 1.1.6 отрицательные значения должны быть красного цвета.

1.2. Создать форматы с условиями:

Вариант 2

2.1. Загрузить шаблон Excel_Lab_2_2.xltx. Скопировать исходные данные в соседние столбцы и назначить соответствующие форматы:

Примечание. В формате 2.1.6 отрицательные числа со словом «Долг» должны быть красного цвета.

2.2. Создать форматы с условиями:

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

1. Как в MS Excel хранятся даты и время? Какие символы-заменители применяются для форматирования дат и времени?

2. Как с помощью числового формата можно:
– масштабировать числа в тысячу раз;
– выравнивать числа по левому краю;
– выравнивать числа по десятичной запятой;
– окрашивать числа в какой-либо цвет;
– выводить дату и время во внутреннем формате;
– скрывать содержимое ячейки;
– заполнять всю ширину ячейки каким-либо символом;
– дописывать к числу текст?

3. Как в MS Excel будут интерпретированы значения ¾ и 5:?

4. Каким образом можно в одном формате по-разному отображать положительные и отрицательные числа?

5. Каковы правила написания формата с условиями?

 

Теоретические сведения

На оглавление

Адресация в Excel

В формулах Excel применяются относительные, абсолютные и смешанные ссылки.

При копировании формулы, содержащей относительные ссылки, они изменяются относительно расположения ячейки, содержащей формулу. Например, ячейка С1 содержит формулу =A1+В1. При копировании формулы в С2 ссылки изменяются (=А2+В2). Если необходимо, чтобы ссылки не изменялись при копировании формулы, нужно использовать абсолютные ссылки. Например, нужно к числам в В1:В5 прибавить значение из А1, для этого в формуле =А1+В1 нужно использовать абсолютную ссылку на А1. Для обозначения абсолютных ссылок используется знак $. Следовательно, формула в С1 должна иметь вид =$A$1+В1, а при копировании в С2 изменится только относительный адрес (=$A$1+B2). Ссылка называется смешанной, если одна часть адреса относительная, другая — абсолютная. Например, в ссылке $A1 при копировании формулы будет меняться только строка, в ссылке C$5 при копировании формулы будет меняться только столбец.

Для циклического изменения типа ссылки используется клавиша F4.

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

Связывание листов

Чтобы использовать в формуле данные, расположенные на другом рабочем листе, удобно открыть новое окно (команда Окно—Новое) и расположить их рядом (Окно—Расположить …). В одном окне открыть лист с формулой, в другом — с данными и ссылаться на ячейки с помощью мыши. При этом автоматически прописанная ссылка будет содержать имя листа, например: =Лист2! А3. Для разделения имени листа и адреса ячейки используется восклицательный знак.

Таким же образом можно ссылаться на данные другой книги, в этом случае ссылка будет содержать имя файла, которое заключается в квадратные скобки, например: =[kurs.xls]Итоги! $D$4.

Применение имен

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

Чтобы присвоить имя, можно использовать один из способов:

– выделить ячейку или диапазон, активизировать Поле имени, ввести имя и нажать Enter;

– выделить ячейку или диапазон, нажать кнопку Присвоить имя на закладке Формулы ленты инструментов, в строке ввода Имя ввести имя и нажать OK;

– выделить диапазон ячеек вместе с подписью, например, диапазон E2:E6 в задании II.2, нажать кнопку Создать из выделенного фрагмента на закладке Формулы ленты инструментов, установить флажок в строке выше и нажать OK, при этом диапазон Е3:Е6 получит имя Премия. Аналогично можно использовать заголовки строк.

Чтобы применить имя в формуле, можно ввести его с клавиатуры или нажать кнопку Использовать в формуле. Чтобы удалить ненужное или неверно заданное имя, следует нажать кнопку Диспетчер имен, выбрать его в списке имен и нажать кнопку Удалить.

 

Вставка функций

 

 

Рис. 3.1. Диалоговое окно Аргументы функции

1 — строка формул

2 — редактируемая формула

3 — описание выделенной функции (ОКРУГЛ)

4 — поля для заполнения аргументов выделенной функции

5 — кнопка для сворачивания диалогового окна при заполнении аргумента

6 — описание выделенного аргумента

7 — значения аргументов (в А1 à 32)

8 — результат вычисления выделенной функции

9 — результат вычисления всей формулы

10 — раскрывающийся список для выбора вложенной функции

11 — кнопка Вставка функции

 

Для вставки функции применяются кнопки на закладке Формулы ленты инструментов или кнопка в строке формул. Затем в окне Мастер функций следует из соответствующей категории выбрать необходимую функцию, нажать ОК и заполнить диалоговое окно Аргументы функции (рис. 3.1). Функции могут использоваться как аргументы в других функциях. Допускается использовать до семи уровней вложения функций. Чтобы редактировать формулу, содержащую функции, следует нажать кнопку (рис. 3.1). На экране отобразится диалоговое окно с аргументами первой функции формулы. Изменение какой-либо вложенной функции происходит путем щелчка мышью по имени этой функции в строке формул.

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

Функции округления

ОКРУГЛ — округляет число до указанного количества разрядов по общим правилам.

ОКРУГЛВВЕРХ — округляет число до указанного количества разрядов в большую (по модулю) сторону.

ОКРУГЛВНИЗ и ОТБР — округляют число до указанного количества разрядов в меньшую (по модулю) сторону.

Табличные формулы

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

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

2. Наберите формулу.

3. Нажмите клавиши Ctrl+Shift+Enter. Табличная формула автоматически заключается в фигурные скобки { }.

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

– весь массив заключается в фигурные скобки { }

– значения строк разделяются точками с запятой;

– значения столбцов разделяются двоеточием:

Чтобы изменить табличную формулу, которая возвращает массив, необходимо выделить весь массив и отредактировать формулу в строке формул. Завершать редактирование табличной формулы также нужно комбинацией клавиш Ctrl+Shift+Enter.

Функция МОБР (массив) возвращает обратную матрицу.

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

Примеры

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

Таблица 3.1

Математическое выражение	Формула в MS Excel
	=ABS(A1)
	=LN(A1)
	=EXP(A1)
	=КОРЕНЬ(А1)
	=COS(ПИ()*А1)
	=SIN(A1)^2
	=СТЕПЕНЬ((EXP(-3*A1)+A1)/ABS(SIN(A1)–7*A1)+TAN(A1);1/3)

 

Пример 3.1. В таблице (рис. 3.2) приведен стаж и заработная плата сотрудников.

Определить:

– какой удельный вес составляет заработная плата каждого сотрудника в общем объеме заработной платы;

– сумму заработной платы сотрудников, имеющих стаж более 5 лет.

Выполнение:

1. Вычислить сумму заработной платы всех сотрудников (ячейка С11)
   =СУММ(С2:С10)
2. Вычислить удельный вес заработной платы первого сотрудника (ячейка D2)
   =C2/$C$11. В данной формуле используется абсолютная ссылка на ячейку С11, т.к. она не должна изменяться при копировании формулы.

Рис. 3.2

Рис. 3.3

1. Протянуть маркером формулу, созданную в D2, для остальных сотрудников. Ячейкам D2:D10 назначить процентный формат с двумя десятичными знаками.
2. Для нахождения суммы заработной платы сотрудников, имеющих стаж более 5 лет (ячейка С12), следует использовать функцию СУММЕСЛИ. В ней нужно указать диапазон В2:В10, по которому задается условие на стаж — ">5", и диапазон С2:С10, по которому производится суммирование. Таким образом, формула будет иметь вид à =СУММЕСЛИ(B2:B10;">5";C2:C10)

Пример 3.2. В ячейках А3:А13 (рис. 3.3) даны числа, а в ячейках B2:D2 — значения разрядов, до которых нужно округлить данные числа. В ячейке B3 написать формулу для округления и скопировать ее для заполнения всей таблицы.

Выполнение:

1. Числа в Excel можно округлять с избытком, с недостатком или по общим правилам. Так как в условии задачи не указан способ округления, будем использовать функцию ОКРУГЛ.
2. Первым аргументом данной функции указывается округляемое число, расположенное в ячейке А3. Для правильного копирования формулы в столбцы С и D в ссылке на А3 должен быть абсолютный столбец ($A3).
3. Вторым аргументом указывается число разрядов округления — ячейка В2. Для правильного копирования формулы в строки 4—13 в ссылке на В2 должна быть абсолютная строка (В$2).
4. Таким образом, формула в В3 будет иметь вид à =ОКРУГЛ($А3;В$2), которую можно скопировать для заполнения всей таблицы.

Пример 3.3. Даны матрицы А и В (рис. 3.4). Найти матрицу С = А – В.

Рис. 3.4

Выполнение:

1. Матрица С имеет такую же размерность как матрицы А и В, поэтому нужно выделить диапазон из 3 строк и 2 столбцов (G2:H4).
2. В строке формул написать формулу à =A2:B4 – D2:E4
3. Завершить ввод формулы комбинацией клавиш Ctrl+Shift+Enter. Формула будет автоматически заключена в фигурные скобки и будет одинаковой для всех ячеек диапазона G2:H4.

 

 

Практические задания

На оглавление

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

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

Задания:

Справочные формулы

Доход от оборота = Цена единицы продукции * Объем сбыта
Себестоимость = Себестоимость единицы продукции * Объем сбыта
Затраты на рекламу = Доход от оборота * % Рекламы
Накладные расходы = Доход от оборота * % Накладных расходов
Прибыль = Доход от оборота – (Себестоимость + Затраты на рекламу + Накладные расходы)

Внимание!!!

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

5. На листе «Продукция 2» создать и рассчитать аналогичную таблицу для продукции 2.

6. На листе «Годовой отчет» создать таблицу и в ячейку В3 ввести формулу для вычисления суммарного объема сбыта продукции 1 и продукции 2 за весь год. Скопировать формулу для нахождения остальных показателей:

7. Изменить какие-либо значения на листе «Исходные данные» и проследить изменения на других листах.

II. Применение имен.

1. На листе «Зарплата» создать таблицы:

2. Присвоить имена областям B3:B6, C3:C6, D3:D6. Создать имена областям E3:E6, F3:F6, G3:G6, H3:H6. Отдельным ячейкам А9, B9, C9, D9, E9 задать имена, используя поле имени.

3. Рассчитать таблицу, используя только созданные имена.

Справочные формулы
Оклад =Тариф * Базовая величина	Начислено = Оклад + Надбавка + Премия
Премия =% премии * Оклад	Подоходный налог = % налога * Начислено
Надбавка = % надбавки * Оклад	Профсоюз = % взноса * Начислено
К выдаче = Начислено – (Подоходный налог +Профсоюз)

Теоретические сведения

На оглавление

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

СЕГОДНЯ — возвращает текущую дату. Данная функция не имеет аргументов.

ГОД — возвращает год, соответствующий аргументу дата_в_числовом_формате. Год определяется как целое в интервале 1900-9999.

МЕСЯЦ — возвращает месяц, соответствующий аргументу дата_в_чис­ловом_формате. Месяц определяется как целое в интервале от 1 (Январь) до 12 (Декабрь).

ДЕНЬ — возвращает номер дня в месяце для аргумента дата_в_число­вом_формате. День возвращается как целое число диапазоне от 1 до 31.

ДАТА — возвращает дату для заданных года, месяца и дня.

Синтаксис: ДАТА(год; месяц; день)