Табличный процессор MS Еxcel.

Часть 2

Табличный процессор MS Еxcel.

Москва

Г

Содержание

Введение. 2

Цель 2

Общий вид рабочего окна MS Excel 2

Основные понятия электронных таблиц MS Excel 3

Активная ячейка и ее режимы работы. 4

Диапазон (блок) ячеек. Выделение диапазонов.

Изменение высоты и ширины ячеек, строк и столбцов 5

Типы данных и форматы их представления 6

Автозаполнение ячеек 7

Вычисления с помощью формул и функций 9

Построение диаграмм 14

Практическая работа 18

Приложение:. Сообщение об ошибках. 34

Литература 34

Введение.

Табличный процессор MS Excel относится к классу прикладных программ, предназначенных для создания и обработки табличных данных в электронном виде. С 1994 года это самый популярный табличный процессор в мире. Программа MS Excel, являясь лидером на рынке программ обработки электронных таблиц, определяет тенденции развития в этой области.

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

Одним из важнейших функциональных расширений MS Excel является встроенная среда программирования Visual Basic for Applications (VBA), предназначенная для решения прикладных задач в MS Office. Благодаря VBA фирме Microsoft удалось не только расширить возможности языка макрокоманд Excel, но и ввести новый уровень прикладного программирования, поскольку VBA позволяет создавать полноценные прикладные пакеты, которые по своим функциям выходят далеко за рамки обработки электронных таблиц.

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

Возможности программы очень разнообразны:

· Ввод и редактирование данных.

· Форматирование ячеек, строк и столбцов таблицы

· Ввод формул (автоматизация расчетов)

· Применение широкого спектра разнообразных функций

· Построение, редактирование и печать диаграмм.

· Предварительный просмотр и печать таблицы

· Создание и ведение баз данных

 

Цель:

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

 

Общий вид рабочего окна MS Excel

Интерфейс табличного процессора EXCEL мало, чем отличается от интерфейса текстового редактора WORD. Такие элементы интерфейса как строка заголовка, верхнее меню, панели инструментов, полосы прокрутки, строка состояния практически одинаковы, за исключением замены или добавки некоторых кнопок (мастер функции, мастер диаграмм и т.п.) на панели инструментов Стандартная. Особыми элементами интерфейса являются строка имен ячеек, строка формул, строка имен столбцов и колонка номеров строк. В нижней части экрана находятся зона ярлыков рабочих листов и кнопки их (листов) прокрутки. На рис. 1 представлен общий вид окна MS Excel.

 

Рис. 1 Общий вид окна MS Excel

 

Основные понятия электронных таблиц MS Excel

Основным документом MS Excel является рабочая книга (Workbook), которая представляет собой файл с расширением *.xls. Книга состоит из рабочих листов (Worksheet), которые обозначаются как Лист 1 (Sheet 1) и т.д. (рис. 1). Рабочий лист можно ассоциировать с понятием “документ” или “электронная таблица”. Листы книги можно перемещать, копировать, переименовывать. Перемещение можно выполнить перетаскиванием ярлычка листа с помощью мыши. Для переименования можно выполнить два щелчка левой кнопкой мыши на ярлычке переименовываемого листа, а затем ввести новое имя, либо при помощи контекстного меню (щелчок правой кнопкой мыши на ярлычке переименовываемого листа). Также контекстное меню позволяет добавить новый лист в книгу, удалить и копировать выбранный лист. Рабочий лист или электронная таблица состоит из 65536 строк (rows) и 256 столбцов или колонок (columns), которые отображаются на экране компьютера. Строки нумеруются целыми числами от 1 до 65536, а столбцы обозначаются буквами латинского алфавита A, B, …, Z, AA, AB, …IV. На пересечении строки и столбца располагается основной структурный элемент таблицы – ячейка (cell). К содержимому ячейки можно обратиться по ее адресу (ссылке), например, A5. Адрес выделенной ячейки отображается в Строке имен ячеек.

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

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

 

Автозаполнение ячеек

 

Составление таблиц – процесс трудоемкий. Разработчики Excel упростили эту задачу с помощью механизма автозаполнения. Он может быть использован там, где некое количество смежных ячеек должно быть заполнено однородной информацией. В одну из ячеек вводится первый элемент ряда данных, в следующую – второй. Затем следует выделить обе ячейки (протаскиванием при нажатой левой кнопке мыши), установить указатель мыши на маркер автозаполнения (черный квадратик в правом нижнем углу ячейки или группы выделенных ячеек, при этом указатель мыши принимает вид черного крестика), нажать левую кнопку мыши и тащить ее далее по строке или столбцу, которые надо автоматически заполнить. Заполнение будет происходить из имеющихся списков (см. меню Сервис – Параметры… – Списки), либо по принципу: следующее значение = предыдущее + шаг. Нужный список можно самостоятельно сформировать и добавить к перечню стандартных.

Примеры, показывающие возможности авто заполнения (Рис. 4):

 

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

 

1. В первые две ячейки 2-ой строки введем даты 27.02.00 и 28.02.00. Выполнив описанную выше последовательность действий, получим ряд:

 

27.02.2000

28.02.2000

29.02.2000

01.03.2000

02.03.2000

03.03.2000

04.03.2000

Учитывается, что год високосный!

2. В первую ячейку 3-ей строки введем название месяца Апрель и применим механизм автозаполнения к одной ячейке. Получим ряд:

Апрель

Май

Июнь

Июль

Август

Сентябрь

Октябрь

 

3. Пусть Xmin=2, Xmin+h=2,2. Введем эти значения в соседние ячейки 4-ой строки и применим автозаполнение. Получим ряд:

2,2

2,4

2,6

2,8

3,2

 

Замечание. Если заполняемый ряд данных выходит за пределы рабочего экрана, удобнее использовать команду Правка – Заполнить Прогрессия…

 

4. Для создания цифровых списков (прогрессии) применяется команда Прогрессия подменю Заполнить меню Правка. Excel может работать с четырьмя типами прогрессии: арифметической (1,2,3,4,5,6,…), геометрической (5,10,20,40,…), дат (1995,1996,1997,1998,…) и автозаполнения. Создать прогрессию в последовательности ячеек можно так:

ü Ввести в ячейку А5 значение 1. Это значение станет начальным значением прогрессии.

ü Начиная с ячейки, содержащей введённое значение, выделить ячейки, в которых будет продолжена прогрессия - А5:K5.

ü

Выбрать команду Прогрессия подменю Заполнить меню Правка. Появится диалоговое окно Прогрессия (Рис. 5):

Рис. 5 Окно Прогрессия.

 

ü В группе Расположение этого окна установить соответствующий режим (по строкам).

ü В группе Тип выбрать тип прогрессии (арифметическая).

ü Ввести значение шага (оставить - 1).

ü Щёлкнуть на кнопку OK.

ü Прогрессия закончится, достигнув конца выделенного диапазона.

Можно и не выделять диапазон ячеек, но тогда в окне Прогрессия необходимо установить Предельное значение (например: 11), щёлкнуть на кнопке OK и ячейки автоматически заполнятся до ячейки K5.

 

Пример:

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

 

Для этого выделите ячейку А6, нажать кнопку Σ, затем Enter. Сумма чисел первого столбца =СУММ(А4:А5) появится в ячейке А6. Аналогичную операцию можно повторить и для остальных столбцов. (Рис. 4)

Однако удобнее применить следующий прием – прием копирования с помощью маркера заполнения: выделить ячейку А6 с записанной функцией =СУММ(А4:А5), установить указатель мыши на Маркер заполнения (маленький черный квадрат) в нижнем правом углу ячейки (указатель примет форму черного крестика) и, удерживая левую кнопку мыши, растянуть рамку вправо на соседние ячейки В6,С6, D6 и т.д. После того, как левая кнопка мыши будет отпущена, в ячейках В6, С6, D6,, К6 появятся результаты суммирования. Обратить внимание, что в ячейке В6 будет находиться формула = СУММ(В4:В5), а в ячейке С6 – формула = СУММ (С4:С5), т.е. имена ячеек (в данном случае имена столбцов) автоматически изменились. При копировании формулы вниз в именах ячеек будут меняться имена строк. Такой вид адресации ячеек называется относительной адресацией, или относительной ссылкой на ячейку.

Для того чтобы при копировании имена ячеек, входящих в формулы не менялись, необходимо использовать абсолютную адресацию, или абсолютную ссылку на ячейку. Для абсолютной адресации (ссылки) используют знак $, который ставится перед тем именем строки или именем столбца ячейки, которые необходимо зафиксировать, т.е. сделать неизменяемыми при копировании формулы. Знак $ можно ввести вручную перед именем строки и именем столбца, перейдя на латинский шрифт и нажав клавиши SHIFT+4, но целесообразней (проще и быстрее) установить курсор мыши в строке формул на название ячейки (А4) и нажать функциональную клавишу F4 один раз – произойдет фиксация имени строки и имени столбца, т.е автоматически вставиться знак $ и перед А и перед 4 ($А$4). Повторное нажатие на эту клавишу фиксирует только имя строки (A$4), третье нажатие на F4 фиксирует имя столбца ($A4), а четвертое – отмена абсолютной ссылки на ячейку (А4).

Формула суммирования ячеек в столбце А с абсолютной адресацией (ссылкой) будет выглядеть так: =СУММ($А$4:$А$5) и при копировании ее в ячейки В6, С6, D6,, К6 имена ячеек в формуле не изменятся, а результаты вычисления будут равны результату вычисления в ячейке А6 (Рис. 6).

 

Рис. 6. Автосуммирование с использованием абсолютной ссылки на ячейку.

 

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

 

Рис. 7 Пиктограмма Мастера функций.

Щелчок на пиктограмму левой кнопкой мыши вызывает диалоговое окно Мастера функций (на Рис. 8 - слева).

 

Рис.8. Диалоговое окно Мастера функций (шаг 1) для различных версий Excel.

 

В старших версий Excel для вызова Мастера функций необходимо щелкнуть на треугольник, находящийся справа от пиктограммы Автосуммирования и выбрать из списка Другие функции (Рис. 9), тем самым, вызвав, Мастер функций (Рис. 8).

 

Рис. 9 Пиктограмма Мастера функций для старших версий Excel.

 

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

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

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

Рис.10. Аргументы функции (шаг 2).

Затем, нажав левую клавишу мыши (курсор в виде белого крестика), растянуть выделение на нужное количество ячеек. Развернуть окно и нажать ОК.

 

Пример:

Подготовить таблицы как показано на рисунке 11 для построения параболы по формуле y=а*(x-b)² + c при условии, что начальное значение аргумента (х) равно -10 и (х) изменяется от -10 до 20 с шагом аргумента = 1, а=0,5, b=5, с=10.

 

Рис. 11 Таблицы для построения параболы.

 

Открыть Лист2, и щелкнув правой кнопкой мыши по названию листа, вызвать контекстное меню и выбрать опцию Переименовать. Вместо Лист2 напечатать Парабола.

В ячейку А1 вводим – «Аргумент», в ячейку В1 – «Функция – парабола», справа в ячейку D1 заголовок таблицы начальных значений параметров так, как показано на рис. 10. Затем выделить блок ячеек D1:L1, открыть пункт меню Формат→Ячейки, выбрать закладку Выравнивание. В этом окне дается возможность выравнивать текст в ячейке как по горизонтали, так и по вертикали самой ячейки, кроме того есть возможность расположить текст в ячейке не только горизонтально, но и вертикально, а также под заданным углом (справа - ячейка Ориентация, в которой можно мышью подвигать Надпись вверх/вниз, можно задать градусы угла под которым будет располагаться текст в ячейке). Рис. 12.

 

Рис.12 Диалоговое окно Формат Ячеек. Выравнивание.

 

Ниже имеется возможность переноса по словам, т.е. переход на другую строку в ячейке (комбинация клавишей левый ALT+ENTER также является переходом на др. строку) и объединение ячеек, куда и надо установить галочку для объединения ячеек в блок D1:L1.

Закладки Шрифт, Граница и Вид дают возможность форматирования шрифта, обрамления и заливки таблицы и их функции. Выбрать закладку Шрифт и отформатировать заголовок (изменить размер, цвет и начертание шрифта). Далее подготовить таблицы для введения в них формул как показано на рисунке 11.

Ввести в ячейку А2 начальное значение аргумента х -10, в ячейку А3 – формулу, изменяющую значение аргумента на его шаг при помощи ссылок на ячейки, в которых введены их значения: =A2+$E$7. При этом ввод имен ячеек производить не руками с клавиатуры; а после знака (=) щелкнуть мышью по ячейке А2, напечатать знак (+) и щелкнуть мышью по ячейке Е7. Для ячейки А2 используется относительная ссылка, т.к. при копировании формулы вниз вдоль строк необходимо, чтобы у ячейки А2 изменялось имя строки (А2 на А3, А3 на А4 и т.д. до А32) для того чтобы предыдущее значение аргумента в каждой новой ячейке увеличивалось бы на шаг (на 1) до конечного значения равного 20. Для ячейки Е7 используется абсолютная ссылка, т.к. числовое значение шага аргумента находится в определенном месте – в ячейке Е7, следовательно, при помощи функциональной клавиши F4 надо зафиксировать имя столбца и имя строки +$E$7. Нажать клавишу ENTER и при помощи маркера заполнения (черный крестик в нижнем левом углу ячейки) скопировать формулу вниз до 32 строки (х=20).

В ячейку В2 ввести формулу параболы =$E$4*(A2-$E$5)^2+$E$6 (формула записана при помощи ссылок на ячейки, в которые введены параметры параболы; знак ^ - означает возведение в степень). Т. к. ячейки Е4, Е5, Е6 не должны изменять имена строк и столбцов (в них введены значения параметров параболы, которые не должны изменяться при копировании формулы), зафиксировать их имена при помощи функциональной клавиши F4 (установить курсор мыши на имя ячейки и щелкнуть один раз на клавишу F4). В формуле, введенной в ячейку В2, используется ссылка на ячейку А2, в В3 на А3 и т.д., поэтому надо оставить относительную ссылку на ячейку А2, чтобы при копировании формулы вниз вдоль строк эта ячейка могла менять имя строки. После введения формулы нажатием клавиши ENTER произвести выполнение формулы и затем и при помощи маркера заполнения (черный крестик в нижнем левом углу ячейки) скопировать формулу вниз до 32 строки.

Построение диаграмм.

Для построения диаграмм необходимо вызвать Мастер построения диаграмм, нажав на панели инструментов Стандартная кнопку Мастера диаграмм. Рис. 13

Рис. 13 Мастер диаграмм. (шаг 1): тип диаграммы.

 

На первом шаге (рис.13) выбирается тип диаграммы и ее вид. Для перехода ко второму шагу следует нажать кнопку Далее. На втором шаге Мастера диаграмм можно изменить или определить исходные данные – источник данных диаграммы (рис.14). Окно второго шага содержит вкладки Диапазон данных и Ряд, т. е. на втором шаге выбираются данные (блок или блоки ячеек), на основании которых строится диаграмма. Закладка Диапазон данных активна по умолчанию, в ней источник данных рассматривается как один диапазон (ряд), который можно изменить или определить (если это не сделано перед вызовом Мастера диаграмм) в ячейке Диапазон.

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

 

Рис. 14 Мастер диаграмм. (шаг 2):источник данных диаграммы.

 

Закладка Ряд выбирается, если источник данных рассматривается как совокупность диапазонов (рядов данных), которые можно добавлять, удалять, переопределять. В ячейке Ряд (большая ячейка, расположенная слева) дается возможность, как удаления, так и добавления необходимого количества рядов (диапазонов), в ячейке Значение для каждого ряда выбирается свой блок ячеек, в ячейке Имя можно указать ячейку, которая является заголовком ряда (диапазона) данных, а можно ввести название с клавиатуры. В нижней части окна есть ячейка Подписи по оси Х, где указывается блок ячеек, содержимое которых отражается на диаграмме под осью Х как подпись каждого столбца или точки графика. Для перехода к следующему шагу следует нажать кнопку Далее.

На третьем шаге Мастера диаграмм определяются параметры диаграммы, размещенные на шести закладках окна третьего шага. (Рис.15)

 

Рис. 15 Мастер диаграмм. (шаг 3): параметры диаграммы.

 

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

Вкладка Легенда содержит элементы управления для настройки легенды. Легенда – это объект диаграммы, содержащий обозначения и названия рядов данных. Если легенда должна присутствовать на диаграмме, то это необходимо отметить флажком Добавить легенду, и в группе Размещение выбрать место для первоначального размещения легенды.

Вкладки Оси и Линии сетки позволяют определить наличие или отсутствие осей и линий сетки.

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

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

На четвертом шаге Мастера диаграмм определяется место размещения диаграммы: на имеющемся листе, т.е. на одном из рабочих листов электронной таблицы, или на отдельном специальном листе диаграмм. (Рис. 16)

 

Рис. 16 Мастер диаграмм. (шаг 4): размещение диаграммы.

 

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

 

Пример:

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

Перед вызовом Мастера диаграмм рекомендуется выделить ряды данных, по которым будет строиться диаграмма, если, конечно, диаграмма не сложная и выделяются ряды данных в виде отдельных столбцов или строк. Для построения параболы необходимо выделить блок ячеек В1:В32, затем нажать кнопку для вызова Мастера диаграмм на Стандартной панели инструментов и на первом шаге выбрать тип диаграммы – График. Нажав кнопку Далее, перейти на второй шаг. На втором шаге при выбранной закладке Диапазон данных в ячейке Диапазон уже будет отражен блок выбранных ранее ячеек и в верхней части окна построен график параболы. Переключившись на закладку Ряды можно увидеть пример того же графика параболы, и что в ячейке Значение уже введен блок выбранных ранее ячеек за исключением ячейки В1, в которой введено название столбца и которая автоматически введена в ячейку Имя. И соответственно в ячейке Ряд указано название ряда, введенное в ячейку В1 - Функция – парабола. Остается только выбрать блок ячеек для введения его в ячейку Подписи оси Х. Нажав кнопку , расположенную справа от ячейки, свернуть окно и белым толстым крестиком, нажав правую кнопку мыши протащить выделение с ячейки В2 до В32. Развернуть окно и перейти на третий шаг, нажав кнопку Далее. Выбрана закладка Заголовки: название диаграммы введено автоматически, можно ввести названия осей. Далее при выборе каждой закладки внести различные изменения и обратить внимание на пример параболы, находящийся справа. Установить те характеристики графика, которые лучше отражают данные и вид параболы, и перейти на четвертый шаг, где произвести выбор размещения диаграммы.

Отредактировать диаграмму, видоизменить все элементы диаграммы можно при помощи контекстного меню, вызываемого правой кнопкой мыши. Если медленно перемещать указатель мыши по области диаграммы, то можно увидеть всплывающие подписи тех элементов диаграммы, которые доступны для изменения. Это сама область диаграммы, область построения, оси X и Y, линии сетки осей X и Y, сам график функции, легенда (условные обозначения). Видоизменение, как правило, состоит в определении другого цвета для какого-то элемента, нового типа линии или маркера. Внести изменения можно, выбрав в контекстном меню первый пункт – Формат соответствующего объекта (области построения, области диаграммы, оси, легенды, рядов данных (рис. 17)) и определив нужные параметры.

 

 

Рис. 17 Окна Форматов соответствующих объектов.

 

Пример:

Практическая работа

Пример 1:

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

Открыть Лист3, и щелкнув правой кнопкой мыши по названию листа, вызвать контекстное меню и выбрать опцию Переименовать. Вместо Лист3 напечатать название листа Таблица умножения.

Для создания таблицы умножения, как показано на рис. 18, необходимо в ячейки В2:К2 ввести горизонтальный множитель, а в ячейки А3:А12 вертикальный множитель.

 

Рис. 18 Таблица умножения.

 

Для ввода горизонтального множителя в ячейку В2 ввести 1, в ячейку С2 – 2, выделить две ячейки В2 и С2 и копировать содержимое дух ячеек при помощи маркера заполнения (наводится курсор мыши на точку, находящуюся в нижнем правом углу второй ячейки и при нажатой левой кнопки мыши растягивается выделение до нужной ячейки) до ячейки К2.

Для ввода вертикального множителя выбрать другой способ введения чисел. В ячейку А3 ввести 1, выделить эту ячейку и вызвать окно Прогрессия (Правка → Заполнить → Прогрессия). В окне указать: расположение – по строкам, тип – арифметическая, шаг – 1 и предельное значение – 10. Нажать кнопку ОК.

Выделить блок ячеек В2:К2, и при нажатой клавише Ctrl выделить блок А3:А12 (т.е. одновременно выделены блок ячеек в строке и блок ячеек в столбце). Нажать пункт меню Формат, опция Ячейки, тем самым, вызвав окно Формат Ячеек. Для выделенных блоков изменить размер и цвет шрифта, подчеркнуть значения и расположить их по центру ячейки. Нажать ОК. Далее выделить всю таблицу, т.е. блок ячеек А2:К12 и вызвать окно Формат Ячеек – закладка Граница, где тип и цвет линий, которыми будет обрамлена таблица (желательно: внешнее и внутреннее обрамления задать различным цветом и типом линии)

Ввести заголовок в ячейку А1, выделить блок ячеек А1:К1, пункт меню Формат → Ячейки, закладка Выравнивание, установить галочку в Объединение ячеек и в Выравнивании по горизонтали выбрать – по центру выделения. Далее закладка Шрифт: изменить размер, цвет начертание шрифта и дать подчеркивание двойной линией по значению.

Самым важным является ввод правильной формулы в ячейку В3 и копирование ее во все ячейки таблицы.

Если в ячейку В3 ввести формулу с относительными ссылками на ячейки =В2*А3 (1*1), то при копировании ее вправо по столбцам и вниз по строкам имена столбцов и строк у ячеек будут меняться и в результате в ячейках таблицы будут неверные значения. (Рис 19)

 

Рис. 19 Таблица умножения (введенная формула с относительной ссылкой на ячейки).

 

Например: в ячейке Д5 должно быть значение равное 9 (3*3), но в строке формул отражена формула не Д2*А5, куда были введены множители 3 и 3, а Д4*С5. И это естественно, т.к. при копировании направо (по столбцам) формулы с относительной ссылкой на ячейки, в имени ячейки меняется имя столбца, при копировании вниз по строкам меняется имя строки. Для того чтобы не изменялись имена строк и столбцов, необходимо зафиксировать данное имя, введя перед ним символ $, т.е. использовать абсолютную или частично абсолютную ссылку на ячейку.

Если же в ячейку В3 ввести формулу с абсолютной ссылкой на ячейку = $В$2*$А$3 и размножить по ячейкам таблицы, то в каждой ячейке будет стоять 1 (1*1).

Следовательно, в первой формуле, введенной в ячейку В3 у одного множителя должна быть абсолютная ссылка на строку, у другого на столбец. Необходимо обратить внимание на то, что горизонтальный множитель находится в блоке ячеек В2:К2, т.е. имена столбцов у этого множителя меняются, а строка остается неизменной. Следовательно, у горизонтального множителя надо зафиксировать строку. У вертикального множителя, наоборот, не изменяется имя столбца, но меняются имена строк, значит, фиксировать надо имя столбца.

Следовательно, в ячейку В3 вводится формула - = В$2*$А3 (щелкнуть в ячейку В3, набрать с клавиатуры =, щелкнуть мышью в ячейку В2, зафиксировать имя строки нажатием функциональной клавиши F4 два раза, знак умножения (*) ввести с клавиатуры, мышкой щелкнуть на А3, зафиксировать имя столбца нажатием функциональной клавиши F4 три раза и для выполнения формулы нажать Enter), затем эта формула копируется по ячейкам таблицы (скопировать содержимое ячейки В3 в буфер обмена, выделить блок ячеек В3:К12 и при помощи контекстного меню вставить формулу в ячейки таблицы). Результат правильно введенной формулы на рис. 18.

По диагонали таблицы расположены значения квадратов множителей, так в ячейке D5 находится число 9, которое является квадратом числа 3 (D$2*$А5=3*3) На рис. 17 ячейки содержащие значения квадратов закрашены желтым цветом. Построение графика по значениям квадратов множителей отличается от построения в предыдущем примере графика параболы. Для построения графика параболы выбирался блок ячеек, все ячейки которого находились в одном столбце, что соответствовало выбору одного ряда с блоком ячеек. В данном случае выбираются ячейки из разных столбцов, что будет соответствовать выбору нескольких рядов, содержащих по одной ячейке, что даст неправильное отражение значений квадратов на графике. Так, если сначала выбрать ячейки, по которым строится график, а потом обратиться к Мастеру диаграмм, или после выбора Мастера диаграмм в ячейке Диапазон (закладка Диапазон данных) выбрать ячейки, содержащие значения квадратов, то неправильное графическое отражение значений квадратов видно на примере в окне Масте р диаграмм (шаг 2 из 4): источник данных. Рис. 20.

 

Рис. 20. Неправильное отражение значений квадратов на графике.

 

Для правильного построения графика по значениям квадратов множителей необходимо сначала вызвать Мастер диаграмм, выбрать тип диаграммы – График, перейти на второй шаг и выбрать закладку Ряд. В ячейку Ряд добавить Ряд1 и выбрать для него ячейки ($ B$3;$C$4;$D$5;$E$6;$F$7;$G$8;$H$9;$I$10;$J$11;$K$12) в ячейке Значения, а также в ячейке Подписи по оси Х – блок ячеек $A$3:$A$12.

 

Рис. 21. Правильное построение графика по значениям квадратов множителей.

 

В верхней части окна (рис. 21) отобразится график по значениям квадратов множителей. Нажав кнопку Далее перейти на третий шаг Мастера диаграмм, где ввести название графика в ячейку Название диаграммы и, открыв закладку Подписи данных, установить галочку в ячейку Значения. Теперь осталось только выбрать на каком листе поместить диаграмму (шаг 4). Рис. 22.

 

Рис 22 График по значениям квадратов множителей

 

Пример 2:

Рис. 23 Таблица для моделирования спада температуры тела пациента под действием жаропонижающих препаратов

 

В А2 ввести - 0, а в ячейку А3 ввести формулу =А2+1, и копировать содержимое ячейки А3 при помощи маркера заполнения (наводится курсор мыши на точку, находящуюся в нижнем правом углу этой ячейки и при нажатой левой кнопки мыши растягивается выделение до нужной ячейки) до ячейки А22. Выделить блок ячеек А1:В22, вызвать окно Формат ячеек (пункт меню Формат – опция Ячейки), выбрать закладку Выравнивание и установить выравнивание по горизонтали – по центру, выбрать закладку Граница и установить внешние и внутренние границы в таблице. ОК.

Для комментария объединить ячейки в блок D2:I3: Формат – Ячейки, закладка Выравнивание, установить галочки в ячейки Переносить по словам и Объединение ячеек, далее выбрать закладку Граница, выбрать тип линии и установить внешние границы, нажать ОК. В строке формул для этого блока ввести t=-ax+b - Зависимость температуры (t) от времени (х) в линей ной модели при условии t>=36,6. Аналогично создать таблицу Параметры модели: в Д1 ввести заголовок Параметры модели, объединить ячейки В1:Н1, заголовок по центру блока, в Д8 ввести «а», в Е8 – «0,5», в Д9 – b, в Е9 – 40, в ячейку F8 ввести пояснение, что «а» - это эффективность препарата и объединить ячейки F8:Н8, в ячейку F9 ввести пояснение, что «b» - э