Удобство группировки данных и расчётов в виде таблиц общепризнанно

Вид материала

Документы

Содержание 1. Основные правила работы в excel Выделение всего листа Изменение ширины столбцов (строк). Расположение информации в пределах ячейки. Центрирование заголовка относительно ширины таблицы. Отмена объединения ячеек. Изменение размера, типа, начертания и цвета шрифта. Выделение ячеек цветом. Рисование линий и рамок. Округление числовых данных и изменение формата их представления. Рисование графиков и диаграмм Расчётные алгоритмы в excel 4. Использование visual basic в excel 5. Задания для самостоятельной работы

Подобный материал:

• Методическая разработка по дисциплине «Информатика» Тема: «Организация данных в виде, 447.07kb.
• Отчеты в ms access, 447.2kb.
• Описание массива, 159.18kb.
• Тройке отображения секции помимо формы отображения каждой строки и условий можно использовать, 70.53kb.
• Тема Архитектура Oracle, 83.8kb.
• Курсовая работа, 52.16kb.
• Массивы переменных как однородные статические структуры данных. Строки символов. Инициализация, 70.39kb.
• «Двумерная визуализация таблиц на примере гип-105а», 114.35kb.
• Система управления базами данных, 102.36kb.
• Методика бухгалтерской экспертизы расчетов с покупателями и заказчиками 8 Приемы искажения, 401.96kb.

ВВЕДЕНИЕ


Удобство группировки данных и расчётов в виде таблиц общепризнанно. Именно поэтому стали очень популярными специальные программы, облегчающие ввод в ЭВМ табличных данных и всевозможные манипуляции над ними. Такие программы называют табличными процессорами. В настоящее время наиболее популярными из них являются программы Excel , входящие в пакет Microsoft Office разных версий для Windows.

В данных методических указаниях изложены основные приёмы ввода информации, выполнения разных по сложности вычислений и построения диаграмм средствами Excel 2000, а также приводятся задания для самостоятельной работы, без которой невозможно получение устойчивых навыков работы на компьютере. Для более полного ознакомления с возможностями табличного процессора Excel и другими примерами их использования можно обратиться, например, к [1– 6].


1. ОСНОВНЫЕ ПРАВИЛА РАБОТЫ В EXCEL

1. Список условных обозначений
   

• (Л’) – одинарный щелчок левой кнопкой мышки на объекте, указанном курсором;
  
• (Л”) – двойной щелчок левой кнопкой мышки на объекте, указанном курсором;
  
• (П') – одинарный щелчок правой кнопкой мышки на объекте, указанном курсором;
  
• (Л) – перетаскивание мышкой объекта, который зацеплен курсором;
  
• – название клавиши, которую следует нажать, или кнопки в окне Windows, по которой следует сделать (Л’);
  
• – одновременное нажатие клавиш, названия которых указаны между <>;
  
• ВставкаИмяПрисвоить… – движение по указанным пунктам меню для заказа нужного действия;
  
• названия программ, окон и параметров пишутся с большой буквы;
  
• при заказе параметров в диалоговом окне слово "выбираем" означает, что надо сделать (Л’) по нужным параметрам и затем (Л’) на кнопке подтверждения выбора ( обычно <ОК>, <Готово>, <Применить>, <Далее> и т. п.);
  
• вместо мышки можно двигаться по кнопкам диалогового окна, нажимая клавиши или . Подтверждение выбора осуществляется клавшей . Отказ от выбора – кнопкой <Отмена> или клавишей .
  

1.2. Основные понятия Excel


Главное окно Excel аналогично окнам других приложений Windows. Его вид представлен на рисунке.

Файл документа, созданного в Excel , имеет расширение .xls и называется рабочей книгой. Книга состоит из отдельных листов. На них можно располагать данные, формулы, которые их обрабатывают, пояснительные тексты, индивидуальные программы пользователя для сложной обработки данных и т. д.

Листам можно присваивать удобные имена или пользоваться шаблонными именами "Лист1", "Лист2" и т. д. Заказ листа для работы – (Л’) на ярлычке листа, изменение имени – (Л”) на нём и, затем, набор нового имени вместо выделенного старого.

Рабочее поле листа разбито на ячейки. Каждая ячейка стоит на пересечении столбца и строки. Столбцы обозначаются буквами латинского алфавита, строки – числами. В адресе ячейки первым указывается столбец. Адрес активной ячейки, в которую поступает информация, набираемая на клавиатуре, указывается в адресном поле строки формул. Примеры адресов ячеек: V5, DA3, A78 и т. д. ( но не 5V, 3DA, 78A).

Группа ячеек, которая обрабатывается одной командой, называется блоком. Прямоугольный блок ячеек задаётся адресами концов любой его диагонали, указанными через ":". Если блок имеет сложную форму, его разбивают на прямоугольные фрагменты и перечисляют их координаты через ";". Примеры блоков: А3:А3 – блок из одной ячейки, B2:D4 – блок из девяти ячеек (B2, C2, D2, B3, …, D4), Е2:Е4 – фрагмент столбца (Е2, Е3, Е4), А3:А6;D8:D12 – сложный блок из двух фрагментов столбцов. Если требуется сослаться на столбец (строку) целиком, в адресе указывают только одну компоненту. Пример: А:С – блок состоит из всех ячеек столбцов А, В и С, 12:34 – все ячейки строк с 12 по 34.

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


1.3. Выделение блока ячеек


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

Приёмы выделения прямоугольных блоков:

• Клавишами – .
  
• Мышкой – курсор в форме толстого крестика протащить по нужным ячейкам.
  
• Выделение целого столбца (строки) мышкой – (Л’) на маркере нужного столбца (строки) в адресной линейке.
  
• Выделение группы столбцов (строк) мышкой – (Л) по маркерам нужных столбцов (строк) в адресной линейке.
  
• Выделение всего листа – (Л’) на пустой клетке, которая находится вверху слева на пересечении адресных линейки и столбца.
  

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

Чтобы снять выделение, достаточно сделать (Л’) или нажать любую клавишу навигации курсора без .


1.4. Ввод текстов


Текст вводится обычным способом, как на пишущей машинке или в документ Word. По умолчанию он прижимается к левому краю ячейки. Если в тексте часто встречаются повторяющиеся слова или выражения, ввод можно ускорить с помощью команды Сервис  Автозамена…, которая действует также, как в Word’е.

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


1.5. Ввод чисел


К числам относятся все данные, имеющие количественный смысл: арифметические числа, денежные суммы, даты, моменты времени и т. д. Единицы измерения при вводе опускаются. Если для удобства нужно видеть их на экране, их вводят в соседние ячейки как пояснительные тексты или выводят на экран с помощью команды Формат  Ячейки… (вкладка Число). На экране числа прижимаются по умолчанию к правому краю ячейки.

При вводе целая часть отделяется от дробной запятой, а не точкой, как это принято в большинстве языков программирования. На цифровой клавиатуре запятая вставляется автоматически при нажатии < . >. При вводе дат в качестве разделителя используют точку символьной клавиатуры. При вводе времени – двоеточие. Примеры: 1,5 – число, 1.5 – дата первое мая. 1:5 – время пять минут второго.

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

Если вводимые числа меняются с постоянным шагом, можно воспользоваться автозаполнением ячеек. Для этого следует:

1. Ввети два первых числа ряда.
   
2. Выделить эти ячейки.
   
3. Протянуть их (см. п. 1.3) в том же направлении, в каком вводились первые числа, до тех пор, пока во всплывающем окне не появится конечное значение ряда.
   

Для ввода чисел, меняющихся по геометрической прогрессии, можно воспользоваться командой Правка  Заполнить…

Задание.

• Введите в ячейки А1:А11 ряд чисел 1; 1,3; 1,6; …;4.
  
• Заполните ячейки В1:В21 теми же числами, но введёнными через строчку (два первых числа вводим через строчку, выделяем В1:В4, протягиваем до В21).
  
• Закажите для введённых в столбец А чисел денежный формат, для столбца В – процентный.
  

1.6. Ввод стандартных списков.


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

Набор стандартных списков можно изменять. Для этого следует:

1. выполнить команду Сервис  Параметры… (вкладка Списки);
   
2. в окне Списки сделать (Л’) по строке Новый список;
   
3. в окне Элементы списка ввести нужные значения по одному на строке;
   
4. нажать кнопку <Добавить>;
   
5. нажать кнопку <ОК>.
   

Задание.

• Введите в С1:С50 даты, начиная с 15 мая, в D1:Х1 дни недели, начиная с субботы.
  
• Создайте список каких-либо предметов и введите его несколько раз, начиная с разных мест списка.
  

1.7. Ввод формул


Ввод формулы обязательно начинают со знака "=" и заканчивают нажатием клавиши . После этого текст формулы высвечивается в информационном поле строки формул, а рассчитанное значение – в той ячейке, в которую была введена формула. Такие формулы называются формулами ячейки.

Если в результате работы формулы рассчитывается несколько значений, перед её вводом следует выделить блок ячеек, в которых надо разместить результаты, набрать формулу по обычным правилам и закончить ввод комбинацией клавиш . Формулы, введённые таким образом, называются формулами массива (см. также [3] и п. 3.5).

Арифметические действия в формулах обозначаются так же, как в языке Бейсик. Порядок выполнения действий такой же, как в арифметике.

Действие	Возведение в степень	Умножение	Деление	Сложение	Вычитание
Обозначение		*	/	+	-

В формулы можно вставлять как конкретные числа, с которыми проводятся расчёты, так и ссылки на ячейки, в которых они размещены. Адреса ячеек можно вводить непосредственно набивкой в английском регистре или с помощью (Л’) по нужной ячейке. Если в формуле используется блок ячеек, его адрес можно ввести с помощью (Л) по нужному блоку.

При изменении данных в ячейках, на которые ссылается формула, происходит автоматический пересчёт её значения.

Excel имеет большую библиотеку встроенных функций, облегчающих обработку данных. Поиск нужной функции и вставку её в формулу удобно делать с помощью Мастера функций (см. п. 1.8.).

Для перехода в режим корректировки введённой формулы делают (Л”) на ячейке с неправильной формулой. Можно так же установить курсор на её ячейке и нажать клавишу или сделать (Л’) в строке формул на нужном фрагменте.

Для ускорения ввода однотипных формул, последовательно использующих данные из некоторого блока, можно использовать протяжку или копирование (см. п. п. 1.3, 1.12).

Задание.

Введите в ячейки А1:А3 какие-либо числа. В ячейку В1 введите формулу x²-2y²+3z², подставляя в явном виде вместо x, y и z те значения, которые указаны в А1, А2 и А3 соответственно. В ячейку В2 введите ту же формулу, ссылаясь на ячейки, в которых расположены нужные значения. Используйте при этом как прямую набивку адреса, так и (Л’) по ячейке. Измените числа, введённые в А1:А3. Измените коэффициент при y на –4. Проследите, как отражаются Ваши действия в строке формул и на рабочем поле листа.


1.8. Работа с Мастером функций


При первом обращении к Мастеру функций во время набора формулы эту программу можно вызвать либо командой Вставка  Функция…, либо кнопкой с надписью f_x на стандартной панели инструментов.

Если формула начинается с функции, знак "=" набирать необязательно, Мастер вставит его сам.

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

Работа Мастера разбита на два шага.

На первом в правой части окна выделяется нужная функция. Для того чтобы облегчить её поиск, в левой части в поле Категория можно выделить нужный тип функций. По умолчанию всегда устанавливается тип "10 недавно использовавшихся". Пояснения по смыслу выбранной функции размещаются в нижней части окна и, если нужно, вызываются через справку. После того, как функция найдена, нажимают <ОК>.

На втором шаге Мастер выдаёт окно с полями для ввода аргументов. В них можно вводить константы, ссылки на ячейки или блоки, арифметические или логические выражения. Способы ввода аргументов те же, что и при наборе формулы. Для удобства ввода можно с помощью (Л) переместить окно аргументов в другую часть экрана так, чтобы оно не загораживало ячейки с нужными данными. Справа от каждого поля выдаётся значение набранного аргумента, под полями всех аргументов – значение функции, внизу окна – значение всей формулы в целом.

Если функция заканчивает формулу, можно нажать <ОК> или клавишу , в противном случае следует сделать (Л’) в тексте формулы и продолжать её набор.

Если в аргумент одной функции входит другая функция, то она называется вложенной. Такую функцию можно вызвать только через адресное поле строки формул. По умолчанию в нём высвечивается последняя функция, с которой работал Excel . (Л’) по ней выдаёт сразу окно аргументов (второй шаг Мастера). Если нужно вставить другую функцию, (Л’) по кнопке в правой части адресного поля вызывает их список. В нём перечислены последние функции, с которыми работал Мастер. Если среди них нет нужной, заказывают строку Другие функции…, которая вызывает первое окно Мастера. Для того чтобы окончить работу с вложенной функцией и продолжать набор аргументов первой, следует сделать (Л’) по названию первой функции в информационном поле строки формул.

Мастер функций допускает использование до семи вложенных функций.

Задание.

Введите в ячейки А1:А10 и В5:В10 какие-либо числа. В ячейку С1 с помощью Мастера функций введите формулу

=СУММ(МАКС(А1:А10);МАКС(В5:В10);МИН(А1:А10);МИН(В5:В10))


1.9. Присваивание имён ячейкам и блокам


Excel содержит ряд средств для замены стандартных адресов на текстовые имена. Практика показывает, что это удобнее при ссылках на информацию из ячеек. Основное ограничение: текстовые имена не должны быть похожи на стандартные адреса ячеек. Например, нельзя использовать для ячейки В15 имя "х1".

Самый простой способ создания имён заключается в следующем:

1. Выделяют нужную ячейку или блок.
   
2. Делают (Л’) по адресному полю в строке формул. Стандартный адрес, размещённый в нём, выделяется.
   
3. Набирают удобное пользователю имя.
   
4. Нажимают клавишу для того, чтобы подтвердить замену. Если вместо сделать (Л’) в рабочем поле, то замена стандартного адреса отменяется.
   

Кроме этого способа можно использовать команды меню. Основные из них:

• ВставкаИмяПрисвоить… Эта команда может как присваивать, так и удалять имена. Для создания нового имени следует:
  

1. Выделить нужную ячейку или блок.
   
2. Заказать команду.
   
3. В диалоговом окне в поле Имя ввести текст имени. Если в рабочем поле листа рядом с выделенным блоком предварительно был введён какой-либо текст, то он автоматически вставляется в поле имени. Если нужно присвоить другое имя, оно вводится вручную.
   
4. Нажать кнопку <ОК> или клавишу .
   

• ВставкаИмяСоздать… Эту команду используют, если ячейки и удобные для их обозначения имена расположены на рабочем листе в виде горизонтальной или вертикальной таблицы, и тексты имён уже введены. Для одновременного создания всех имён следует:
  

1. Выделить блок, состоящий из текстов имён и именуемых ячеек.
   
2. Заказать команду.
   
3. В диалоговом окне сделать (Л’) по параметру, который правильно указывает, где находятся тексты по отношению к именуемым ячейкам.
   
4. Нажать кнопку <ОК> или клавишу .
   

Для удаления уже существующих имён надо:

1. Заказать команду ВставкаИмяПрисвоить…
   
2. В диалоговом окне пометить ненужные имена на удаление. Для этого следует сделать (Л’) и нажать кнопку <Удалить> на каждом из них.
   
3. Нажать кнопку <ОК> или клавишу .
   

Задание.

Введите в ячейки А3:I3 какие-либо тексты, в ячейки под ними – числа. Присвойте через адресное поле ячейке А4 имя, совпадающее с текстом в А3, а ячейке В4 – не совпадающее с текстом в В3. Для ячеек С4:I4 создайте имена с помощью других команд. Проследите по адресному полю, как теперь обозначает Excel эти ячейки. В А5 введите формулу =А4+В4*С4, в В5 – ту же формулу, но используя новые имена ячеек ( либо прямой набивкой, либо по (Л’) ).


1.10. Использование подписей данных


Подписью столбца (строки) называется любой текст, размещённый сверху (слева) вплотную к данным. Подпись – это обобщённое название всех ячеек, расположенных ниже (правее) ячейки, в которой она расположена. Пример: в ячейках А20:А30 размещены аргументы функции, в ячейках В20:В30 – формулы, вычисляющие её значения. Если в А19 и В19 ввести соответственно слова "аргумент", "функция" или просто "х", "у", то они являются подписями всех данных, расположенных ниже А19 (даже, если они введены с пропусками ячеек).

Подписи, так же как и имена, можно использовать в формулах вместо стандартных адресов. Для этого надо в группе "Параметры книги" команды Сервис  Параметры… (вкладка Вычисления) установить флажок на параметре "Допускать названия диапазонов".

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


1.11. Правка информации


Самый простой способ – повторно ввести в ячейку новую информацию. Если это нежелательно, можно перейти в режим правки с помощью (Л”) на нужной ячейке. Можно так же установить курсор на эту ячейку и нажать клавишу или сделать (Л’) в строке формул на нужном фрагменте.

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

Для очистки ячеек выделяют нужный блок и нажимают или выполняют команду ПравкаОчистить, в которой можно уточнить, что конкретно следует удалить.

Отменить неверные изменения до выхода из режима правки можно клавишей , после выхода – горячими клавишами , кнопкой "Отменить" (в центре стандартной панели инструментов) или командой Правка  Отменить ввод…


1.12. Копирование и перемещение информации


Источник – блок, в котором первоначально размещена информация. Адресат – блок, в котором она размещена после выполнения команды. Техника выполнения этих команд та же, что и в программе WORD, а именно:

1. Выделяют блок-источник.
   
2. Выполняют команду Вырезать или Копировать, которую можно заказать в меню Правка, в контекстном меню (П') или на стандартной панели инструментов.
   
3. Выделяют блок-адресат. Если он по форме такой же, как блок-источник, можно выделить только левую верхнюю ячейку.
   
4. Выполняют команду Вставить, которую можно заказать в меню Правка, в контекстном меню (П') или на стандартной панели инструментов.
   

При перемещении блока на небольшое расстояние можно после п. 1 зацепить мышкой границу блока (курсор превращается в стрелку, направленную влево-вверх) и перетащить её на новое место.

При копировании в ячейки, смежные с источником, можно после п. 1 воспользоваться протяжкой (см. п.п. 1.3, 1.7).

Особенности выполнения операций.

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

• При копировании (протяжке) формул ссылки на данные, указанные именами блоков или ячеек, не изменяются, а те, что заданы адресами или подписями столбцов (строк), автоматически изменяются на то количество строк и столбцов, на которое смещена формула. Ссылки, которые не надо менять при копировании (протяжке), называют абсолютными, изменяемые – относительными. Т. о. имена ячеек или блоков являются абсолютными ссылками, адреса и подписи столбцов (строк) – относительными. Адрес или его часть можно сделать абсолютной ссылкой, не присваивая ячейке имени. Для этого следует поставить перед неизменяемой частью символ "$". Пример: $D$4 – при копировании в любом направлении формулы, содержащей эту ссылку, данное всегда берётся из ячейки D4. D$4 – закреплена строка. Если, к примеру, копия формулы с этой ссылкой сдвинута на два столбца правее источника, ссылка на данное заменяется на F$4, если же копия сдвинута на две строки вниз, то ссылка остаётся неизменной. Закрепление всего адреса или его частей при набивке удобно делать повторными нажатиями клавиши .
  
• Вид ссылки, указанной именем блока, при копировании (протяжке), не изменяется, но для расчётов берётся то значение из него, которое находится в той же строке (столбце), что использующая его формула. Если надо использовать значения из других строк (столбцов) блока, следует задавать их стандартными адресами.
  
• Если в формуле используются подписи данных, то при копировании (протяжке) вдоль столбца (строки) с подписью действуют те же правила, что и для имени, в другом направлении – подписи заменяются на новые.
  

Задание.

Установите по команде Сервис  Параметры… (вкладка Вычисления) флажок на параметре "Допускать названия диапазонов".

Введите в ячейки А1:F1 тексты "h", "x", "y", "z", "k", "v". Ячейке А2 присвойте имя h и введите в неё число 10. В В2:В10 введите какие-нибудь числа, в С2 – формулу =В2*h, в D2 – формулу =x*h. Протяните С2:D2 вниз до строки 10. Выделите D2:D10 и протяните этот блок на столбцы E, F. Обратите внимание на то, как выглядят формулы в этих ячейках и какие результаты получены по ним.

Теперь в ячейке C2 закрепите адрес В2 ($В$2) и протяните её вниз, вбок. Посмотрите, как изменится вид формулы и результат. Закрепите часть адреса В2 и повторите процедуру.


1.13. Команды форматирования


Термин форматирование обозначает изменение вида информации без изменения её смысла. Форматирование применяют для того, чтобы информация выглядела удобно для глаза и дальнейшего анализа. Все команды форматирования применяются к предварительно выделенной ячейке, блоку, столбцам или строкам и собраны в диалоговом окне Формат ячеек, которое вызывается по команде ФорматЯчейки… или через контекстное меню (вызывается по (П')). Основные команды можно также заказать в панели инструментов Форматирование (если её нет на экране, она вызывается командой ВидПанели инструментов…).

К основным командам форматирования относятся:

• Изменение ширины столбцов (строк). Курсор мышки располагается в адресной линейке на правой границе нужного столбца. (Л”) автоматически подбирает ширину по длине самого длинного данного в этом столбце, (Л) позволяет передвинуть границу вручную. Аналогично изменяется ширина строк. Можно также воспользоваться командами ФорматСтолбцы… (ФорматСтроки…).
  
• Расположение информации в пределах ячейки. В панели Форматирование этим управляют кнопки с макетами строк, аналогичные кнопкам в Word'е. В диалоговом окне Формат ячеек (команды ФорматЯчейки…) на вкладке Выравнивание есть также параметры, позволяющие по-разному ориентировать текст, объединять ячейки в выделенном блоке в одну (отменять ранее сделанное объединение), переносить тексты по словам, чтобы они были видны целиком даже в столбце небольшой ширины.
  
• Центрирование заголовка относительно ширины таблицы. В строке заголовка выделяют ячейки, по которым надо центрировать текст, и нажимают кнопку "Объединить и поместить в центре" (справа от кнопок с макетами строк).
  
• Отмена объединения ячеек. Выделяют объединённую ячейку, выполняют команду ФорматЯчейки… (вкладка Выравнивание) и снимают флажок с параметра "Объединение ячеек".
  
• Изменение размера, типа, начертания и цвета шрифта. В панели Форматирование этим управляют кнопки такие же, как в Word'е. В диалоговом окне Формат ячейки эти возможности собраны на вкладке Шрифт. Там же есть дополнительные параметры, позволяющие выделенные символы представить в виде нижних или верхних индексов.
  
• Выделение ячеек цветом. В панели Форматирование этим управляет кнопка "Цвет заливки" (ведёрко, из которого выливается краска). В диалоговом окне Формат ячейки палитра возможных цветов представлена на вкладке Вид.
  
• Рисование линий и рамок. В панели Форматирование основной набор линий представлен в выпадающем меню кнопки "Границы". Заказ какой-либо линии в выделенном блоке – утопленный дизайн кнопки, отсутствие данной линии – выпуклый дизайн. Чтобы изменить расположение линий, следует сделать (Л’) по нужной кнопке. В диалоговом окне Формат ячейки на вкладке Граница можно так же заказать разные типы и цвет линий.
  
• Округление числовых данных и изменение формата их представления. С помощью этих команд добавляют обозначения валют к денежным данным, вводят разделители разрядов в больших числах, выбирают нужное представление дат и прочее. В панели Форматирование кнопки этих команд обычно располагаются в центре между кнопками выравнивания и границ. В диалоговом окне Формат ячейки эти команды собраны на вкладке Число. Если числовая информация, которую Вы вводите, меняет вид, следует проверить на этой вкладке, какой формат данных заказан в ячейке и, если он неправильный, изменить его.
  

Задание.

Введите в ячейку А1 заголовок: "Таблица функции", в ячейки А2, В2, С2 соответственно тексты: "х", "у", "максимальное значение". В блок А3:А15 занесите протяжкой значения аргументов, например, 2; 2,5; 3; …; 8. В блоке В3:В15 также с помощью протяжки разместите формулу какой-нибудь функции от этих аргументов, например, y=2cos(x+2)e^0,5x. Выполните следующее форматирование полученной таблицы:

• отцентрируйте заголовок;
  
• выровняйте по центру ячеек всю информацию таблицы;
  
• округлите до двух знаков после запятой значения функции;
  
• выделите заливкой ячейки А2:С2 и разрешите перенос по словам в них;
  
• закажите красный шрифт для С3:С15;
  
• обведите жирной рамкой таблицу и строку заголовка, проведите тонкие пунктирные линии между столбцами.
  
• с помощью функции ЕСЛИ (см. п. 3.3) отметьте символом "*" строку, в которой функция принимает максимальное значение.
  

2. РИСОВАНИЕ ГРАФИКОВ И ДИАГРАММ
   

Графики и диаграммы делают более наглядной и понятной числовую информацию, которая введена в таблицу или получена в результате расчётов. Шаблон нужного графика или диаграммы создаётся с помощью программы Мастер диаграмм, которая заказывается командой ВставкаДиаграмма… или кнопкой "Мастер диаграмм" в стандартной панели инструментов (разноцветные вертикальные столбики). Работа Мастера разбивается на четыре шага. После окончания его работы подготовленный им шаблон корректируется пользователем для того, чтобы получить максимальную наглядность в иллюстрации числового материала.

Задание.

Построить диаграмму, иллюстрирующую таблицу функции, полученную в п. 1.13.

1. Первый шаг Мастера диаграмм: тип диаграммы
   

Выбор типа диаграммы зависит от характера данных, которые будут отображаться. Пояснения к каждому типу расположены справа в нижней части окна. Над ними предлагаются варианты представления диаграмм данного типа. Нужный выбирается с помощью (Л’) по нему. Вертикальная ось называется осью Y или осью значений, вдоль неё всегда откладываются числовые показатели. Горизонтальная (ось абсцисс) – называется осью Х, если на ней откладываются числовые аргументы, и осью категорий, если аргументами являются тексты (названия фирм, материалов, фамилии людей и т.п.).

Основные замечания:

• Если по оси абсцисс надо откладывать числовые аргументы, удобнее всего пользоваться Точечной диаграммой. Эта диаграмма находит среди аргументов максимальное значение и выбирает по нему подходящий масштаб изображения. Порядок размещения точек на графике зависит только от величины аргументов, а не от последовательности их ввода в таблицу.
  
• Большинство диаграмм рассматривают ось абсцисс как текстовую. Ось разбивается на равные промежутки по количеству отображаемых точек, и значения функции откладываются в той последовательности, в какой они были введены в таблицу. При изменении порядка ввода данных вид диаграммы меняется. Например, если изобразить с помощью диаграммы типа График опытные данные по зависимости энергопотребления от температуры наружного воздуха, то точки будут расположены вдоль оси абсцисс не по величине температуры, а вперемежку, так, как они вводились. На диаграмме типа Точечная для этих же данных большие температуры окажутся правее маленьких.
  
• Круговая диаграмма отображает только один ряд данных. На ней удобно оценивать вклад каждого значения в общую сумму.
  
• Если нужно представить на одной диаграмме два числовых показателя от одних и тех же числовых или текстовых аргументов, и значения этих показателей сильно различаются по величине, то удобно пользоваться Пузырьковой диаграммой. Один показатель будет отображён высотой пузырька, другой – его радиусом.
  
• Кроме типов, представленных на вкладке "Стандартные", полезно ознакомиться с предложениями вкладки "Нестандартные".
  

Выберем для нашего задания Точечную диаграмму с гладкими кривыми без маркеров.


2.2. Второй шаг Мастера диаграмм: источник данных диаграммы


Окно второго шага содержит две вкладки.

На вкладке "Диапазон данных" в поле "Диапазон" вводятся координаты блока, который содержит значения аргументов, функций и подписи к ним. Эти координаты можно ввести прямой набивкой или протащив мышку по нужным ячейкам. Несмежные области добавляются при протаскивании при нажатой клавише . Параметр "Ряды в:" задаёт ориентацию данных. Образец, встроенный в Окно, показывает примерный вид будущей диаграммы. Если Мастер использует данные неправильно, следует перейти на вкладку "Ряд".

Основные элементы, на которые надо обращать внимание:

• что откладывается по оси абсцисс – если вместо реальных аргументов откладываются номера точек (1, 2, …), Мастер не понял, где находятся аргументы или выбран неверный тип диаграммы;
  
• как выглядят обозначения функций в легенде – если вместо реальных названий внесены стандартные тексты "Ряд1", "Ряд2" и т. д., – не найдены ячейки с подписями функций;
  
• сколько функций построено на диаграмме – если реальное количество не совпадает с нужным, задан неверный диапазон данных или выбран неправильный тип диаграммы.
  

На вкладке "Ряд" представлено окно с перечнем обозначений всех функций, изображённых на диаграмме, и поля, в которых указано, из каких ячеек мастер берёт информацию для выделенной функции. В поле "Имя" можно ввести либо адрес ячейки, в которую оно занесено, либо непосредственно текст, которым удобно обозначить эту функцию. Если какая-то функция оказалась лишней, её выделяют и нажимают кнопку <Удалить>. По кнопке <Добавить> Мастер выдаёт бланк с чистыми полями, в которые следует внести координаты блоков, в которых размещены, аргументы, значения и имя новой функции.

Для нашего задания на вкладке "Диапазон данных" внесём координаты блока данных А2:В15 и поставим переключатель "Ряды в столбцах". Посмотрите, как эта же информация оказалась представленной на вкладке "Ряд".

3. Третий шаг Мастера диаграмм: параметры диаграммы
   

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

Введите заголовки: для диаграммы – Учебный график, для оси Х – Аргументы, для оси Y – функция. Закажите основные линии сетки по осям Х и Y. Откажитесь от легенды.

3. Четвёртый шаг Мастера диаграмм: размещение диаграммы
   

На этом шаге всего два параметра:

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

Закажем размещение на том же листе.

3. Исправление диаграммы
   

Шаблон, построенный мастером, часто приходится корректировать. Переход в режим правки можно сделать разными способами:

(Л’) или (Л”) по любому элементу диаграммы приводит к тому, что элемент, на который указывал курсор, выделяется маркерами, а меню Данные превращается в меню Диаграмма. Четыре первых команды этого меню повторяют шаги Мастера диаграмм и позволяют вызвать окно того шага, на котором нужно что-то исправить. Остальные команды предлагают возможности, которые нельзя было заказать ранее. Наиболее важная из них – Добавить линию тренда… (см. п. 2.6). В меню Формат первой строкой появляется команда вызова окна форматирования выделенного элемента, которая позволяет изменить его оформление. Если переход в режим правки сделан по (Л"), то это окно открывается сразу.

Перейти в режим правки можно также, сделав (П') по любому элементу диаграммы. При этом открывает контекстное меню, в котором находятся основные команды корректировки для этого элемента.

Ряд элементов после выделения обводится рамкой с квадратными маркерами (область диаграммы, область построения диаграммы, легенда, заголовки диаграммы или осей). Зацепившись за неё, можно перетащить мышкой выделенный элемент на новое место, а зацепившись за маркер – изменить его размеры.

Выполните следующую корректировку шаблона, сделанного Мастером:

• увеличьте размер диаграммы и перенесите её на другое место;
  
• расширьте Область построения диаграммы за счёт полей;
  
• подберите другую заливку области построения диаграммы;
  
• измените минимальное и максимальное значения по оси Х, а также размер и цвет шрифта в подписях;
  
• сделайте пунктирными линии сетки;
  

3. Построение линии тренда
   

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

На вкладке Тип приведены графики стандартных линий тренда:

• линейная – уравнение y=m*x+b;
  
• логарифмическая – уравнение y=c*ln(x)+b;
  
• полиномиальная – уравнение y=b+c₁*x+c₂*x²+ …+c_n*xⁿ, где n=2, 3, 4, 5 или 6;
  
• степенная – уравнение y=c*x^b;
  
• экспоненциальная – уравнение y=c*e^bx;
  
• линейная фильтрация – используется метод скользящего среднего, при котором каждое значение функции заменяется на среднее арифметическое по n соседним точкам, расположенным симметрично относительно данной (n от 2 до 20). Линия тренда в этом случае уравнения не имеет.
  

На вкладке Тип следует сделать (Л’) по тому графику, вид которого кажется наиболее подходящим для сглаживаемой функции. Если на диаграмме представлено несколько функций, то следует дополнительно выделить название нужной функции в поле "Построен на ряде", расположенном под графиками.

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

На вкладке Параметры можно задать:

• название тренда;
  
• продление графика тренда за пределы диапазона аргументов (параметр "Прогноз");
  
• вывод уравнения тренда в область построения диаграммы (параметр "Показывать уравнение на диаграмме").
  

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

Задание.

Внесите на диаграмму полиномиальный тренд третьей степени и его уравнение. Представьте его цветным пунктиром. Переместите уравнение тренда из Области построения диаграммы на её поля.

2. РАСЧЁТНЫЕ АЛГОРИТМЫ В EXCEL
   

Большинство основных вычислительных алгоритмов в Excel оформлены в виде стандартных функций и вызываются с помощью программы Мастер функций (см. п. 1.8). Самые популярные из них:

• ЕСЛИ – позволяет предусмотреть разные варианты заполнения ячейки;
  
• СУММ, ПРОИЗВЕД – соответственно суммирование и произведение значений в одном или нескольких блоках;
  
• СУММПРОИЗВ – суммирование произведений соответствующих элементов двух или нескольких массивов;
  
• СРЗНАЧ, СРГЕОМ – расчёт соответственно среднего арифметического и геометрического по числам в заданных блоках;
  
• СЧЁТ – определение количества чисел в заданном блоке.
  

Стандартную библиотеку можно дополнять функциями, созданными пользователем самостоятельно (см. [3, 4]).

Более сложные алгоритмы оформлены в виде команд и заказываются через меню Сервис. Наиболее важные из них:

• Подбор параметра… – нахождение корня уравнения;
  
• Поиск решения… – решение систем уравнений и задач оптимизации;
  
• Пакет анализа – содержит программы для решения сложных статистических и инженерных задач.
  

Если нужная для вычислений команда отсутствует в меню, её можно установить с помощью команды СервисНадстройки…


3.1. Расчёт таблицы значений функции от одного аргумента


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

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

Пример.

Найти графически координаты корней и максимумов функции Y=2cos(x+2)e^-0,5x

Составим план размещения информации:

Ячейки	Информация	Значение
А1	Заголовок расчёта	Поиск корней и экстремумов
А2:В2	Названия констант, необходимых для расчёта аргуменов	А2: Начало, В2: Шаг
А3:В3	Ориентировочные значения констант	А3: 0, В3: 1
А4:В4	Заголовки таблицы	А4: Х, В4: Y
А5	Формула для первого аргумента	=$A$3
А6	Формула для второго аргумента	=А5+$B$3
А7:А25	Формулы для остальных аргументов	Заполняются протяжкой
B5	Формула для первого значения функции	=2*COS(A5+2)*EXP(-0,5*A5)
В6:В25	Формулы для остальных значений функции	Заполняются протяжкой

Формулы, занесённые в ячейки А5:А25, будут нагляднее, если перед их набором присвоить константам в А3:В3 имена (см. п. 1.9) и вводить их с помощью (Л’), а в формуле функции использовать название столбца аргументов: =2*COS(Х+2)*EXP(-0,5*Х) (см. п. 1.10).

Приведённый выше алгоритм позволяет составить таблицу функции при аргументах, меняющихся с нужным нам шагом. Количество шагов, на которое рассчитана таблица, заранее известно (в нашем примере это 20). Поэтому конечный аргумент, рассмотренный в таблице, определяется по формуле =А3+В3*20. Такой способ перебора аргументов не всегда удобен. Иногда мы знаем начальный и конечный аргументы, при которых функция представляет для нас интерес. И нам нужно подобрать шаг, с которым следует двигаться по аргументам, чтобы в расчётах не выйти за нужные границы. В этом случае в ячейки А

Задание.

Отформатируйте полученную таблицу и постройте диаграмму типа Точечная по ней.

Таблица и график показывают, что при х>9 функция практически равна нулю, первый корень лежит в диапазоне 2, первый экстремум – около х=4. Введём в А3:В3 новые константы: 2 и 0,1 соответственно. Excel сразу же пересчитал таблицу и график на новый диапазон аргументов и теперь можно локализовать корень и экстремум уже с точностью 0,1. Диапазон для корня 2,7, координата экстремума – x=3,5. При необходимости можно снова изменить константы, с помощью которых создаётся диапазон аргументов, и продолжить уточнение ответов.

Задание.

Найдите какой-нибудь корень и экстремум этой функции в отрицательной области аргументов.


3.2. Расчёт таблицы значений функции от двух аргументов


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

Рассмотрим пример. Составить таблицу и график функции z=y²x-ln(x+y) для диапазонов 2 и 1.

Будем менять х с шагом 0,4, у – с шагом 0,8. Составим план размещения информации:

Ячейки	Информация	Значение
А1	Заголовок расчёта	Функция двух переменных
А4	Название первой переменной	Х
В4:В14	Значения первой переменной	2; 2,4; 2,8; …;6 (заполняются протяжкой)
С2	Название второй переменной	Y
С3:Н3	Значения второй переменной	1; 1,8; 2,6; …; 5 (заполняются протяжкой)
С4	Формула для первого значения функции	=C$32*$B4-LN($B4+C$3)
С4:Н14	Формулы для остальных аргументов	Заполняются протяжкой С4 сначала вбок, затем –вниз

При вводе формулы в ячейку С4 символы "$" расставлены с таким расчётом, что при протяжке вбок разрешено менять адрес только у аргумента Y, аргумент Х для всей полученной строки берётся из одной и той же ячейки (в адресе Х закреплён столбец). При протяжке вниз – наоборот: в адресе аргумента Y закреплена строка, поэтому он не меняется, и в столбце оказываются значения функции, сосчитанные для одного и того же Y, но разных Х. Символ "$" можно вводить непосредственной набивкой в английском регистре или повторными нажатиями клавиши (см. п. 1.12).

Задание. Отформатируйте полученную таблицу (см. п. 1.13):

• объедините ячейки С2:Н2;
  
• объедините ячейки А4:А14, измените направление текста для Х;
  
• выделите цветом шрифта или заливкой заголовки таблицы (С2:Н3 и А4:В14);
  
• обведите толстой линией всю таблицу и отделите этой же линией заголовки с аргументами от значений функции;
  
• разделите тонкими линиями столбцы таблицы.
  

Перейдём к построению диаграммы. Представим на ней зависимость z(x) при разных y.

На первом шаге закажем тип Точечная с гладкими кривыми без маркеров. На втором шаге выделим диапазон данных В4:Н14 и параметр "Ряды в столбцах". На вкладке Ряд внесём для рядов данных имена: у=1, у=1,8 и т. д. На третьем шаге закажем легенду, основную сетку по осям и введём какие-нибудь заголовки. На четвёртом – размещение на том же листе.

Задание. Откорректируйте диаграмму:

• закажите более толстые линии для каждой зависимости;
  
• измените масштаб по оси Х;
  
• подберите тренд для одной из линий и выведите его уравнение на диаграмму.
  

Постройте вторую диаграмму, на которой будет представлена зависимость z(у) при разных х.

Примечание. Если перед вводом формулы в С4 присвоить диапазонам С3:Н3 и В4:В14 имена соответственно у и х, то формулы в С4 можно ввести в более понятном виде: =у2*x-LN(x+y) (см. п. п. 1,9, 1.12).


3.3. Использование функции ЕСЛИ для анализа информации


Функция ЕСЛИ позволяет предусмотреть разные способы заполнения одной и той же ячейки. То, каким из них следует воспользоваться в данный момент, Excel определяет самостоятельно по тому, выполняется или нет при введённых данных указанное в функции условие. Рассмотрим действие этой функции на конкретных примерах.

Пример 1.

Поставщик ввёл оптовую скидку на цену для больших партий товара. Надо составить шаблон для расчёта стоимости любой партии товара.

Составим таблицу из констант, необходимых для расчёта. В ячейки А1:А4 ведём названия констант: "ОбъёмПартии", "ОптБарьер", "РознЦена", "ОптЦена". Присвоим ячейкам В1:В4 такие же имена (удобно пользоваться командой ВставкаИмяСоздать, см. п. 1.9). В ячейку С1 введём текст "СтоимПартииТовара".

Выделим ячейку С2 и вызовем через Мастер функций функцию ЕСЛИ. В окне аргументов введём для них следующие значения.

В аргумент "Логическое_выражение:" вводится условие, по которому Excel выбирает нужный вариант действий. Оно выглядит так (правила ввода аргументов изложены в п. 1.7):

ОбъёмПартии<=ОптБарьер

В строке второго аргумента ("Значение_если_истина:") указывается способ, по которому следует заполнить ячейку при правильном условии. Им может быть готовая константа, ссылка на ячейку, в которой содержится нужная информация или расчётная формула. Для нашего примера этот аргумент выглядит так:

ОбъёмПартии* РознЦена

И, наконец, в третьем аргументе ("Значение_если_ложь:") указывается, как заполнять ячейку, если условие не выполняется. Для нашего примера следует ввести

ОбъёмПартии* ОптЦена

Чтобы не набирать заново длинные названия переменных, можно ввести их с помощью (Л’) по соответствующим ячейкам в столбце "В". Расчётный шаблон готов. Чтобы проверить его, введите простые числа в ячейки В1:В4 и проверьте, правильно ли функция ЕСЛИ выбрала формулу для заполнения ячейки С2. Введите в В1 другой объём партии, при котором требуется использовать вторую формулу. Если в обоих случаях получены верные результаты, можно красиво отформатировать ячейки А1:С4 (см. п. 1.13) и пользоваться этим шаблоном, меняя только значения констант в В1:В4.

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


Пример 2.

Заполним последний столбец в Задании п. 1.13.

Выделим ячейку С3 и вызовем через Мастер функций функцию ЕСЛИ. Для аргументов введём следующие значения.

Условие, по которому Excel выбирает нужный вариант действий, выглядит так: В3=МАКС($B$3:$B$15). В строку второго аргумента вводим символ "*" (без кавычек), в третий – пробел и нажмём после этого <ОК>.

Протянем полученную формулу по блоку С3:С15.

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

Рассмотрим ЕСЛИ в той строке, в которой появилась "*". Значение функции y в этой строке максимально. Левая и правая части условия оказались одинаковыми, то есть первый аргумент – правильный. Поэтому для заполнения своей ячейки ЕСЛИ выбрала то, что указано во втором аргументе. Для значений функции у в других строках условие, введённое в функцию ЕСЛИ, оказывается неверным, поэтому она заполняет свои ячейки по варианту третьего аргумента. В нашем случае это пробел, который невидим на экране, поэтому ячейки кажутся пустыми.

Измените аргументы, введённые в А3:А15. "*" переместилась в другую строку, хотя формулы в С3:С15 не были изменены. После изменения данных каждая функция ЕСЛИ автоматически проверила свой первый аргумент наново и приняла новое управляющее решение, каким правилом пользоваться для заполнения своей ячейки.

Задание.

Введите в блок D3:D15 функцию ЕСЛИ, которая поставит знак "-" в строках с отрицательными значениями "у" и знак "+" – для положительных "у".

Другие варианты использования функции ЕСЛИ.

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

Пример 3.

Объём заказа k, который принимает фирма, должен лежать в диапазоне от a1 до a2 штук изделий. Цена одного изделия составляет d рублей. Составить шаблон для расчёта стоимости заказа, который будет выдавать предупреждение, если объём не попал в допустимые границы.

Введём в ячейку А1 общее название шаблона: Расчёт стоимости заказа, в А3:А6 названия переменных, а в В3:В6 их значения. Названия переменных, использованные при формулировке примера, неудобны для Excel – расчёта: а1, а2 совпадают со стандартными адресами других ячеек, поэтому можно воспользоваться длинными текстовыми именами, например, в А3 – ОбъёмЗаказа, в А4 – НеМеньше, в А5 – НеБольше, в А6 – Цена. В А2 введём текст СтоимостьЗаказа, в В2 – функцию ЕСЛИ со следующими аргументами:

Логическое_выражение: И(B3>=B4;B3<=B5)

Значение_если_истина: B3*B6

Значение_если_ложь: ТАКОЙ ЗАКАЗ НЕ ПРИНИМАЕМ

Если ячейкам В3:В6 предварительно присвоить имена, указанные в А3:А6 (см. п. 1.9), смысл аргументов станет более понятным.

Посмотрите, как действует шаблон, при разных значениях, введённых в В3. Отформатируйте его (см. п. 1.13):

• измените ширину столбца А так, чтобы все длинные тексты были видны на экране;
  
• выровняйте по левому краю значения переменных в столбце В;
  
• выделите заливкой или цветом шрифта ячейки А2:В2, А3:В3;
  
• отцентрируйте заголовок по ячейкам А1:В2.
  

Измените условие в первом аргументе:

ИЛИ(В3<=B4;B3>=B5)

В какой последовательности надо теперь перечислять способы заполнения ячейки В2?

Итог. Если выбор одного из двух вариантов заполнения ячейки зависит от нескольких условий, все они перечисляются через ";", заключаются в общие скобки и перед ними указывается нужный тип объединения:

• должны выполняться все одновременно – И;
  
• должно выполняться хотя бы одно из них – ИЛИ.
  

Пример 4.

Составить шаблон для расчёта подоходного налога по прогрессивной схеме.

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

налог=


Здесь d – доход, n1, n2 – границы налоговых ставок, р1, р2, р3 – процентные ставки налогов (очередной процент действует только на ту часть дохода, которая попала в его диапазон).

Введём в ячейку А1 общий заголовок шаблона: Расчёт налога. В А2:А7 введём соответственно названия Доход, Граница1, Граница2, Проц1, Проц2, Проц3. В В2:В7 введём значения этих величин, В С2 – текст: Налог, в D2 – функцию ЕСЛИ, первый и второй аргументы которой такие:

Логическое_выражение: B2<=B3

Значение_если_истина: B5*B2

В третьем аргументе надо объяснить, как заполнять ячейку, если доход больше первой границы. Так как для этого случая существует ещё две возможности: d n2 и d > n2, в этот аргумент вставляют дополнительную функцию ЕСЛИ, которая должна выбрать нужный вариант. Правила вставки дополнительной функции в аргумент основной изложены в п. 1.8. Окончательно третий аргумент основной функции (Значение_если_ложь) выглядит так:

ЕСЛИ(B2<=B4;B5*B3+B6*(B2-B3);B5*B3+B6*(B4-B3)+B7*(B2-B4))

Итог. Если для заполнения ячейки имеется от трёх до семи вариантов, все они вводятся через вложенные функции ЕСЛИ, вставленные в аргумент Значение_если_ложь предыдущей функции. Если вариантов больше семи, все они разбиваются на мелкие группы и проверяются функциями ЕСЛИ в разных ячейках.


3.4. Оценка определённого интеграла


Интеграл – это площадь под графиком подынтегральной функции. Один из самых простых способов оценки этой площади – метод трапеций. По этому методу промежуток интегрирования [a;b] разбивают на несколько (n) равных частей длиной h=(b-a)/n. Подынтегральную функцию f(x) заменяют на хорды, проведённые через её значения на концах каждого из полученных отрезков. После этого реальную площадь считают приближённо равной сумме площадей прямоугольных трапеций, основания которых – ординаты функции в точках дробления промежутка интегрирования (x_i, i=0, 1, 2, …, n) , а боковыми сторонами являются хорды, заменяющие функцию, и соответствующие отрезки на оси абсцисс. Математически площадь каждой трапеции выражается формулой

S_i= h(f(x_i-1)+f(x_i))/2

С учётом вышесказанного в Excel оценку интеграла можно выполнить следующим образом:

1. Составить таблицу значений подынтегральной функции (см. п. 3.1.).
   
2. Дополнить эту таблицу столбцом (строкой) с вычислением площадей элементарных трапеций.
   
3. Просуммировать эти площади.
   

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

Пример.

Составить таблицу значений интеграла как функции верхнего предела в диапазоне 0. Отразить эти данные на графике. Подобрать функцию тренда.

Составим план размещения информации:

Ячейки	Информация	Значение
А1	Заголовок расчёта	Определённый интеграл как функция верхнего предела
А2:D2	Название констант расчёта	А2: a, В2: b, С2: n, D2: h
А3:D3	Значения констант расчёта	А3: 0, В3: 5, С3: 20; D3: =(B3-A3)/2
А4:С4	Заголовки таблицы	А4: v, В4: f(v), C4: Si, D4: S(v)
A5:A25	Промежуточные значения верхнего предела	А5: =А2, А6: =А5+$D$3, A7:A25 заполняются протяжкой ячейки A6
B5:B25	Формулы подынтегральной функции	В5: =(2,2(A5+1)-3*A5)/(EXP(1,5*A5)+A5) В6:В25 заполняются протяжкой ячейки В5
С5:С25	Формулы площадей элементарных трапеций	С5: 0, С6: =$D$3*(B5+B6)/2 C7:C25 заполняются протяжкой ячейки C6
D5:D25	Значение интеграла от 0 до v	D5: 0, D6: =D5+C6 D7:D25 заполняются протяжкой ячейки D6

Задание.

Постройте диаграмму типа Точечная для зависимости интеграла от верхнего предела. Подберите тренд для него. Выведите уравнение тренда на диаграмму. Отформатируйте таблицу и диаграмму.


3.5. Нахождение корня уравнения


Помимо способа, изложенного в п. 3.1, для решения этой задачи можно воспользоваться командой СервисПодбор параметра… Перед обращением к этой команде следует ввести в таблицу алгоритм расчёта функции (он может быть представлен одной или несколькими формулами) и ввести в ячейку её аргумента ориентировочное значение, с которого следует начать поиск корня.

Команда СервисПодбор параметра… вызывает на экран окно Подбор параметра, в котором следует указать:

• адрес ячейки, в которой находится конечное значение функции;
  
• то число, к которому её надо приравнять;
  
• ячейку аргумента.
  

В процессе выполнения команды начальное значение аргумента заменится на найденное, при котором функция будет равна нужному значению (не обязательно нулю). Точность подбора аргумента и максимально допустимое количество итераций при решении задачи задаются в диалоговом окне команды СервисПараметры… на вкладке Вычисления.

Задание.

Найдите двумя способами с точностью 0,001 корень уравнения e^-0,5x-2x+4=3.


3.6. Решение систем уравнений


Для решения систем нелинейных уравнений можно использовать команду СервисПоиск решения…, преобразовав задачу в оптимизационную (см. п. 3.7).

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

АХ=В; Х=А^-1В

Здесь А – матрица коэффициентов при неизвестных, В – столбец свободных членов системы, Х – неизвестные решения, А^-1 – обратная матрица коэффициентов системы.

В библиотеке Мастера функций Excel в категории Математические есть функции МУМНОЖ и МОБР, которые выполняют соответственно умножение и обращение матриц, необходимое для решения данной задачи. Так как результатом работы этих функций являются массивы чисел, их следует вводить как функции массива (см. п. п. 1.7, 1.8).

Рассмотрим систему четырёх линейных уравнений с четырьмя неизвестными. Введём исходные данные задачи по представленному ниже плану.

Ячейки	Информация	Значение
А1	Заголовок расчёта	Решение системы линейных уравнений
А4	Общий заголовок строк	№ уравнения
В4:В7	Номера строк	1; 2; 3; 4
С2	Общий заголовок столбцов	№ переменной
С3:F3	Номера переменных	1; 2; 3; 4
C4:F7	Коэффициенты при неизвестных системы	Любые числа
G2	Заголовок	Свободные члены
G4:G7	Свободные члены уравнений	Любые числа
H2	Заголовок	Решение системы
H4:H7	Формула массива	{=МУМНОЖ(МОБР(C4:F7);G4:G7)}
I2	Заголовок	Проверка
I4:I7	Формула массива	{=МУМНОЖ(C4:F7;H4:H7)}

Для удобства работы перед вводом коэффициентов системы и расчётных формул можно провести форматирование таблицы (см. п. 1.13):

• объединить ячейки, в которых размещены заголовки;
  
• разместить эти заголовки по центру объединённых ячеек;
  
• изменить направление текста в заголовке А4:А7 на вертикальное;
  
• разрешить перенос по словам в заголовках G2:G3, H2:H3, I2:I3;
  
• разделить тонкими линиями столбцы полученной таблицы;
  
• обвести жирной рамкой всю таблицу в целом и блоки заголовков (A2:B7 и A2:I3).
  

Перед вводом формулы массива следует выделить ячейки, в которых надо разместить результаты. При решении системы это блок Н4:Н7, при проверке правильности найденного решения – I4:I7. Затем формула набирается обычным способом с помощью Мастера функций, но ввод заканчивается нажатием клавиши или кнопки <ОК> при дополнительно утопленных клавишах . При вводе формула массива автоматически заключается в фигурные скобки.


3.7. Решение задач оптимизации


Команда СервисПоиск решения… предоставляет пользователю следующие возможности:

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

Эти возможности реализуются с помощью параметров, собранных в основном окне Поиск решения и дополнительном Параметры поиска решения. Дополнительное окно вызывается кнопкой <Параметры> из основного. Кнопка <Справка> вызывает окно с разъяснением смысла каждого параметра и возможностей, которые предоставляются при его заказе.

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

f₁(x₁,x₂…,x_n)=0; f₂(x₁,x₂…,x_n)=0; …; f_n(x₁,x₂…,x_n)=0;

составляют вспомогательную целевую функцию

S=f₁²+ f₂²+…+ f_n²

S – неотрицательная функция, её минимальное значение равно нулю и достигается только тогда, когда все слагаемые одновременно равны нулю. А это и есть решения исходной задачи.

Рассмотрим в качестве примера систему двух нелинейных уравнений

x²+y²=3; 2x+3y=1

Введём исходные данные задачи по представленному ниже плану.

Для удобства дальнейшей работы можно провести форматирование созданной таблицы, аналогичное п. 3.5.

Вызовем команду СервисПоиск решения… В окне Поиск решения установим следующие параметры:

• "Установить целевую ячейку:" А9
  
• "Равной:" минимальному значению
  
• "Изменяя ячейки:" А4:В4
  
• Нажмём кнопку <Параметры> и в дополнительном окне Параметры поиска решения проверим, что флажок Линейная модель не установлен. Закроем дополнительное окно кнопкой <ОК>
  
• Запустим команду кнопкой <Выполнить> основного окна.
  

Когда команда закончит работу, на экране автоматически появляется окно Результаты поиска решения. Пояснения к параметрам, представленным в нём, вызываются кнопкой <Справка>. Закажем, к примеру, параметры "Сохранить найденное решение" и "Тип отчёта: результаты". В этом случае начальные значения переменных в ячейках А4:В4 заменятся на найденные и в таблицу будет вставлен новый лист "Отчёт по результатам 1". Просмотрите отчёт. Проверьте, какое значение приняла вспомогательная целевая функция в А9 при найденных решениях. Если она существенно отличается от нуля, то решение найдено неверно.

Ячейки	Информация	Значение
А1	Заголовок расчёта	Решение системы нелинейных уравнений
А2	Заголовок	Переменные
А3:В3	Название переменных	А3: Х, В3: Y
А4:В4	Начальные значения переменных	А3: 1, В3: -1
А5	Заголовок	Функции системы
А6:В6	Названия функций системы	А6: f1, B6: f2
А7:В7	Формулы для расчёта функций	=A42+B42-3 =2*A4+3*B4-1
А8	Заголовок	Вспомогательная целевая функция
А9	Формула целевой функции	=A72+B72

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


Задание.

Составьте таблицу значений целевой функции S=(x²+y²-3)²+(2x+3y-1)² в диапазоне аргументов -3, -3. Выберите 4 – 5 точек с наименьшими значениями функции, проведите поиск решения из каждой из них. В результате должно быть получено только два разных решения: х₁=-1,268; у₁=1,179 и х₂=1, 576; у₂=-0,717. Графически уравнения системы представляются окружностью и прямой линией. Система такого типа не может иметь больше двух точек пересечения.


4. ИСПОЛЬЗОВАНИЕ VISUAL BASIC В EXCEL


4.1. Работа с макросами


Познакомимся с таким средством работы в Excel, как "макросы".

Выберем раздел меню СервисМакросНачало записи... Появится окно Запись макроса. Вы можете ввести или изменить информацию в полях этого окна, в частности, назначить свое имя или "горячую клавишу" для запуска макроса. Оставьте содержание полей неизменным или, если хотите, измените их и нажмите кнопку <ОК>. Обратите внимание на появление небольшой панели с кнопкой <Остановить запись> на листе книги Excel. Выполните любую последовательность действий на листе, например, закажите для выделенных ячеек более крупный и цветной шрифт и введите туда формулу. После этого нажмите кнопку <Остановить запись> (надо именно нажимать кнопку, а не закрывать панель).

Сотрите все результаты выполненных вами действий при записи макроса. Далее выберем раздел меню СервисМакросМакросы…. В появившемся окне выделим имя того макроса, который Вы только что записали, и нажмем кнопку <Выполнить>. Убедитесь что последовательность действий, проделанных Вами при записи макроса, оказалась выполненной заново. Вы можете опять стереть результаты этих действий или выделить другие ячейки и опять заказать выполнение макроса. Все действия, сделанные при записи макроса, повторятся автоматически. При этом, если в макрос входят формулы, которые содержат ссылки на другие ячейки , и он выполняется не на прежнем месте, то ссылки автоматически будут преобразованы в соответствии с правилами копирования.

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


Примечания.

• Не забывайте останавливать запись макроса сразу после завершения нужной Вам последовательности действий.
  
• Ввод формулы следует заканчивать нажатием кнопки в Строке формул, а не клавиши .
  
• Если Вы при записи макроса назначили ему «горячее» сочетание клавиш, например , или другое, выполнить соответствующий макрос можно без выхода в меню, а просто нажав это сочетание клавиш. Проверьте это на практике.
  

Задание.

Запишите макрос, выполняющий следующую последовательность действий:

• поставьте курсор в ячейку А2 и введите число "1";
  
• в ячейку А3 введите число "1,5";
  
• протяните этот ряд до значения "6";
  
• в ячейку В2 введите формулу "=SIN(А2)+А2:1,5" ( не забудьте, что заканчивать формулу надо кнопкой < Enter > в Строке формул) ;
  
• протяните формулу из В2 до В12;
  
• округлите значения в В2:В12 до двух цифр после запятой;
  
• обведите рамками ячейки А1:В12 и А1:В1 и проведите линию между А1:А12 и В1:В12:
  
• поставьте курсор в А1.
  

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


5. ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ

1. Известны доходы сотрудников фирмы. Представить эти данные в виде таблицы и гистограммы, найти минимальный, максимальный и средний доход. Выделить сотрудников, средний месячный доход которых меньше 5 минимальных зарплат. Подсчитать количество таких сотрудников.
   
2. Известна ежедневная выручка филиалов магазина в течение месяца. Представить эти данные в виде таблицы и накопленной гистограммы. Найти выручку каждого филиала и общую выручку магазина за этот месяц. Выделить филиалы с максимальной и минимальной месячной выручкой, дни с максимальной и минимальной выручкой.
   
3. Известны месячные коэффициенты инфляции в течение года. Представить эти данные в виде таблицы и графика. Найти общую и среднюю инфляцию за год.
   
4. Имеется таблица нормативной и реальной загрузки вагонов, сцепляемых в состав. Представить эти данные в виде таблицы и гистограммы. На гистограмме отразить процентное отношение веса груза по отношению к нормативу. Найти общий вес груза в составе, отметить номера недогруженных вагонов и рассчитать недобор груза в них в обычных единицах и процентах от норматива.
   
5. Имеются сведения об успеваемости студентов по разным дисциплинам. Представить эти данные в виде таблицы и накопленной гистограммы (по оси Х – дисциплина, по оси У – средний балл по ней). Определить средний балл каждого студента и группы в целом. Выделить студентов, имеющих "2" по какой-нибудь дисциплине.
   
6. Для аргументов, изменяющихся от а-3 до а+3 с шагом , составить таблицу значений плотности распределения нормального закона, которая задаётся функцией
   

.

Представить эти данные в виде точечной диаграммы плавной линией. Предусмотреть возможность изменения параметров

7. Для аргументов x от до с шагом и