Информатика. Excel 2000 (основные приемы работы): Учебное пособие

Министерство образования Российской Федерации Государственное образовательное учреждение высшего профессионального образования СЕВЕРО-ЗАПАДНЫЙ ГОСУДАРСТВЕННЫЙ ЗАОЧНЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ

Н.М. Петухова, Е.О. Петухова

Информатика Excel 2000 Основные приемы работы Учебное пособие

Санкт-Петербург 2003

Утверждено редакционно-издательским советом университета УДК 881.3 Петухова Н.М., Петухова Е.О., Excel 2000 (основные приемы работы): Учеб. пособие. – СПб.: СЗТУ, 2003. – 182 с. Пособие написано в соответствии с требованиями государственных образовательных стандартов высшего профессионального образования по направлениям и специальностям подготовки дипломированного специалиста: 060800 – «Экономика и управление на предприятии (по отраслям)», 653300 – «Эксплуатация наземного транспорта» (специальность 150200 – «Автомобили и автомобильное хозяйство»), 653400 – «Организация перевозок и управление на транспорте» (специальность 240100 – «Организация перевозок и управление на транспорте (автомобильном)») и направлениям подготовки бакалавра: 521500 – «Менеджмент», 551400 – «Наземные транспортные системы». Пособие предназначено для студентов первого курса всех форм обучения и посвящено изложению одного из разделов дисциплины «Информатика» – основам работы в электронной таблице Excel .

Рецензенты: кафедра информатики и вычислительной математики СЗТУ (зав. кафедрой Г.Г. Ткаченко, канд. физ.-мат. наук, доц., Л.В. Боброва, канд. техн. наук, доц.), Ю.Н. Сидоров, канд. техн. наук, доцент кафедры математического обеспечения и применения ЭВМ СПбГЭТУ (ЛЭТИ), канд. техн. наук, ОАО Н.А. Смирнова «Ленстройматериалы» © Северо-Западный государственный заочный технический университет, 2003 © Петухова Н.М., Петухова Е.О., 2003

Ввдение Электронная таблица Excel для Windows корпорации Microsoft входит в состав знаменитого пакета Microsoft Оffice. С момента своего появления в 1985 году она завоевала репутацию наиболее мощной и удобной электронной таблицы и до настоящего времени занимает ведущее положение на мировом рынке. Excel считается одной из лучших разработок фирмы Microsoft. В России Excel является, по существу, единственной реально используемой электронной таблицей. Благодаря мощным возможностям и удобному интерфейсу электронные таблицы применяются в самых различных областях нашей деятельности. Работа в среде Excel не требует квалификации программиста, поэтому его пользователями являются инженеры, научные работники, экономисты, руководители любого уровня, менеджеры и все, кому приходится производить какие-либо вычисления. Именно поэтому знакомство с Excel, как и с другими Windows – приложениями, входящими в состав пакета Microsoft Оffice, является составной частью общеобразовательного курса Информатики. Учебное пособие предназначено для студентов, которые впервые знакомятся с Microsoft Excel, но имеют хотя бы первоначальный опыт работы на персональном компьютере в Windows 2000 (98, 95). Целью пособия является ознакомление с основными возможностями Excel и приобретение навыков самостоятельного решения практических задач. При желании глубже изучить этот замечательный программный продукт следует обратиться к специальной литературе, которая в большом количестве имеется на книжном рынке. Учебное пособие следует прочитать не один раз, так как с одного раза усвоить все невозможно. Читать пособие целесообразно, сидя за компьютером, выполняя все примеры, упражнения и задания для самостоятельной работы, ничего не пропуская. Без этого чтение принесет мало пользы. Чем больше будет таких занятий, тем быстрее вы освоите возможности этого замечательного пакета. Умение ставить задачи и реализовывать их решение средствами Excel поможет вам при выполнении лабораторных и курсовых работ, а так же в вашей будущей самостоятельной деятельности.

Глава 1. Основные технологические операции, производимые с данными 1.1. Выделение Табличный процессор представляет собой инструмент, с помощью которого можно производить различные действия над объектами электронной таблицы (ячейками, блоками ячеек, листами). Основные технологические операции, производимые с ячейками и блоками ячеек – это заполнение (ввод данных), перемещение, копирование, вставка, удаление. Чтобы выполнить какое-либо действие над объектом ЭТ, его надо сначала выделить. Выделение является важнейшим действием, производимым с объектами таблицы. Выполнение какого-либо действия над объектом Excel осуществляется только после его выделения. Выделенная ячейка называется текущей (активной) ячейкой. Выделение ячейки осуществляется переходом в нее с помощью клавиатуры или щелчком по ней левой клавишей мыши. Ячейка окажется выделенной жирной линией с маленькой квадратной точкой в ее правом нижнем углу, называемом маркером заполнения. Выделение строки произойдет, если щелкнуть левой клавишей мыши по имени (заголовку) строки. Вся строка окрасится в темный цвет, кроме первой ячейки, которая становится активной. Выделение столбца произойдет, если щелкнуть левой клавишей мыши по его имени (заголовку) столбца. Весь столбец окрасится в темный цвет, кроме первой ячейки, которая становится активной. Выделение всего листа таблицы осуществляется щелчком левой кнопкой мыши по кнопке Выделить все, находящейся в левом верхнем углу таблицы, или, нажав одновременно кнопки Ctrl+A (регистр En). Выделение блока ячеек можно осуществить двумя способами. 1 способ: установить указатель мыши на ячейку, находящуюся в одном из углов блока; нажать левую кнопку мыши и, удерживая ее нажатой, перетащить в противоположный угол блока. Курсор при этом должен сохранять вид белого перекрестия. отпустить левую кнопку мыши. Блок будет выделен целиком. 2 способ: щелкнуть левой клавишей мыши в одном из углов блока; нажать кнопку Shift и, не отпуская ее, щелкнуть левой клавишей мыши в противоположном углу блока. Блок будет выделен целиком.

Выделение нескольких блоков ячеек можно выполнить следующим образом: Выделить первый блок (любым способом). Нажать и, удерживая нажатой клавишу Ctrl, выделить остальные блоки. Отпустить Ctrl. Для отмены любого выделения необходимо щелкнуть в любом свободном месте таблицы. 1.2. Ввод и редактирование Ввод данных может осуществляться как в текущую ячейку, так и в строку формул. Чтобы ввести число, надо выделить ячейку, набрать число, текст или формулу (курсор будет иметь вид вертикальной черты), нажать клавишу Enter.

Рис. 13 Набираемое число будет отображаться как в текущей ячейке, так и в строке формул (рис. 13). Отменить ввод можно нажатием кнопки Esc. По умолчанию при вводе после нажатия клавиши Enter курсор перейдет на строку вниз (или вправо в зависимости от настройки). Если при нажатии Enter курсор переходит вниз, а ввод требуется производить вправо по строке, такое перемещение курсора оказывается неудобным. Тогда для завершения ввода следует нажимать не клавишу Enter, а клавишу со стрелкой (вправо). Ввод считается незаконченным, если курсор не покинул ячейку, в которую производился ввод. Другое перемещение активной ячейки можно задать, выбрав команду:

Сервис – Параметры – вкладка Правка – установить флажок Переход к другой ячейке после ввода, в направлении – в поле ввода раскрыть список и выбрать нужное перемещение. Для редактирования данных, записанных в ячейку, необходимо: активизировать эту ячейку; нажать клавишу F2 или дважды щелкнуть по ней мышью; таблица перейдет в режим редактирования (в ячейке появится курсор ввода в виде вертикальной черты); исправить данные или ввести новые; нажать Enter или клавишу со стрелкой. Редактирование или ввод данных можно производить как в текущей ячейке, так в строке формул. Ввод числовых данных Если вводимое число начинается со знака «+», Excel «+» опускает. Знак «-» сохраняется при вводе, а введенное число считается отрицательным. В соответствии с распространенной практикой финансовых отчетов числа в круглых скобках считаются отрицательными: (100) = -100 . Если вводимое число больше 11 разрядов, оно преобразуется в научный формат. Например, если ввели число 123456789123 и нажали клавишу Enter, на экране в ячейке будет записано число 1,23457E+11, что означает 1, 23457*10+11. Для представления десятичных чисел в русской версии используется десятичный разделитель – запятая «,», а разделитель разрядов – пробел (в оригинальной версии – точка и запятая соответственно). Вводимые числа могут быть представлены в различных форматах, но в некоторых случаях формат вводимого числа устанавливается автоматически в соответствии с тем, что было введено. Например, если после ввода числа введена буква р, ячейке автоматически присвоится денежный формат (рубли). Если введенное число заканчивается знаком «%», то ячейке присвоится процентный формат. Аналогично со знаком «$». Целые числа можно вводить и в виде обыкновенных дробей, используя «/». Например, если ввести ¼ и нажать Enter, в ячейку будет записано число 0,25 (если ввод осуществлен в числовом формате), а если использовать, например, общий формат, будет напечатано 01апр, а в строке формул 01.04.2000. Подробнее о форматах чисел написано в разделе Основные приемы форматирования ячеек. Значения, которые видны в ячейке, называются видимыми значениями, а значения, хранящиеся в ячейке и в строке формул, – фактическими. Количество видимых цифр в ячейке зависит от ширины столбца. Если уменьшить ширину

столбца, который содержит длинные цифровые данные, то в зависимости от используемого формата число будет изображено или в научном формате, или ячейка будет заполнена строкой #####. В этом случае для правильного отображения числа нужно увеличить ширину столбца или изменить формат числа. Об изменении ширины столбца написано в разделе Ручная подгонка ширины столбца (стр. 25). Ввод текстовых данных Текстовыми считаются данные, которые не распознаются как данные другого типа. Если числа входят в состав текста, то такие данные воспринимаются как текст. Текстовыми данными могут быть и просто числа, если они вводятся в кавычках. Например, если ввести число 234, заключив его в кавычки, т.е. “234”, оно будет воспринятой Excel как текст. В одну ячейку можно ввести до 32000 знаков. Если текстовые данные не умещаются в ячейке, они могут перекрывать содержимое ячеек слева или справа в зависимости от выравнивания. Тем не менее, текст принадлежит только одной ячейке. Если ввести какие-либо данные только в одну из перекрытых ячеек, перекрывавший ее текст будет казаться обрезанным. Чтобы показать перекрытый текст, нужно увеличить ширину столбца. Текст может переноситься на следующую строку внутри ячейки, если выполнить команду Формат – Ячейка – вкладка Выравнивание – флажок Переносить по складам. Чтобы избежать случайного разбиения на строки, следует в месте желаемого переноса одновременно нажать клавиши Alt+Enter. Ввод даты и времени Для ввода даты или времени в ячейку ЭТ надо выделить ячейку и ввести дату, используя один из предлагаемых Excel форматов. Для ознакомления с форматами, которыми можно пользоваться в Excel, следует: выбрать команду Формат – Ячейки. Появится диалоговое окно Формат ячеек. В списке Числовые форматы выбрать Дата (или Время), в поле Тип ознакомиться с предлагаемыми форматами. Для ввода текущей даты в ячейку или в формулу надо, удерживая нажатой клавишу Ctrl, нажать клавишу с символом точка с запятой (на этой же клавише находится буква «ж»). Дата введется в формате Д.ММ.ГГГГ. Например, 10.05.2003. Для ввода текущего времени в ячейку или в формулу надо, одновременно удерживая нажатыми клавиши Ctrl+Shift, нажать клавишу с символом

«двоеточие» (на этой же клавише находится буква «ж»). Дата введется в формате Время, типа11:30 24 -часового стиля. Например, 23:35. По существу, типа Дата в Excel не существует. Для представления дат используются обычные числа, отображенные в формате дат. Целая часть числа соответствует дням, месяцам, годам, а дробная – часам, годам, минутам, секундам. Основная единица времени в Excel – один день. Каждый день имеет порядковый номер, начиная с 1, которой соответствует 1 января 1900 г (начало отсчета дат в Excel). Такой способ хранения дат позволяет обрабатывать их как обычные числа, например, находить дату, отстоящую от любой другой даты на желаемое число дней в будущем или прошлом, находить промежуток времени между двумя датами и т.д. Такое сопоставление чисел датам хранится во внутренних таблицах. Форматы даты позволяют отображать их в одном из привычных видов Ввод данных в блок ячеек Ввод данных можно производить не только в текущую ячейку, но и в блок ячеек. Для ввода данных в блок ячеек надо: выделить блок ячеек, ввести очередное число, нажать Enter. Ввод чисел осуществляется до тех пор, пока не будут заполнены все ячейки блока. После ввода числа в последнюю ячейку блока, активной снова станет первая ячейка блока. Пусть надо заполнить числами 10,20, …, 90 блок ячеек А1:С3. Для этого следует: выделить блок ячеек А1:С3, установить курсор в ячейку А1, набрать число10, нажать Enter. Курсор перейдет в ячейку В1, набрать число 20, нажать Enter. Курсор перейдет в ячейку С1, набрать число 30 (рис.14), и т.д.

Рис.14 Множественный ввод Часто возникает необходимость ввести во все ячейки блока или во все выделенные несмежные ячейки одинаковые данные. Для этого следует ввести в одну текущую ячейку данные и одновременно нажать клавиши Ctrl+Enter. Содержимое клетки будет автоматически повторено во всех ячейках выделенных областей. 1.3. Копирование и перенос В Excel, как и в остальных приложениях Windows, копирование и перемещение данных осуществляется через буфер информационного обмена. Копировать можно как содержимое одной ячейки, так и блока ячеек. Копирование можно производить или на тот же рабочий лист, или на другой рабочий лист, или в другую рабочую книгу. Перед выполнением этих операций данные, подлежащие копированию или перемещению, должны быть выделены. Копирование с помощью главного меню Выделить копируемый объект. Выполнить команду Правка – Копировать. Установит курсор в первую ячейку блока, куда копируется объект. Выполнить команду Правка – Вставить. Копия объекта появится в нужном месте. Убрать выделение, щелкнув левой кнопкой мыши в свободном месте рабочего поля. Убрать пунктир в источнике копирования, нажав Esc. Копирование с помощью панели инструментов Выделить копируемый объект.

Щелкнуть левой кнопкой мыши по кнопке Копировать . Выделенный объект будет в двойной подвижной рамке. Щелкнуть левой кнопкой мыши в левом верхнем углу нового положения копируемого объекта. Щелкнуть левой кнопкой мыши по кнопке Вставить . Копия объекта появится в нужном месте. Убрать выделение, щелкнув левой кнопкой мыши в свободном месте рабочего поля. Убрать пунктир в источнике копирования, нажав Esc. Перенос с помощью главного меню Выделить переносимый объект. Выполнить команду Правка – Вырезать. Установит курсор в первую ячейку блока, куда переносится объект. Выполнить команду Правка – Вставить. Копия объекта появится в нужном месте. Перенос с помощью панели инструментов Выделить объект, который следует перенести. Щелкнуть левой кнопкой мыши по кнопке Вырезать . Щелкнуть левой кнопкой мыши по кнопке в левом верхнем углу нового положения переносимого объекта. Щелкнуть левой кнопкой мыши по кнопке Вставить . Объект появится в новом месте и исчезнет из старого места. Копирование с помощью мыши Выделить копируемый объект. Подвести курсор к границе объекта. Курсор примет вид стрелки. Нажать клавишу Ctrl. К стрелке курсора добавится +. Нажать левую кнопку мыши и, удерживая нажатой Ctrl и левую кнопку мыши, перетащить копируемый объект в нужное место. Отпустить левую кнопку мыши. Скопированный объект появится в новом месте. Отпустить Ctrl.

Убрать выделение. Перенос с помощью мыши Выделить переносимый объект. Подвести курсор к границе объекта. Курсор примет вид стрелки. Нажать левую кнопку мыши и перетащить переносимый объект в нужное место. Отпустить левую кнопку мыши. Пусть надо переместить содержимое блока ячеек А1:В4 в блок ячеек С1:С4. Для этого: Поместите указатель мыши на границу блока (справа). Он превратится в стрелку (рис. 15). Нажмите и перетащите отделившийся контур в блок ячеек С1:С4. Отпустите левую кнопку мыши. Выделенные ячейки будут перемещены в нужное место, а столбец А1:А4 окажется пустым (рис. 16).

Рис. 15

Рис. 16

Пусть надо скопировать содержимое блока ячеек С1:С4 в блок ячеек В1:В4. Для этого:

Подведите курсор к границе блока С1:С4 (слева). Курсор примет вид стрелки (рис. 17).

Рис.17

Рис.18

Нажмите клавишу Ctrl. К стрелке курсора добавится +. Нажмите левую кнопку мыши и, удерживая нажатой Ctrl и левую кнопку мыши, перетащите копируемый объект в блок ячеек В1:В4. Отпустите левую кнопку мыши. Скопированный объект появится в новом месте. Отпустите Ctrl. Уберите выделение, нажав Esc. Данные будут и в ячейках блока С1:С4, и в ячейках блока В1:В4 (рис.18). Копирование (перемещение) данных на другие листы выполняется аналогичным образом, но при нажатой кнопке Alt. Для перемещения выделенного блока надо перетащить его с помощью мыши на вкладку нужного листа (при этом лист откроется) и установить на нужное место. При перемещении данных надо выполнить те же действия, но еще нажать кнопку Ctrl. 1.4. Вставка Вставка блока ячеек между другими ячейками

С помощью клавиши Shift можно переместить выделенный блок и вставить его между другими ячейками (рис.19, 20). Для этого в процессе протаскивания следует нажать клавишу Shift. Тогда контур области превратится в разделитель, напоминающий латинскую букву I, вытянутую по вертикали или горизонтали. Этот указатель требуется установить между ячейками, куда предполагается вставить выделенный блок. Затем отпустить кнопку мыши и Shift. Комбинация клавиш Ctrl + Shift позволяет копировать, а не перемещать блок ячеек.

Рис. 19

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

Щелкнуть правой кнопкой мыши по заголовку строки, перед которой надо вставить строку, (или по заголовку столбца, слева от которого надо вставить столбец). В появившемся меню выбрать пункт Вставить. Очистка и удаление Очистка освобождает ячейки или блок ячеек от находящихся в нем данных. Для очистки ячейки или блока следует выделить их и нажать кнопку Del. При очистке ячейки не исчезают, а только лишаются своего содержания. При этом не происходит освобождение ячеек от установленных в них форматов и элементов оформления (рамок, заливки и т.д.). Чтобы вместе с данными убрать и все оформление, надо выбрать команду Правка – Очистить и в открывшемся вспомогательном меню выбрать пункт Все. Удаление означает физическое удаление самого объекта (строки, столбца, блока и т.д.). При удалении объекта их место заполняют ячейки, расположенные рядом. Чтобы удалить объект надо: Выделить объект. Выбрать команду Правка – Удалить. Появится диалоговое окно Удаление ячейки (рис. 21). Указать, куда надо сдвинуть фрагменты таблицы после удаления объекта. Для удаления объекта можно использовать контекстное меню. Для этого следует: Выделить объект. Вызвать контекстное меню. Выбрать пункт Удалить. В диалоговом окне Удаление ячейки выбрать, куда надо сдвинуть фрагмент таблицы после удаления объекта.

Рис. 21

1.6. Изменение ширины столбцов Ручная подгонка ширины столбца Для установки нужной ширины столбца следует: Установить курсор на границе столбцов в области заголовка (на вертикальной линии, отделяющей клетки заголовков). Курсор примет вид двунаправленной стрелки. Нажать левую кнопку мыши и перетащить границу столбца на нужное расстояние. Этот способ используется для индивидуальной подгонки ширины столбца. Задание минимальной ширины столбцов Для задания минимально возможной ширины столбцов надо: Выделить заголовки нужных столбцов. Установить курсор на границе любых столбцов внутри выделенного фрагмента. Дважды щелкнуть мышью. Столбцы, не содержащие данные, останутся без изменения. 1.7. Использование маркера для копирования и заполнения

Использование маркера для копирования Этот способ копирования используется для копирования содержимого одной ячейки в несколько смежных ячеек. Для копирования надо: Выделить ячейку, содержимое которой следует скопировать. Установить курсор в правый нижний угол выделенной ячейки на маркер копирования. Курсор приобретет вид черного перекрестия. Нажать левую кнопку мыши и, не отпуская ее, переместить курсор в ячейки копирования. Ячейки будут обведены серой рамочкой. Отпустить кнопку мыши. Использование маркера для вставки пустого столбца Пусть заполнен блок ячеек А1:С2. Надо вставить столбец между заполненными столбцами А и В (рис. 22, 23). Для этого следует: Выделить блок ячеек А1:А2 (или весь столбец А). Нажать левую кнопку мыши на маркере заполнения.

Рис. 22

Рис. 23

Нажать и, удерживая нажатой кнопку Shift, перетащить отделившийся контур до тех пор, пока он не охватит столбец В. Отпустить кнопку мыши, а затем кнопку Shift. Аналогично можно вставлять пустые строки. Использование маркера для удаления пустого столбца Для удаления пустого столбца надо: Выделить столбец, который требуется удалить. Нажать кнопку Shift и удерживать ее нажатой. Нажать левую кнопку мыши на маркере и перетащить его на один столбец влево.

Как только черное обрамление столбца изменится на серое, отпустить кнопку мыши и Shift. Выделенный столбец удалится. Для удаления выделенного столбца можно воспользоваться командой Правка – Удалить. Задания 1. 2. 3. 4.

Выделите ячейку В5. Отмените выделение. Выделите строку 7. Отмените выделение. Выделите столбец В. Отмените выделение. Выделите блок ячеек В2:Е8 с помощью протаскивания. Отмените выделение. 5. Выделите блок ячеек А2:К4 с помощью клавиши Shift. Отмените выделение. 6. Выделите одновременно блоки А3:В4, С1:D4, A6:D8. Отмените выделение. 7. Выделите одновременно столбцы А, С, Е. Отмените выделение. 8. Выделите одновременно строки 2, 4, 6. Отмените выделение. 9. Введите в блоки ячеек А1:С1 и А4:С7 какие-нибудь числа. Переместите содержимое блока ячеек А1:С1 в блок ячеек А4:С7 (между строками 5 и 6). Отмените выполненное действие. 10.зЗаполните блок ячеек А1:С2 числами. Вставьте столбец между заполненными столбцами А и В. Удалите столбец, который был вставлен в предыдущем задании.

1.8. Создание и заполнение рядов Использование маркера заполнения Создать ряд можно с помощью маркера заполнения (автозаполнением). Пусть требуется заполнить блок ячеек А1:А5 числами 25. Для этого надо: Ввести в ячейку А1 число 25. Установить курсор на маркере заполнения. Перетащить маркер заполнения вниз до конца блока ячеек А1:А5 (рис. 24). Если переместить маркер заполнения в обратном порядке (до А1), содержимое скопированных ячеек удалится.

Рис. 24 Пусть требуется заполнить блок ячеек А3:D3 числами 17. Для этого надо: Ввести в ячейку А3 число 17. Установить курсор на маркер Протащить его направо до ячейки D3 включительно. Блок ячеек А3: D3 будет заполнен числами 17 (рис. 25).

Рис. 25 Пусть в блок ячеек А1:А2 введены числа 10, 20 и в блок ячеек С1:С2 числа 10, 20; а в блок ячеек Е5:Е6 числа 50, 60. Если выделить блок ячеек А1:А2 и протащить маркер заполнения вниз, в столбце будет создана арифметическая прогрессия.

Рис. 26 Если выделить блок С1:С2, нажать кнопку Ctrl и перетащить маркер вниз – будет другое заполнение. Если выделить блок ячеек Е5:Е6 и протащить маркер вверх – получится убывающая прогрессия (рис. 26). Если выделенные ячейки содержат текстовые данные, то перетаскиванием маркера будет просто скопирован текст во все ячейки (рис. 27).

Рис. 27 Если же выделенная ячейка содержит и текстовые, и числовые данные, то будут скопированы текстовые данные и распространены числовые (рис. 28).

Рис. 28 Если ввести в одну ячейку день недели или название месяца (рис. 29) и протащить их маркером в несколько ячеек по вертикали или по горизонтали, получим (рис. 30):

Рис. 29

Рис. 30 Создание рядов дат Для создания рядов дат следует: Ввести начальную дату в первую ячейку блока, в следующую ячейку этого блока - вторую дату создаваемого ряда. Выделить с помощью мыши эти две ячейки (рис. 31).

Поместить указатель мыши на маркер выделения. Указатель примет вид перекрестия. Нажать кнопку мыши и перетащить маркер выделения так, чтобы интервал ячеек для создаваемого ряда оказался внутри контура выделения.

Рис. 31 Отпустить кнопку мыши. На основании двух значений даты в начальных ячейках интервала Excel найдет шаг изменения даты и построит нужный ряд (рис. 32).

Рис. 32 Использование маркера с правой кнопкой мыши Для выбора способа заполнения блока данными можно перетаскивать маркер с помощью правой кнопки мыши. Отпустив ее, появится контекстное меню (рис. 33). Выбрать в нем нужный пункт.

Рис. 33 Создание ряда с помощью главного меню Создать или заполнить ряд можно с помощью команды Правка – Заполнить – Прогрессия (рис. 34). Для этого надо выделить блок ячеек по строке или по столбцу, в первую ячейку блока ввести первое число и выбрать приведенную выше команду. В диалоговом окне Прогрессия выбрать тип прогрессии, шаг и расположение чисел и нажать кнопку ОК (рис.35).

Рис. 34

Рис. 35 Создание пользовательских списков Если требуется периодически вводить в листы Excel повторяющиеся данные, такие как списки имен или названий, можно создать пользовательские списки. Для создания таких списков необходимо: Выбрать команду Сервис – Параметры – Списки.

В окне Списки выбрать Новый список (рис. 36).

Рис. 36 В поле Элементы списка набрать столбиком содержимое списка в нужном порядке (рис. 37). Нажать ОК. После того, как такой список создан, для его ввода достаточно будет набрать в начальной ячейке блока нужный элемент списка и протащить маркер заполнения по всему блоку (рис.38).

Рис. 37

Рис. 38 Импорт пользовательских списков Пользовательский список можно создать с помощью уже существующих данных на листе Excel. Для импортирования данных нужно: Выделить интервал ячеек с этими данными. Выбрать команду Сервис – Параметры – Списки.

Щелкнуть по кнопке Импорт (рис. 39). Выделенные данные составят новый список. Нажать ОК.

Рис. 39 Задания 1. Заполните блок ячеек А3:А20 числами 1,2,3, … , воспользовавшись маркером заполнения. 2. Заполните блок ячеек В2:В6 данными 2000, 2001, 2002, 2003, 2004, с помощью окна Прогрессия. 3. Заполните блок ячеек С2:С5 данными Квартал 1, Квартал 2, Квартал 3, Квартал 4. 4. Заполните блок ячеек С4:С16 датами 09.01.2003, 16.01.2003 … . 5. Заполните блок ячеек D2:D9 названиями дней недели Пн, Вт, Ср, Чт, Пт, Сб, Воскр, с помощью маркера заполнения. 6. Создайте пользовательский список из 12 фамилий. Импортируйте их в ячейки Е1:Е12.

Глава 2. Создание формул Вычисления в таблицах Excel осуществляются при помощи формул. Любые формулы начинаются со знака равенства (=). Без знака равенства вводимые данные, если они не представляют собой числа, воспринимаются как текст. В формулу могут входить числовые константы, операторы, адреса ячеек и функции. Рассмотрим операторы, используемые в Excel. 2.1. Математические операторы % – операция вычисления процента (применима к одному числу), ^ – возведение в степень, * / – умножение, деление, + - – сложение, вычитание. Операторы перечислены в соответствии с приоритетом операций. Операторы с одинаковым приоритетом записаны в одну строчку. Формулы записываются в одну строку. Вычисления по формуле производятся в соответствии с приоритетом операций. Последовательные операции одного и того же приоритета выполняются слева направо. Для изменения порядка выполнения операций используются круглые скобки. В формуле каждой открывающей скобке (левой) должна соответствовать закрывающая скобка (правая). Если вы забудете закрыть какую-либо из открывающих скобок, будет выведено сообщение «Несоответствие скобок» и подсвечена недостающая часть. Рассмотрим примеры записи формул в Excel: Формулы в математической записи

Формулы в ячейках Excel

A3 B3 + 10 + D 4 − D5 F 6 − F 7

=A3/(D4-D5)+(B3+10)/(F6-F7)

Sin2C1-1

=(SIN(C1))^2-1

Примечание: Если в ячейку ввести число со знаком %, то его значение будет в 100 раз меньше. Например, если ввести 15%, то будет запомнено число 0,15. Такое действие эквивалентно установлению процентного формата (см. раздел «Основные приемы форматирования ячеек»). Число со знаком % может быть введено в формулу, например, необходимо вычислить 15% от 300. Можно записать =15%*300

или =0,15*300. В обоих случаях результат будет одинаковым – число 45. Знак % может применяться и к содержимому ячейки, например, =А7%. Результатом будет одна сотая содержимого ячейки А7. Ввод формул Формулу можно вводить либо непосредственно в ячейку, либо в строку формул. Если в ячейку введена формула, то после нажатия Enter в этой ячейке отобразится результат вычислений по этой формуле. Если сделать данную ячейку текущей, то сама формула будет отображена в строке формул, а в ячейке – результат вычислений. Например, пусть требуется ввести в ячейку А1 сумму чисел 10 и 20. Для этого надо выделить ячейку А1 и ввести =10+20 (рис. 40).

Рис. 40 После нажатия Enter в текущей ячейке А1 появилось вычисленное значение – число 30, а в строке формул – сама формула, по которой произведено вычисление. (рис. 41)

Рис. 41 Задания 1. Введите в ячейку А1 формулу 3*8+12/4-2. После нажатия Enter моментально появится результат: 25. Активизируйте А1. Если в самой ячейке высвечивается результат вычисления, то в строке формул – введенное выражение. 2. Введите в ячейку А2 формулу (3*8+12)/4-2 После нажатия Enter моментально появится результат: 7. Активизируйте А2. Если в самой ячейке высвечивается результат вычисления, то в строке формул – введенное выражение. 3. Введите в ячейку А3 формулу 3*8+12/(4-2). После нажатия Enter моментально появится результат: 30. Активизируйте А3. Если в самой ячейке высвечивается результат вычисления, то в строке формул – введенное выражение. Обратите внимание на то, что формулы содержат одни и те же числа и операции, но различаются расположением скобок. Электронные таблицы могут работать в двух режимах: режиме решения и режиме показа формул. Для перехода из режима решения в режим формул следует выполнить команду Сервис – Параметры – установить флажок Формулы – ОК. Для перехода из режима формул в режим решения надо выполнить Сервис – Параметры – снять флажок Формулы – ОК. После выполнения пунктов1, 2, 3 задания на экране будет следующая таблица (рис. 42 – в режиме решения).

Рис. 42 4. Перейдите в режим показа формул, выполнив команду Сервис – Параметры – Формулы – ОК. Тогда таблица будет иметь вид (рис. 43 – режим показа формул).

Рис.43 Текстовый оператор & & – оператор сцепления строк. Он объединяет значения, стоящие слева и справа от него, в одну строку. Введем в ячейку В3 текст «Магазин продал », в ячейку С4 – « компьютеров» (в конце первого текста и в начале второго стоят пробелы, чтобы при объединении строки не слипались), в ячейку В5 число 100. Объединим эти данные в ячейке D5, используя оператор сцепления: =В3&С4&В5 В результате в D5 получим: Магазин продал 100 компьютеров (рис. 44).

Рис. 44 В режиме формул таблица будет иметь вид (рис. 45):

Рис. 45

Операторы отношения: =,

>, <, >=, <=, <>.

<> означает знак неравенства. Операторы отношения могут использоваться как с числовыми, так и с текстовыми данными. С их помощью можно строить формулы типа (рис. 46) =В2>А3, что означает, что содержимое ячейки В2 больше содержимого ячейки А3. Если, например, в ячейке В2 находится число 7, а в ячейке А3 число 3, то в результате вычисления по этой формуле получится значение ИСТИНА (рис. 47); если в ячейку А3 поместить число 9, то результатом будет ЛОЖЬ (рис. 48).

Рис. 46

Рис. 47 Рис. 48 В Excel можно сравнивать между собой буквы. Результатом вычисления по формуле =”B”<”D”

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

или отредактировать уже имеющуюся формулу. Для редактирования надо выделите ячейку, в которой записана формула, и нажать клавишу F2. Нажатие клавиши F2 переводит ячейку в режим редактирования. Для перевода ячейки в режим редактирования можно также дважды щелкнуть по ней. При редактировании формулы ячейки, ссылки на которые ссылаются в ней, выделяются на рабочем листе цветными рамками, а сами ссылки отображаются в ячейке и в строке формул тем же цветом, что облегчает редактирование 2.2. Использование в формуле ссылок В формулах можно использовать числа, находящиеся в других ячейках. Адреса ячеек, содержимое которых используется в вычислениях, называются ссылками. Значение, отображаемое в ячейке с формулой, пересчитывается при изменении значения ячейки, на которую указывает ссылка. При изменении содержимого ячеек изменяется и результат вычислений. Ссылку на ячейку можно задать разными способами. Во-первых, адрес ячейки можно задать вручную. Пусть требуется в ячейку А3 ввести формулу А1+А2. Пусть также в ячейке А1 записано число 3, а в ячейке А2 – число 7. Чтобы ввести формулу в ячейку А3 ее надо выделить (активизировать), набрать =А1+А2 и нажать Enter. В ячейке А3 появится число 10. Если выделить ячейку A3, в строке формул появится только что введенная формула (содержимое ячейки) А3 (рис. 49). В режиме показа формул будем иметь (рис. 50):

Рис. 49

Рис. 50 Задания

1. Перейдите в режим решения. 2. Введите в ячейку А1 число 7. Обратите внимание на то, что в ячейке А3 появился новый результат: 14.

3. Отмените предыдущее действие, нажав кнопку . В ячейке А3 появится прежний результат: 7. 4. Оставьте в ячейке А3 число 3, в ячейке А2 – число 7, в ячейку В1 введите число 2; в ячейку В4 введите формулу =(А1+А2)/В1*6. Формулу можно ввести заново, либо отредактировать уже имеющуюся. 4. Нажмите Enter, в ячейке А3 появится число 30. Выделите ячейку А3 – в строке формул будет содержимое ячейки. При вводе в формулу ссылок вручную необходимо убедиться, что клавиатура находится в режиме ввода латинских символов, иначе вместо результата будет выведено #ИМЯ?, что указывает на ошибочный ввод (рис. 51) .

Рис. 51 Для уменьшения количества ошибок, допускаемых при непосредственном вводе адресов ячеек в формулу, целесообразно использовать мышь. Например, если требуется ввести в ячейку С5 формулу =А4+В5, можно: выделить ячейку С5 и набрать знак равенства, щелкнуть по ячейке А4 набрать знак плюс, щелкнуть по ячейке В5 (в формулу будет введена ссылка на ячейку А), нажать Enter. Относительные и абсолютные ссылки В Excel существует два основных типа адресации данных (ссылок): относительные и абсолютные. Относительная ссылка определяет адрес ячейки по отношению к активной ячейке. Пусть, например, в ячейке В2 имеется ссылка на ячейку А3. В относительном представлении ссылка А3 указывает на ячейку, которая

расположена на один столбец левее и на одну строку ниже данной ячейки (рис. 52).

Рис. 52 Если формула будет скопирована в другую ячейку, то такое относительное указание ссылки сохранится. Например, если формула будет скопирована в ячейку С3, ссылка будет продолжать указывать на ячейку, расположенную на один столбец левее и на одну строку ниже данной ячейки, то есть на ячейку В4 (рис. 53). Если выполнить копирование содержимого ячейки В2 в разные стороны, можно увидеть, что копии будут отличаться от содержимого копируемой ячейки (рис. 54 – режим показа формул). При копировании по горизонтали изменяется имя столбца, при копировании по вертикали – номер строки, при копировании по диагонали – и то и другое. Часто такие изменения адресов нужны при копировании ячеек, чтобы ссылки на ячейки корректировались автоматически

Рис. 53

Рис. 54 По умолчанию ссылки на ячейки в формулах рассматриваются как относительные.

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

Рис. 55 В формулах можно создавать смешанные ссылки. Смешанная ссылка содержит и относительную, и абсолютную компоненты адреса ячейки. Например ссылка В$3 по столбцу относительная, а по строке абсолютная, поэтому она сохраняет адрес при копировании формулы, в которую она входит, по вертикали, а при копировании по горизонтали в третьей строке меняется адрес столбца (рис. 56 – режим показа формул).

Рис. 56 Таким образом, ссылки на ячейку В3 записываются с.о.: относительная ссылка: =В3 абсолютная ссылка: =$В$3 смешанные ссылки: =$В3

=В$3. Если перед символом стоит знак $, то соответствующие компоненты адреса являются абсолютными (при копировании не меняются), если знака $ нет – относительными (при копировании могут меняться). Для быстрого изменения типа ссылки используется клавиша F4. Для изменения способа адресации (относительного в абсолютный) надо выделить адрес в формуле и нажать кнопку F4. При последовательном нажатии клавиши F4 номер ячейки В3 будет записываться как $В$3, В$3, $В3. Для ускорения ввода абсолютных ссылок и избежания ошибок следует использовать клавишу F4. Например, 1. Выделите ячейку С2 и наберите =А2+В2. 2. Нажмите F4. Формула примет вид =А2+$В$2. 3. Еще раз нажмите F4 – ссылка станет смешанной и формула примет вид =А2+В$2 (относительный адрес столбца и абсолютный адрес строки). 4. Еще раз нажмите F4 – ссылка преобразуется и формула примет вид =А2+$В2 (абсолютный адрес столбца и относительный адрес строки). 5. Еще раз нажмите F4, и вы вернетесь к исходному относительному типу адресации. Как уже отмечалось, при копировании абсолютные и относительные адреса ведут себя по-разному. Особенность ЭТ – возможность автоматического изменения относительных адресов при копировании и перемещении формул. Формула, где в качестве операндов используются относительные адреса ячеек, воспринимается Excel как шаблон, а адреса ячеек в таком шаблоне – как средство указания на местоположение ячеек относительно ячейки с формулой. После копирования формула с относительными адресами будет ссылаться не на те ячейки, на которые она ссылалась в исходном месте, а на новые ячейки, адреса которых определяются относительно нового положения формулы. Рассмотрим пример. Пусть в таблицу введены данные, как представлено на рисунке 57 – в режиме показа формул, а на рисунке 58 – в режиме вычислений.

Рис. 58

Рис. 59 В формуле, находящейся в ячейке С3 адреса А1 и В2 являются относительными и отражают взаимное расположение исходных данных, находящихся в ячейках А1 и В2, и формулы, записанной в ячейке С3: ячейка А1 смещена на две клетки вверх и две клетки влево относительно ячейки С3, ячейка В2 смещена на одну клетки вверх и одну клетку влево относительно ячейки С3. Если скопировать содержимое ячейки С3 в ячейки D3 и E3, будем иметь (рис. 59, 60):

Рис. 59

Рис. 60 При копировании относительные адреса ячеек изменились таким образом, чтобы в каждой формуле сумма вычислялась для значений, находящихся смещенными на две клетки вверх и две клетки влево относительно ячейки D3 (E3) для первого слагаемого и на одну клетку вверх и одну клетку влево относительно ячейки D3 (E3) для второго слагаемого. Если изменить формулу, находящуюся в ячейке С3, заменив относительные адреса на абсолютные, и скопировать ее содержимое в ячейки D3:E3, получим (рис. 61 – режим показа формул, рис. 62 – режим вычислений):

Рис. 61

Рис. 62 При копировании смешанных ссылок абсолютная компонента адреса не изменится, а относительная пересчитается в зависимости от того, куда будет произведено копирование. Изменим адреса ячеек в формуле таким образом, чтобы они были смешанными. Например, пусть первый адрес в формуле будет $А1, а второй В$2. То есть в первом случае абсолютным является адрес столбца, а относительным является – адрес строки, во втором наоборот, адрес столбца является относительным, а адрес строки – абсолютным. Тогда формулы после копирования будут иметь вид (Рис. 63, 64):

Рис. 63

Рис. 64 Задания 1. В ячейки электронной таблицы введены данные, как показано на рисунке (рис. 65). Ответьте на поставленные вопросы, не прибегая к помощи

компьютера, а затем проверьте ответы, создав соответствующую таблицу в Excel.

Рис. 65 – Какие числа (формулы) будут в блоке ячеек С4:С7, если скопировать в него содержимое ячейки С3? – Поместите в ячейку С3 формулу =$А$1+$В$2. Какие числа (формулы) будут в блоке ячеек С4:С7, если скопировать в него содержимое ячейки С3? – Поместите в ячейку С3 формулу =$А1+В$2. Какие числа (формулы) будут в блоке ячеек С4:С7, если скопировать в него содержимое ячейки С3? – Поместите в ячейку С3 формулу =А$1+$В2. Какие числа (формулы) будут в блоке ячеек С4:С7, если скопировать в него содержимое ячейки С3? – Скопируйте содержимое ячейки С3 в блок ячеек D3:E3. Какие числа (формулы) будут в блоке ячеек D3:E3, если скопировать в него содержимое ячейки С3? – Скопируйте содержимое ячейки С3 в блок ячеек F6:F8. Какие числа (формулы) будут в блоке ячеек F6:F8, если скопировать в него содержимое ячейки С3. 2.3. Примеры 1. Реализовать в Excel вычисление суммы двух матриц размерностью 2 × 3. Суммой матриц А и В одной и той же размерности n × m называется матрица С той же размерности такая, что cik = aik + bik. Для проверки правильности реализации вычисления суммы матриц в Excel целесообразно рассмотреть контрольный пример. Пусть ⎛ 1 2 3⎞ ⎛3 2 1 ⎞ ⎟⎟ и В = ⎜⎜ ⎟⎟ . Найти их ⎝ 4 5 6⎠ ⎝ 1 0 −1⎠

даны две матрицы размерности 2 × 3: А = ⎜⎜ сумму – матрицу С = А + В.

Вычислим значения элементов матрицы С «вручную», чтобы затем сравнить их с машинным вариантом. 3 +1 ⎞ ⎛1 + 3 2 + 2 ⎛ 4 4 4⎞ ⎟⎟ = ⎜⎜ ⎟⎟ . ⎝ 4 + 1 5 + 0 6 + (−1) ⎠ ⎝ 5 5 5⎠

С = ⎜⎜

Рис. 66 Реализуем вычисление суммы матриц в Excel. Для этого: Откройте лист рабочей книги и дайте ему имя «Операции с матрицами 1». Введите в ячейки А1, Е1, I1 соответственно пояснения: Матрица А, Матрица В, Матрица С. Введите в блок ячеек А3:С4 элементы матрицы А, а в блок ячеек Е3:G4 элементы матрицы B. Блок ячеек I3:K4 используйте для вычисление элементов матрицы С (рис. 66 – режим показа формул). В ячейку I3 введите формулу =А3+Е3. Для ввода адресов ячейки в формулу используйте мышь. Для этого: активизируйте ячейку I3, введите знак «равно» (=), щелкните по ячейке А3, введите знак «плюс» (+), щелкните по ячейке Е3. Это поможет избежать неверного введения адресов ячеек. Оставьте адреса А3 и Е3 относительными. Скопируйте содержимое ячейки I3 в блок ячеек J3:K3, используя маркер заполнения. При копировании по строке относительные адреса столбцов в формуле поменяются, а относительные номера строк останутся неизменными, что нам и нужно для реализации решения поставленной задачи. Скопируйте содержимое ячейки I3 в ячейку I4. При копировании по столбцу имя столбца не изменится, а номера строки изменятся, что нас устраивает. Скопируйте содержимое ячейки I4 в блок ячеек J4:K4. При копировании по строке относительные адреса столбцов поменяются, а относительные номера строк останутся неизменными, что нам и нужно. В результате получим значения элементов матрицы С (рис. 67).

Рис. 67 Решение, полученное в Excel, совпадает с «ручным» счетом, значит, реализация решения задачи сделана верно и может быть использована для вычисление суммы любых двух матриц размерности 2 × 3. ⎛ 7 8 9⎞

⎛ − 6 − 11

3 ⎞

⎟⎟ , В = ⎜⎜ ⎟, Вычислите сумму двух матриц: А = ⎜⎜ 8 − 6 ⎟⎠ ⎝ 3 5 4⎠ ⎝ 6 используя имеющуюся таблицу. Для этого введите в блоки ячеек А3:С4 и Е3:G4 новые значения элементов матриц А и B. Тогда в блоке ячеек I3:K4 получите новые значения элементов матрицы С (рис. 68 – режим решения).

Рис. 68 2. Реализовать в Excel вычисление произведения матрицы размерности 2 × 3 на число. Произведением матрицы А на число λ (или числа λ на матрицу А) называется матрица В такая, что bik = λ · aik, т.е. при умножении матрицы на число (или числа на матрицу) надо все элементы матрицы умножить на это число. Рассмотрим контрольный пример. Пусть дана матрица размерностью 2 ⎛ 1 2 3⎞ ⎟⎟ и число λ = 10. Вычислим матрицу В1. × 3: А = ⎜⎜ ⎝ 4 5 6⎠ ⎛ 1 ⋅ 10 2 ⋅ 10 3 ⋅ 10 ⎞ ⎛ 10 20 30 ⎞ ⎟⎟ = ⎜⎜ ⎟⎟ . ⎝ 4 ⋅ 10 5 ⋅ 10 6 ⋅ 10 ⎠ ⎝ 40 50 60 ⎠

В1 = ⎜⎜

Реализуйте вычисление произведения матрицы размерности 2 × 3 на число в Excel. Для этого: Откройте лист рабочей книги «Операции с матрицами 1».

В ячейку B6 введите число 10. В ячейку С6 – пояснительный текст λ=. В блок ячеек А9:С10 введите формулы для вычисления произведения матрицы на число. Для этого: В ячейку А9 введите формулу =А3*D6 и нажмите кнопку F4, чтобы сделать адрес D6 абсолютным. Получится формула =А3*$D$6. Скопируйте содержимое ячейки А9 в блок ячеек В9:С9. Относительный адрес при копировании изменится (поменяется имя столбца), а абсолютный останется неизменным, что нас устраивает. Скопируйте содержимое ячейки А9 в ячейку А10. Относительный адрес при копировании изменится (поменяется номер строки), а абсолютный останется неизменным, что нам и надо. Скопируйте содержимое ячейки А10 в блок ячеек В10:С10. Относительные адреса при копировании изменятся, а абсолютные нет (рис. 69 – режим формул). В режиме решения таблица будет иметь вид (рис. 70).

Рис. 69

Рис. 70

3. Реализовать в Excel вычисление произведения матрицы А размерности 3 × 3 на вектор-столбец Х. ⎛ a11 a12 a13 ⎞ ⎜ ⎟ А = ⎜ a 21 a 22 a 23⎟ , ⎜ a31 a32 a33 ⎟ ⎝ ⎠

⎛ x1 ⎞ ⎜ ⎟ Х = ⎜ x2 ⎟ , ⎜ x3 ⎟ ⎝ ⎠

⎛ b1 ⎞ ⎜ ⎟ В = А·Х = ⎜ b2 ⎟ , где ⎜ b3 ⎟ ⎝ ⎠

b1 = a11· b1 + a12 · b2 + a13 · b3, b2 = a21· b1 + a22 · b2 + a23 · b3, b3 = a31· b1 + a32 · b2 + a33 · b3. Рассмотрим контрольный пример. ⎛ 1 2 − 1⎞ ⎜ ⎟ Пусть матрица А = ⎜ 3 0 2 ⎟ , вектор-столбец Х = ⎜4 − 2 5 ⎟ ⎝ ⎠

⎛ 2⎞ ⎜ ⎟ ⎜ 4⎟ . ⎜ 6⎟ ⎝ ⎠

Для вычисления вектора В = А·Х воспользуемся вычисляющими значения bi:

формулами,

b1 = 1· 2 + 2 · 4 + (-1) · 6 = 4, b2 = 3· 2 + 0 · 4 + 2 · 6 = 18, b3 = 4· 2 + (-2) · 4 + 5 · 6 = 30. Реализуем вычисление произведения матрицы А размерностью 3 × 3 на вектор-столбец Х в Excel. Для этого: Откройте лист рабочей книги и дайте ему имя «Операции с матрицами 2». Введите в ячейки А1, Е1, C6 соответственно пояснения: Матрица А, Вектор-столбец X, Произведение А·Х. Введите в блок ячеек А2:С4 элементы матрицы А, а в блок ячеек Е2:Е4 элементы вектор столбца Х. В блоке ячеек D7:D9 организуйте вычисление элементов матрицы B (рис. 71– режим показа формул). Для этого: В ячейку D7 введите формулу =А2*$Е$2+В2*$Е$3+С2*$Е$4. Адреса ячеек блока Е2:Е4 взять абсолютными, чтобы при копировании они не менялись. Скопируйте содержимое ячейки D7 в блок ячеек D8:D9. Относительные адреса ячеек подстроятся под нужные нам формулы. Получим таблицу, представленную на рисунке 72 (в режиме решения).

Рис. 72

Рис. 73

4. Для матрицы размерностью 3 × 3 реализовать в Excel вычисление: – определителя матрицы, – обратной матрицы, – умножения обратной матрицы на прямую (получится единичная матрица), – транспонированной матрицы. Рассмотрим контрольный пример. ⎛ 1 2 − 1⎞ ⎜ ⎟ Пусть дана матрица А = ⎜ 3 0 2 ⎟ . ⎜4 − 2 5 ⎟ ⎝ ⎠

Выполним заданные вычисления «вручную», чтобы в дальнейшем использовать их в качестве контрольного примера для проверки правильности реализации вычислений в Excel. Определитель матрицы третьего порядка определяется по формуле: det(A) = a11a22 a33 + a12 a23 a31 + a13 a21 a32 – – a11 a23 a32 – a12 a21 a33 – a13 a22 a31.

Для заданной матрицы det A = 1·0·5 + 3·(-2)·(-1) + 2·2·4 – (-1) ·0·4 - 2·3·5 - 1·2·(-2) = -4 det(А)

≠ 0.

Вычислим обратную матрицу. Квадратная матрица В называется обратной квадратной матрице А, если произведение А·В есть единичная матрица. Для нахождения обратной матрицы А-1 используем формулу: А-1 =

1 (AV)T, det(А) det A

≠ 0,

где AV – матрица, присоединенная к матрице А. Элементами присоединенной матрицы являются алгебраические дополнения элементов матрицы А. Найдем их.

0

3

2

А11 = (-1)1+1 − 2 5 = 4 А12 = (-1)1+2 4

2 А21 = (-1)2+1 − 2

3

0

5 = -7 А13 = (-1)1+3 4 − 2 = -6

1 −1

−1

1

2

5 = -8 А22 = (-1)2+2 4 5 = 9 А23 = (-1)2+3 4 − 2 = 10

1 −1

2 −1 А31 = (-1)3+1 0

2

1 2

2 = 4 А32 = (-1)3+2 3 2 = -5 А33 = (-1)3+3 3 0 = -6

Таким образом, присоединенная матрица будет иметь вид: ⎛ 4 − 7 − 6⎞ ⎜ ⎟ A = ⎜ − 8 9 10 ⎟ , ⎜ 4 − 5 − 6⎟ ⎝ ⎠ V

а транспонированная присоединенная матрица ⎛ 4 −8 4 ⎞ ⎜ ⎟ (A ) = ⎜ − 7 9 − 5 ⎟ ⎜ − 6 10 − 6 ⎟ ⎝ ⎠ V T

и

2 −1 ⎞ ⎛ −1 ⎜ ⎟ 1 V T А = – (A ) = ⎜1.75 − 2.25 1.25 ⎟ . 4 ⎜ 1 .5 − 2 .5 1 .5 ⎟ ⎝ ⎠ -1

Вычислим матрицу С = А × В. Для вычисления произведения матриц размерности 3 × 3 используем формулы: ⎛ a11 a12 a13 ⎞ ⎜ ⎟ А = ⎜ a 21 a 22 a 23⎟ , ⎜ a31 a32 a33 ⎟ ⎝ ⎠

⎛ b11 b12 b13 ⎞ ⎜ ⎟ B = ⎜ b21 b22 b23 ⎟ , ⎜ b31 b32 b33 ⎟ ⎝ ⎠

⎛ c11 c12 c13 ⎞ ⎜ ⎟ C = A·B= ⎜ c 21 c 22 c 23 ⎟ , ⎜ c31 c32 c33 ⎟ ⎝ ⎠

где c11 = a11· b11 + a12· b21 + a13 ·b31 c12 = a11· b12 + a12 ·b22 + a13· b32 c13 = a11· b13 + a12· b23 + a13· b33 c21 = a21· b11 + a22· b21 + a23· b31 c22 = a21· b12 + a22· b22 + a13· b32 c23 = a21· b13 + a23· b23 + a13· b33 c31 = a31· b11 + a32· b21 + a33· b31 c32 = a31· b12 + a32· b22 + a33· b32 c33 = a31· b13 + a33· b23 + a33· b33. Для рассматриваемого примера: c11 = 1·(-1)+2·1,25+(-1) ·1,75 = 1 c12 = 1· (-1) + 2 ·1,25 + (-1)· 1,5=0 c13 = 1· (-1) + 2· 1,25 + (-1) · 1,5=0 c21 = 3· (-1) + 0· 1,75 + 2· 1,5=0 c22 = 3· 2 + 0· (-2,25) + 2· (-2,25)=1 c23 = 3· (-1) + 0· 1,25 + 2· 1,5=0 c31 = 4· (-1) + (-2)· 1,75 + 5· 1,5=0 c32 = 4· 2 + (-2)· (-2,25) + 5(-2,5)=0 c33 = 4· (-1) + (-2)· 1,25 + 5· 1,5=1. Таким образом, получили матрицу С: ⎛ 1 0 0⎞ ⎜ ⎟ C = ⎜ 0 1 0 ⎟ = Е. ⎜ 0 0 1⎟ ⎝ ⎠

Матрица С – единичная матрица (так как А – прямая матрица, а В – обратная), что и следовало ожидать.

Теперь можно перейти к реализации вычислений в электронной таблице. Откройте лист рабочей книги и дайте ему имя «Операции с матрицами 3». Введите в блок ячеек А2:С4 элементы матрицы А (рис. 73). В ячейку Е3 введите формулу для вычисления определителя матрицы: =A2*B3*C4+A3*B4*C2+B2*C3*A4-C2*B3*A4-B2*A3*C4-2*C3*B4. Для ввода адресов ячейки в формулу используйте мышь.

Рис. 73 Транспонируйте матрицу А в блок ячеек А7:С9 (рис. 74).

Рис. 74 Введите в блок ячеек G2:I4 формулы для вычисления элементов присоединенной матрицы. В блок ячеек G7:I7 введите формулы для вычисления обратной матрицы (рис. 75 – фрагмент таблицы в режиме показа формул). Для этого надо транспонировать присоединенную матрицу и все ее элементы разделить на определитель. Для ускорения введения формул следует использовать операцию копирования с помощью маркера заполнения. Так как содержимое ячейки Е3 не должно меняться при копировании, необходимо адрес этой ячейки сделать абсолютным.

Рис. 75 Перенесите в ячейку G7 результат вычисления по формуле, находящийся в ячейке G2. Разделите его на содержимое ячейки Е3: =G2/Е3. нажмите кнопку F4. Получите =G2/ $E$3. Скопируйте содержимое ячейки G7 в остальные ячейки блока G7: I9. В результате будем иметь (рис. 76).

Рис. 76 В блоке ячеек D12:F14 организуйте вычисление элементов × матрицы С = А В по приведенным выше формулам (рис. 77). Для этого в ячейку D12 введите формулу для вычисления с11: =A2*E2+B2*E3+C2*E4. Чтобы ускорить процесс введения формул для вычисления остальных элементов матрицы С, целесообразно использовать операцию копирования. Например, чтобы получить значения с12 и c13 можно скопировать содержимое ячейки С2, где находится формула, по которой вычисляется значение с11, в ячейки Е2:G12.

Рис. 77

Рис. 78 При копировании по строке номера строк адресов ячеек, используемых в формуле, не меняются, а имена столбцов меняются. В формулах, вычисляющих значения с11, с12 и с12, элементы матрицы А (a11 a12 a13) одинаковые, поэтому при копировании адреса ячеек, в которых они находятся, не должны меняться. Сделайте их абсолютными. Адреса же элементов матрицы В поменяются как раз так, как нам нужно. В связи со сказанным измените формулу, записанную в ячейку D12 для вычисления с11 следующим образом: =$A$2*E2+$B$2*E3+$C$2*E4.

После копирования в первой строке матрицы С будут нужные формулы. Для получения элементов второй и третьей строк матрицы поступите так же. Введите в ячейку D13 формулу для вычисления с21, сделайте абсолютными адреса матрицы А и скопируйте содержимое ячейки С8 в ячейки D8:E8. Введите в ячейку С9 формулу для вычисления с31, сделайте абсолютными адреса матрицы А и скопируйте содержимое ячейки С9 в ячейки Е13:G13 (рис. 78). Можно организовать вычисление элементов матрицы С, например, в блоке ячеек D17:F19, заполняя ее по столбцам. В этом случае при копировании по столбцу адреса столбцов меняться не будут, а номера строк поменяются. Поэтому для удобства копирования по столбцам целесообразно формулу, вычисляющую значение с11 записать, в ячейку D17 таким образом: =A2*$E$2+B2*$E$3+C2*$E$4, а затем скопировать ее в блок ячеек D18:D19. Аналогично заполните остальные ячейки блока. В результате получите таблицу (рис. 79). Результаты решения задачи, полученные при использовании таблицы Excel совпали с ответами контрольного примера.

Рис. 79

Задания 1. Реализовать в Excel вычисление суммы двух матриц размерности 3 × 3. 2. Реализовать в Excel вычисление произведения матрицы размерности 3 × 3 на число.

2.4. Формулы с массивами Массивы являются удобным средством обработки групп однотипных данных. Массивы могут быть аргументами в некоторых функциях или формулах, возвращающих в результате вычислений либо единственное значение, либо массив новых значений. Формулы, возвращающие массив результатов, называются табличными формулами или формулами массива. Блок ячеек, в котором используется общая формула, называется интервалом массива. Массив представляет собой определенным способом организованный список каких-либо данных. Массивы констант В формулах Excel можно использовать массивы констант. Массив констант строится как совокупность значений, заключенных в фигурные скобки и разделенных точкой с запятой. Массивы могут иметь различную размерность. Различают одномерные и двумерные массивы. Например, одномерный массив, размерностью 1× 3 , состоящий из одной строки и трех элементов в этой строке и содержащий элементы: 5 7 9, записывается как {5;7;9}, т.е. представляет собой совокупность значений массива, заключенных в круглые скобки и разделенных точкой с запятой. Это горизонтальный одномерный массив (вектор-строка). Чтобы записать одномерный массив вертикально (вектор-столбец): 5 7 9, следует записать {5:7:9}, т.е. записать совокупность значений массива в круглых скобках и разделить их двоеточием. Двумерный массив состоит из нескольких строк и столбцов. Например, двумерный массив, размерностью 2 × 3 , (состоящий из двух строк и трех столбцов), содержащий элементы: 11 13 15 17 19 21, записывается как {11;13;15:17;19;21}, т.е. представляет собой совокупность значений массива, заключенных в круглые скобки, при этом значения,

расположенные в одной строке отделяются друг от друга точкой с запятой, а строки двоеточием. Массивы констант могут содержать числовые, текстовые или логические значения, причем текстовые константы должны быть заключены в кавычки. В один массив можно вводить значения разных типов, например, массив : abc 123 представляется как {«a»;«b»;«c»:1;2;3}. Пусть имеются два двумерных массива: 1 2 10 20 3 4, 30 40. Найти массив, каждый элемент которого представляет собой сумму соответствующих элементов исходных массивов: 11 22 33 44. Выполним следующие действия: Выделим блок ячеек, в котором должны расположиться элементы результирующего массива: В2:С3. Не сбрасывая выделения, в текущую ячейку (или в строку формул) введем выражение: ={1;2:3;4}+{10;20:30;40}. Одновременно нажмем комбинацию клавиш Ctrl + Shift + Enter. Использование такой комбинации клавиш сообщает Excel, что вводится формула с массивами. Excel автоматически добавляет фигурные скобки при вводе табличной формулы. Эти скобки видны в строке формул, но если эти скобки ввести «вручную», формула будет воспринята как текст. Операндами этой формулы являются массивы констант. В выделенном диапазоне получим элементы результирующего массива. Формулы с массивами выдали более одного результата: в режиме решения видим массив чисел (рис. 80), в режиме показа формул – массив формул (рис. 81).

Рис. 80

Рис. 81 Как видно из рисунка 81, в каждой ячейке выделенного диапазона записана формула, введенная в текущую ячейку. Эта «единичная» формула как бы существует одновременно в четырех ячейках в виде различных формул выделенного диапазона. Но внести изменения в них по отдельности нельзя. Это можно сделать, только выделив весь интервал В2:С3. Интервал В2:С3 представляет собой интервал массива, и табличная формула хранится в каждой ячейке. Если в формуле используется массив констант, все остальные компоненты должны иметь ту же размерность, что и первый массив. В случае необходимости Excel расширяет нужные компоненты до требуемых размеров. Все компоненты должны иметь такое же количество строк и столбцов, как компоненты с наибольшим числом строк и столбцов. Например, пусть одна из компонент – двумерный массив, состоящий из двух строк и трех столбцов. Для представления результатов вычисления по формуле ={1;2;3:4;5;6}*2 требуется выделить блок ячеек 2 × 3 . Одна из компонент исходного массива – массив 2 × 3 , а другая – одиночное значение. При вычислении по этой формуле автоматически будет расширена вторая компонента до массива размерности 2 × 3 и произойдет вычисление по этой формулы как ={1;2;3:4;5;6}*{2;2;2:2;2;2}. Результат представлен на рисунке 82.

Рис. 82 Формулы массива В формулах Excel в качестве операндов будут использоваться формулы массива (табличные формулы). Пусть в блоке ячеек А2:D2 записаны числа 1, 3, 5, 7, а в блоке ячеек А3:D3 – 10, 30, 50, 70. Вычислим и запишем в блок ячеек А5:D5 суммы соответствующих элементов блоков ячеек А2:D2 и А3:D3. Сделаем это уже традиционным способом: введем в ячейку А5 формулу =А2+D2 и скопируем содержимое этой ячейки в блок ячеек В5:D5. В блоке ячеек А5:D5 будут записаны числа 11, 33, 55, 77 (рис. 83 – режим формул, рис. 84 – режим решения). Теперь используем для вычисления элементов результирующего массива табличную формулу (формулу массива). Для этого:

Выделим для получения результата блок ячеек А7:D7. Не сбрасывая выделения, в текущую ячейку (или в строку формул) введем выражение: = А2:D2 + А3:D3. Одновременно нажмем комбинацию клавиш Ctrl + Shift + Enter. Формула будет автоматически заключена в круглые скобки. Результатом введенного выражения будут суммы в выделенных ячейках (рис. 83 – режим показа формул). В результате в блоке ячеек получим результирующий массив (рис. 84 – режим решения).

Рис. 83

Рис. 84 Интервал А7:D7 представляет собой интервал массива, где в каждой ячейке содержится формула массива. Внести изменения по отдельности в ячейку интервала нельзя. Интервал же А5:D5 не представляет собой интервал массива, и можно отдельно внести изменения в любую формулу этого блока. Рассмотрим пример. Пусть в блок ячеек А2:В3 записаны числа 1, 3, 5, 7, а в блок ячеек D2:Е3 – 10, 30, 50, 70, т.е. даны два двумерных массива. Вычислим и запишем в блок ячеек В5:С6 сумму соответствующих элементов блоков ячеек А2:В3 и D2:Е3. Выполним действия, аналогичные действиям, рассмотренным в предыдущем примере: Введем в блоки ячеек А2:В3 и D2:Е3 исходные данные. Выделим для получения результата блок ячеек В5:С6.

Не сбрасывая выделения, введем выражение:

в текущую ячейку (или в строку формул)

= А2:В3 + D2:Е3. Одновременно нажмем комбинацию клавиш Ctrl + Shift + Enter.

Рис. 85

Рис. 86 Формула будет автоматически заключена в фигурные скобки. Результатом введенного выражения будут суммы в выделенных ячейках (рис. 85 – режим показа формул). В результате в блоке ячеек получим результирующий массив (рис. 86 – режим решения). Аргументами формулы являются два однотипных массива: в первом примере – одномерные массивы, состоящие из четырех элементов, во втором двумерные, состоящие из двух столбцов и двух строк. Такими же являются результирующие массивы. Попробуем ввести аналогично формулу в диапазон ячеек, отличный от диапазона исходных ячеек. Правильные суммы оказались записанными только в блок, соответствующий исходным блокам, в остальные же ячейки блока помещен код ошибки #Н/Д (нет данных) (рис. 87).

Рис. 87 Выделим для получения результата блок ячеек, меньший, чем требуется. Тогда от результата останется только два значения, остальные будут потеряны (рис. 88).

Рис. 88 Формулы массива можно использовать для ввода повторяющихся формул, что позволит сэкономить время при вводе. Необходимо помнить, что нельзя удалить отдельные ячейки в интервале массива, так как ячейки совместно используют одну формулу. Редактирование массивов Так как все ячейки интервала массива используют одну формулу, этот интервал редактируется иначе, чем обычные ячейки. При редактировании интервала массива одновременно меняются все ячейки интервала. Ни одна операция не может быть выполнена для части массива. Нельзя ни редактировать, ни удалять, ни перемещать, ни очищать отдельные ячейки массива, нельзя изменять содержимое только некоторых ячеек массива, вставлять в него или удалять из него ячейки. Все ячейки в таком интервале являются единым целым и редактируются также целиком путем выделения всего интервала. При попытке это сделать выводится сообщение о том, что данную операцию нельзя выполнить для массива. Редактировать формулу массива можно следующим образом: выделить любую ячейку диапазона и поместить в строку формул курсор (либо дважды щелкнуть по одной из ячеек и курсор появится в самой ячейке). Фигурные скобки в строке формул исчезнут. Ввести другую формулу в ячейку или изменить старую, можно также удалить ячейку. Нажать комбинацию клавиш Ctrl + Shift + Еnter. Будет выделен интервал массива и внесены изменения во все ячейки интервала. Например, поменяем знак плюс в формуле = А2:В3 + D2:Е3 на минус. Для этого активизируем текущую ячейку, поменяем знак «+» на «-» и нажмем Ctrl + Shift + Enter. Получим (рис. 89):

Рис. 89 Для перемещения содержимого массива-интервала надо: выделить его целиком. выполнить команду Правка – Вырезать или нажать кнопку Вырезать (вокруг выделенной области побежит пунктирная рамка). Выделить ячейку в начале новой области. Выполнить команду Правка – Вставить. Выделенный массив можно перетащить в другое место мышью. Чтобы определить диапазон, занимаемый формулой массива, следует выделить какую-нибудь ячейку массива и выбрать команду Правка – Перейти – Выделить, в окне выделение группы ячеек установить переключатель на группе текущий массив. Задания 1. Реализовать в Excel вычисление суммы двух матриц размерностью 3 × 3, используя массив формул. 2. Реализовать в Excel вычисление произведения матрицы размерностью 3 × 3 на число, используя массив формул.

Глава 3. Использование функций В Excel используется более трехсот встроенных функций. Они выполняют определенные вычисления над своими аргументами и возвращают одно или несколько значений. Применение функций значительно расширяет возможности Excel, делает эту программу действительно универсальной. Все функции можно подразделить на следующие группы: математические, статистические, логические, дата и время, финансовые, текстовые, ссылки и массивы, работы с базой данных, проверки свойств и значений инженерные, информационные 3.1. Правила записи и ввода функций Запись функции состоит из двух частей: имени функции и следующего за ним аргумента (или аргументов) в круглых скобках.. Только несколько функций, к которым относятся, например, ПИ(), ИСТИНА(), не имеют аргументов. Но даже в этом случае после имени функции обязательно должна быть пара круглых скобок. Функции применяются в формулах, часто в сочетании с операторами и другими функциями. Рассмотрим пример. Для вычисления суммы значений ячеек С1, С2, С3, С4, С5, С6, С7, С8, С9, С10 можно использовать формулу =С1+С2+С3+С4+С5+С6+С7+С8+С9+С10 но она очень длинная, поэтому можно воспользоваться функцией СУММ() и ввести ее в ячейку вместо записанной ранее формулы. Тогда будем иметь в рассматриваемой ячейке =СУММ(С1:С10) . Правила использования аргументов: Если функция имеет несколько аргументов, они должны быть разделены точкой с запятой. Например, как в формуле =СУММ(С1;С2;С3) Максимальное количество аргументов в функции не должно превышать 30, а длина формулы не может быть больше 1024 символов. Но

отдельный аргумент может быть блоком, содержащим любое количество ячеек. Например, как в функции =СУММ(С1:С10; D1:D25; F7:F17) Аргументом функции может быть любое математическое выражение, составленное из других функций. Например, =CУММ(COS(F3*ПИ());SIN(D3*ПИ())) В рассмотренных примерах в качестве аргументов функции использовались адреса ячеек или блоки ячеек. В качестве аргументов функций могут использоваться также числа. Например, как в формуле =СУММ(25;30;45) После нажатия клавиши Enter в ячейке, содержащей данную формулу, будет число 100. В общем случае можно сказать, что функцию суммирования можно записать СУММ(числа), где числа – это список не более, чем из 30 аргументов, каждый из которых может быть или числом, или формулой, или адресом ячейки, или блоком ячеек, где записаны числовые значения. Ввод функций 1 способ Печатая их непосредственно на клавиатуре. 2 способ С помощью главного меню. Для этого надо: Выделить ячейку, в которую надо ввести функцию. Выбрать команду Вставка – Функция (рис. 90). Появится диалоговое окно Мастер функций – шаг 1 из 2. Выбрать категорию функции слева в списке Категория функции (рис. 91). (Если воспользоваться категорией Полный алфавитный перечень, поиск функции будет производиться медленнее.) Прокрутить справа алфавитный список функций из этой категории, найти среди них нужную и выделить ее имя (рис. 92).

Рис. 90

Рис. 91 Щелкнуть по ОК или нажать Enter. В активную ячейку будет помещен знак равенства, имя выбранной функции и пустые скобки с курсором и затем появится диалоговое окно. Оно дает возможность ввести аргументы выбранной функции в соответствующие поля с названиями Число1, Число2, количество которых будет увеличиваться по мере ввода новых аргументов (рис. 92).

Рис. 92 Ввести в поля ввода значения аргументов или их адреса. Чтобы ввести адрес аргумента можно выделить нужную ячейку или блок ячеек на листе ЭТ. Одновременно справа от окошка ввода аргумента будет показано его текущее значение (или список текущих значений). Результат вычислений появится в правом верхнем углу диалогового окна и около слова Значение в нижней части окна и будет изменяться одновременно с изменением вводимых аргументов. (Для перехода от одного поля ввода к другому следует щелкнуть по нему левой клавишей мыши.) Щелкнув по кнопке ОК или нажав клавишу Enter, можно завершить ввод функции в активную ячейку. В том случае, когда вы знаете имя функции, но не помните точно, какие у нее аргументы и в каком порядке они должны следовать, надо набрать знак = и имя функции, а затем нажать Ctrl+A. Откроется сразу второе диалоговое окно Мастер функций, в которое можно вводить все нужные аргументы. Примечание. Адреса ячеек в функции можно вставлять так же, как и в обычные формулы. Пусть, например, в ячейку С10 нужно ввести сумму значений, находящихся в ячейках С1:С9. Сначала надо выделить ячейку С10 и набрать =СУММ(). После этого следует выделить интервал С1:С9; по периметру этого интервала появится бегущая рамка. Нажать Enter. Ввод формулы будет завершен, бегущая рамка исчезнет, в строке формул будет выведено =СУММ(С1:С9). 3 способ С помощью панели инструментов. Для этого надо: Выделить ячейку, в которую следует ввести функцию.

Нажать кнопку Вставка функции на панели инструментов Стандартная. Появится диалоговое окно Мастер функций. Далее следует производить действия, описанные во втором способе. Так как функция =СУММ(числа) очень распространенная, для ее ввода предусмотрена специальная кнопка Автосуммирование инструментов Стандартная.

на панели

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

3.2. Математические функции ABS(число) – вычисляет абсолютное значение аргумента.. Число – это единственный аргумент, который может быть числом, адресом ячейки с числовым значением или формулой. Например, если ячейка В2 содержит число -25, то в результате выполнения функции = ABS( В2) получится значение 25 (возвращает значение 25). При положительном значении аргумента в результате выполнения функции эта величина останется без изменения. ЗНАК(число) – определяет знак числа. Если аргумент больше 0, то возвращает значение 1, если значение равно 0, то возвращает значение 0, если отрицательное – то -1. Например, функция = ЗНАК(В2) Если ячейка В2 содержит число 15, то будет возвращено значение 1, если –15, то –1, если 0, то 0.

СУММ(числа) – вычисляет сумму чисел, на которые ссылается аргумент. Числа – это список аргументов, каждый из которых может быть числом, формулой, или адресом ячейки или блока ячеек, где записаны числовые значения. Например, функция = СУММ(А1;В1) вычислит сумму чисел, находящихся в ячейках А1,В1. = СУММ(А1:В5) вычислит сумму чисел, находящихся в блоке ячеек А1:В5. СУММЕСЛИ(область просмотра; критерий поиска; область просмотра) – ищет в области просмотра данные, отвечающие критерию поиска и суммирует значения соответствующих ячеек из области суммирования. Если область суммирования не задана, суммирование производится из области просмотра. Критерий поиска может включать только одно условие. Например, функция =СУММЕСЛИ(С6:С10;”ж”;D6:D10), введенная в ячейку В12 (рис. 93) для вычисления суммы окладов женщин, возвращает число 6600, а функция =СУММЕСЛИ(С6:С10;”м”;D6:D10), введенная в ячейку В12 (рис.94) для вычисления суммы окладов мужчин, возвращает число 9800 – сумму окладов мужчин.

Рис. 93

Рис. 94 ПРОИЗВЕД(числа) – перемножает всех значения, на которые ссылается аргумент. Числа – это список аргументов, каждый из которых может быть числом, формулой, или адресом ячейки или блока ячеек, где записаны числовые значения. Например, функция =ПРОИЗВЕД(А1,В1,В5) вычислит произведение чисел, находящихся в ячейках А1, В1, В5, = ПРОИЗВЕД(А1:В5) произведение чисел, находящихся в блоке ячеек А1:В5. СУММПРОИЗВ(массив1; массив2;…) вычисляет сумму произведений соответствующих членов двух или более массивов- аргументов. Например, функция = СУММПРОИЗВ(А1:А10;В1:В10) вычислит сумму произведений элементов блоков ячеек А1:А10 и В1:В10, т.е. А1·В1+А2·В2+А3·В3+…А10·В10. ЧАСТНОЕ(делимое; делитель) – возвращает полученного от деления (дробь отбрасывается). Например, функция =ЧАСТНОЕ(9; 2) возвращает 4.

целую

часть

частного,

НОД(число1; число2; …) – вычисляет наибольший общий делитель всех аргументов, каждый из которых должен быть больше 1. Дробная часть аргументов (если они есть) отбрасывается. Единица является делителем любого числа. Например, функция =НОД(36; 45) возвратит 9 –наибольшее число, делящее без остатка оба числа: и 36, и45.

НОК(число1; число2; …) – вычислит наименьшее общее кратное всех аргументов (до 29), которые должны быть больше 1. Дробная часть аргументов (если они есть) отбрасывается. Например, функция =НОК(10; 8) возвратит 40 – наименьшее число, делящееся без остатка на 10 и 8. ФАКТР(число) – вычисляет факториал. (Факториалом называется произведение чисел от 1 до некоторого заданного числа.) Например, функция =ФАКТР(4) произведет вычисление 4!=1·2·3·4=24. Аргументом функции должно быть целое положительное число. Если аргумент окажется не целым, при вычислении факториала все десятичные знаки после запятой будут отброшены. В качестве аргумента может быть адрес ячейки, в которой хранится число. ОКРУГЛ(число; десятичные знаки) – округляет число до заданного количества десятичных знаков. Аргумент состоит из двух частей: первая часть аргумента – это число, подлежащее округлению, вторая часть определяет до какого количества знаков следует округлить число. Вторая часть аргумента может быть положительным числом, отрицательным и нулем. При отрицательных значениях десятичные знаки будут округляться влево от десятичной запятой, т.е. до десятков, сотен и т.д., а при положительном - вправо до десятичной запятой, т.е. до десятых, сотых, тысячных т т.д. Например, =ОКРУГЛ(342,567; 2) даст результат 342,57 =ОКРУГЛ(342,567; 1) даст результат 342,6 =ОКРУГЛ(342,567; 0) даст результат 343 =ОКРУГЛ(342,567; -1) даст результат 340 =ОКРУГЛ(342,567; -2) даст результат 300 Первая часть аргумента может быть ячейкой, в которой хранится какое-либо число. Например, функция =ОКРУГЛ(С5;2) производит округление числа, находящегося в ячейке С5 с точностью до двух знаков. ЦЕЛОЕ(число) – возвращает ближайшее снизу целое число от аргумента. Например, функция = ЦЕЛОЕ(7,9) возвратит число 7. =ЦЕЛОЕ(-7,9) возвратит число –8. ОТБР(число) – отбрасывает все дробные разряды числа. Например, функция

=ОТБР(7,9) возвратит число 7, а функция =ОТБР(-7,9) возвратит число –7. ЧЕТН(число) – округляет число до ближайшего четного. Например, функция =ЧЕТН(47,8) возвратит число 48, а функция =ЧЕТН(-24,9) возвратит число 26. НЕЧЕТ(число) – округляет число до ближайшего нечетного. Например, функция =НЕЧЕТ(47,8) возвратит число 49, а функция =НЕЧЕТ(-24,9) возвратит число 25. КОРЕНЬ(число) – вычисляет положительное значение квадратного корня из аргумента. Аргументом функции должно быть целое положительное число. Если аргумент окажется не целым, будет выдано сообщение об ошибке #число! Например, функция = КОРЕНЬ(9) даст результат 3. Если в ячейке А1 записано число 25 = КОРЕНЬ(А1) даст результат 5. СТЕПЕНЬ(число; степень) – возводит число в заданную степень. Например, функция =СТЕПЕНЬ(6; 2) возвратит число 36. Вместо функции СТЕПЕНЬ для возведения в степень можно использовать операцию ^, например, 6^2. EXP(степень) – возвращает результат возведения в степень основания натурального логарифма (e=2,718281828459045). Например, функция = EXP(2) вычислит e2=7,389056099. LN(число) – вычислит натуральный логарифм числа. Например, функция = LN(2,718281828459045) возвратит 1. LOG10(число) – вычислит десятичный логарифм числа. Например, функция =LOG10(100) возвратит 2. LOG(число; основание) – вычислит алгоритм числа по заданному основанию. Например, функция = LOG(100;10) – возвратит 2. ПИ()

Возвращает число 3,14159265358979 – математическую константу π. Например, если ячейка содержит формулу =ПИ()/2, будет возвращено число 1,570796322679490. РАДИАНЫ(угол) – преобразует угол, заданный в градусах, в радианы. Функция = РАДИАНЫ(75) возвратит 1,3089969. ГРАДУСЫ(угол) – преобразует угол, заданный в радианах в градусы. =ГРАДУСЫ(1,3089969) возвратит 75. SIN(угол в радианах) – вычисляет синус угла. COS(угол в радиана – вычисляет косинус угла. Рассмотрим пример. Пусть требуется вычислить площадь треугольника по формуле Герона. 1. Введем значения сторон в ячейки А4, А5, А6. 2. Вычислим в D4 величину полупериметра p=(a+b+c)/2. 3. Вычислим в D5 площадь треугольника по формуле S= p( p − a)( p − b)( p − c) . 4. Введем в ячейки A1, A2, А4, А5, А6, С4, С5 поясняющий текст как показано на рисунках 95 (режим формул), 96 (режим решения).

Рис. 95

Рис. 96

3.3. Матричные функции В категории математических функций имеются функции, которые реализуют средства линейной алгебры. Их аргументами являются матрицы, которые могут быть заданы адресами, именами, массивами констант. К таким функциям относятся: ТРАНСП(матрица) – транспонирует исходную прямоугольную матрицу, т.е. меняет элементы ее строк и столбцов местами. Например, матрица ⎛ 4 3⎞ ⎜⎜ ⎟⎟ после транспонирования будет иметь вид: ⎝ 1 2⎠

⎛ 4 1⎞ ⎜⎜ ⎟⎟ . ⎝ 3 2⎠

= ТРАНСП(А2:В4) – транспонирует матрицу, помещенную в блок ячеек А2:В4, в выделенный блок ячеек (на рис. 97 в блок ячеек Е5:F6). МОПР(матрица) – вычисляет определитель прямоугольной матрицы. Например, ⎛ 4 3⎞ ⎟⎟ = 4 · 2 – 3 · 1 = 5 ⎝ 1 2⎠

det A = ⎜⎜

= МОПР(А2:В4) – вычислит определитель матрицы, записанной в блок ячеек А2:В4, и поместит в ячейку I5, где записана эта функция (рис. 97). МОБР(матрица) – вычисляет матрицу, обратную к данной. Исходная и полученная матрицы могут быть только квадратными, т.е. имеющими одинаковое число строк и столбцов. Перемножение обратной и прямой матриц дает единичную матрицу, т. е. матрицу, у которой на главной диагонали находятся единицы. Не все матрицы имеют обратную. В этом случае будет выдано сообщение об ошибке #ЧИСЛО!. = МОБР(А2:В4) – вычислит матрицу, обратную матрице, записанной в блок ячеек и поместит ее в выделенный диапазон (на рис.84 – в блок ячеек E2:F3). МУМНОЖ(матрица1; матрица2) – перемножает матрицы-аргументы и помещает их в выделенный блок ячеек. Число столбцов матрицы1 должно совпадать с числом строк матрицы2. Результирующая матрица будет иметь число строк, как и матрица1, и число столбцов, как матрица2 Например, функция

= МУМНОЖ(A2:B3;E2:F3) перемножить матрицы, находящиеся в блоках ячеек A2:B3 и E2:F3 и поместит в блок ячеек I2:J3. Так как матрицы, находящиеся в блоках ячеек A2:B3 и E2:F3 соответственно прямая и обратная, то в результате их перемножения получилась единичная матрица (рис. 97).

Рис. 97 Для ввода одной из функций: ТРАНСП(матрица), МОБР(матрица), МУМНОЖ(матрица1; матрица2) должны быть выполнены следующие действия: выделен блок ячеек, куда должны быть помешены результаты; в текущую ячейку введена функция; нажата комбинация клавиш Shift + Ctrl + Enter (в строке формул введенная формула автоматически обрамляется в фигурные скобки). Рассмотрим пример. ⎛ 1 2 − 1⎞ ⎜ ⎟ Дана матрица А = ⎜ 3 0 2 ⎟ . Используя матричные функции, ⎜4 − 2 5 ⎟ ⎝ ⎠

вычислить обратную матрицу, транспонировать матрицу А, вычислить определитель матрицы, умножить обратную матриц на прямую. Данный пример был просчитан в главе «Построение формул». 1. Откройте новый лист рабочей книги Excel и назовите его «Матричные функции». 2. В блок ячеек А4:С6 введите матрицу А (рис. 98). Разместите обратную матрицу в блоке Е4:G6. Для получения обратной матрицы: выделите блок ячеек Е4:G6; в текущую ячейку блока введите формулу =МОБР(А4:С6); нажмите клавиши Shift + Ctrl + Enter.

Рис. 98 Поместите транспонированную матрицу в блок ячеек Е8:G10. Для получения транспонированной матрицы : выделите блок ячеек Е8:G10; в текущую ячейку блока введите формулу =ТРАНСП(А4:С6); нажмите клавиши Shift + Ctrl + Enter. После выполнения указанных действий в режиме показа формул блоки ячеек Е4:G6 и Е8:G10 будут иметь вид (рис. 99).

Рис. 99 Для размещения результата произведения прямой и обратной матриц используем блок ячеек I4:K6. Для нахождения произведения матриц: выделите блок ячеек I4:K6; в текущую ячейку введите формулу =МУМНОЖ(А4:С6; E4:G6); нажмите клавиши Shift + Ctrl + Enter (рис.100 – режим показа формул). Для вычисления определителя матрицы в ячейку I8 введите формулу =МОПР(А4:С6).

Рис. 100 В результате в режиме решения таблица будет иметь вид (рис. 101).

Рис. 101 Пример. Используя матричные функции, составить таблицу для решения системы уравнений ⎧4 x1 + 2 x2 = 7.9 ⎨ ⎩2 x1 + 6 x2 = 3.5

В матричной форме система уравнений может быть записана следующим образом: А · Х =В, где А – матрица коэффициентов системы, Х – вектор-столбец неизвестных, В – вектор-столбец свободных членов: a12 ⎞ ⎛a ⎟⎟ , A = ⎜⎜ 11 ⎝ a21 a22 ⎠

⎛x ⎞ X = ⎜⎜ 1 ⎟⎟ , ⎝ x2 ⎠

⎛b ⎞ B = ⎜⎜ 1 ⎟⎟ . ⎝ b2 ⎠

Для решения матричных уравнений удобно использовать обратную матрицу. Если det ≠ 0 , можно вычислить обратную матрицу А-1 и умножить левую и правую части заданного уравнения слева на А-1 : А-1 · (А · Х) = А-1 · В. Так как А-1 · (А · Х) = (А-1 · А) · Х, то А-1 · (А · Х) = E · X = X. Тогда получаем X = А-1 · B. Таким образом, для нахождения решения (вектора-столбца Х) надо найти обратную матрицу А-1 и умножить ее на вектор-столбец В. Выполните следующие действия: Откройте новый лист рабочей книги и назовите его Решение системы уравнений. В блок ячеек А2:В3 введите коэффициенты системы уравнений. В блок ячеек D2:D3 введите элементы вектор-столбца свободных членов. Выделите блок ячеек F2:G3; в текущую ячейку блока введите =МОБР(А2:В3); нажмите кнопки Shift + Ctrl + Enter. Выделите блок ячеек G5:G6; в текущую ячейку блока введите =МУМНОЖ(F2:G3;D2:D3); нажмите кнопки Shift + Ctrl + Enter. Введите пояснительный текст, как показано на рисунке 102.

Рис. 102 Получите решение системы уравнений в ячейках G5:G6 (рис.103).

Рис.103

3.4. Статистические функции Приведем некоторые статистические функции. СРЗНАЧ(числа) – производит вычисление среднего арифметического для последовательности чисел: суммируются числовые значения чисел и результат делится на количество этих значений. Например, функция =СРЗНАЧ(В5:В10) вычислит среднее арифметическое значение содержимого блока ячеек В5:В10. МАКС(числа) – находит наибольшее значение среди заданных чисел. Например, функция =МАКС(С3:С13) найдет максимальное значение в блоке ячеек С3:С13. МИН(числа) – находит наименьшее значение среди заданных чисел. Например, функция =МИН(С10:С20) найдет минимальное значение в блоке ячеек С10:С20. СЧЕТ(область просмотра) – подсчитывает в области просмотра количество числовых значений. Пустые ячейки, логические значения и тексты пропускаются. Например, =СЧЕТ(B1:D3) возвратит 6 (рис. 104).

Рис. 104 СЧЕТЗ(область просмотра) – подсчитывает в области просмотра количество непустых ячеек, которыми считаются значения любого типа, включая и строки нулевой длины (“ “). Например, =СЧЕТЗ(B1:D3) возвратит 8 (рис. 105).

Рис.105 СЧЕТЕСЛИ(область просмотра; критерий поиска) – подсчитывает в области просмотра число ячеек, отвечающих критерию поиска. Например, =СЧЕТЕСЛИ(B1:D3;”>3”) возвратит 2 (рис.106).

Рис.106 =СЧЕТЕСЛИ(B1:D3;”=ж”) возвратит 1 (рис.10).

Рис.107 Рассмотрим примеры. 1. Найти минимальное и максимальное значения из чисел, находящихся в блоке ячеек А2:D2 (108 – режим показа формул, 109 – режим вычислений).

Рис.108

Рис.109 2. Вычислить средние арифметические значения содержимого блока ячеек А1:А5 и блока ячеек В1:В5 (рис.110 – режим формул, рис.111 – режим решения).

Рис. 110

Рис.111

СРОТКЛ(число1; число2; …) – возвращает среднее абсолютных значений отклонений чисел от их среднего значения. Среднее отклонение вычисляется по формуле : 1 n ∑| ( X i − X ) | , n i =1

где X – среднее арифметическое, т – количество чисел. Например, СРОТКЛ(3;2;1;4;2;3;3) равняется 0,77551, т.е. (3+2+1+4+2+3+3)/7=18/7=2,571429; (|(3-2,57)| + |(2-2,57)| + |(1-2,57)| + |(4-2,57)| + |(2-2,57)| + |(3-2,57)| + |(3-2,57)|) = 0,77551 (рис. 109, 110). 3. Вычислить среднее отклонение чисел 3, 2, 1, 4, 2, 3, 3 (112 – режим формул, рис.113 – режим решения).

Рис. 112

Рис.113 3.5. Логические функции Excel содержит разные логические функции. Некоторые из них проверяют условия, в зависимости от которых выполняются те или иные действия. Условие представляет собой математическое выражение, в котором сравниваются две величины: числа или числовые значения формул, текстовые строки, логические значения. Результатом проверки условия является логическое значение ИСТИНА (условие выполняется) или ЛОЖЬ (условие не выполняется). Рассмотрим некоторые из них. Функции проверки условия Функция ЕСЛИ(логическое выражение; значение да; значение нет). Эта функция выполняет проверку условия, задаваемого первым аргументом. Если условие выполняется (ИСТИНА), то значением функции будет значение да, если не выполняется - то значением функции будет значение нет. Рассмотрим примеры: =ЕСЛИ(А6<0;15;25) Если значение содержимого ячейки А6 будет меньше 0, то результат вычисления равен 15, в противном случае (т.е. если значение содержимого ячейки А6>=0) результат равен 25. В аргументах функции можно использовать другие функции, например, =ЕСЛИ(СУММ(D1:D6)>0; СУММ(D1:D6);0)

Результатом выполнения этой функции будет сумма значений содержимого ячеек D1:D10, если эта сумма положительная, и нулю в противном случае, то есть если эта сумма отрицательная или равна нулю (рис.114, 115).

Рис.114

Рис.115 Аргументами функции, а также результатом выполнения функции могут быть текстовые константы. Например, ==ЕСЛИ(В5>100; “Принять”;”Отказать”) Если содержимое ячейки больше 100, то результатом выполнения функции будет значение “Принять”, в противном случае - ”Отказать” (рис.116, 117).

Рис.116

Рис.117 Текстовые значения могут быть и в логическом выражении: =ЕСЛИ(В10=”отлично”;”100%”;”70%”) Функции И, ИЛИ, НЕ. =И(логич. выражение1; логич. выражение2; … ) =ИЛИ(логич. выражение1; логич. выражение2; … ) =НЕ(логич. выражение) Эти функции дают возможность строить сложные логические выражения. Функции И() и ИЛИ() могут иметь в качестве аргументов до 30 логических выражений, функция НЕ() – только один аргумент. Аргументами этих функций могут быть логические значения, массивы, адреса ячеек. Результаты, получаемые с помощью функций И() и ИЛИ() существенно отличаются. Рассмотрим примеры: =ЕСЛИ(И(А3>0; D3>0); “Решение есть”; “Решения нет”)) =ЕСЛИ(ИЛИ(А3<0; D3<0); “Решения нет”; “Решение есть”)) В первом случае: если и содержимое ячейки А3>0, и содержимое ячейки D3>0, результатом будет “Решение есть”, если содержимое хотя бы одной из ячеек (А3 или В3) <=0, результатом будет - “Решения нет” (рис. 118, 119, 120).

Рис.118

Рис.119

Рис.120 Примечание. Формулу можно сделать короче, если вместо текстовых констант ввести ссылки на них (рис.121).

Рис. 121 Во втором случае: если или содержимое ячейки А3<0, или содержимое ячейки D3<0, результатом будет “Решения нет”, в противном случае Решение есть (рис.122, 123, 124).

Рис. 122

Рис. 123

Рис.124 Часто вместо функций И, ИЛИ нагляднее использовать операторы умножения и сложения. Например, рассматриваемые формулы можно записать: =ЕСЛИ(А3>0*D3>0; “Решение есть”; “Решения нет”)) (рис.125) =ЕСЛИ((А3<0)+(D3<0); “Решения нет”; “Решение есть”)) (рис.126).

Рис.125

Рис.126 Функция НЕ() выдает логическое значение противоположное логическому значению своего аргумента:

=НЕ(А1=А2) эквивалентна =А1<>A2 =НЕ(А1>А2) эквивалентна =А1<=A2 Например =НЕ(2*2=4) вернет ЛОЖЬ, так как условие 2*2=4 истинно (рис. 127). =НЕ(2*2=5) вернет ИСТИНА, так как условие 2*2=5 ложно (рис.128).

Рис. 127

Рис. 128

Вложение функций ЕСЛИ() Функции ЕСЛИ() могут вкладываться друг в друга. Можно вложить друг в друга в качестве второго и третьего аргументов до 7 функций ЕСЛИ(). =ЕСЛИ(В10=25; “Отлично”; ЕСЛИ(И(В10<25;В10>22); “Хорошо”; ЕСЛИ(И(В10<=22;B10>19); “Удовлетворительно”; “Неудовлетворительно”))) Выполняется функция следующим образом: если число, находящееся в ячейке В10, равно 25, то значением функции будет “Отлично”; иначе – если число, находящееся в ячейке В10 меньше 25, но больше 22, то функция примет значение “Хорошо”, иначе – если В10 меньше или равно 22 и больше 19, функция примет значение “Удовлетворительно”, иначе “Неудовлетворительно” (рис.129, 130, 131).

Рис.129

Рис.130

Рис.131 Следует иметь в виду, что вложенных функций ЕСЛИ() должно быть на единицу меньше, чем возможных вариантов принимаемых значений. Иногда аргументы логических функций становятся громоздкими, если приходится проверять выполнение одного и того же условия элементами блоков ячеек, например: =ЕСЛИ(И(А4>=B2;B4>=B2;C4>=B2;D4>=B2;E4>=B2;F4>=B2); “Да”; “Нет”) (рис. 132) Условие можно записать короче, если в аргументе функции использовать блок ячеек: =ЕСЛИ(И(А4:F4>=B2); “Да”; “Нет”) (рис. 133).

Рис. 132

Рис. 133 3.6. Функции даты и времени

В Excel имеются функции даты и времени. Рассмотрим некоторые из них. СЕГОДНЯ() – используется для получения числового значения текущей даты (текущей системной даты компьютера). При обращении к этой функции аргументы не нужны, но круглые скобки должны быть обязательно. Функция незаменима, когда в документах или в расчетах используется текущая дата. Например, = СЕГОДНЯ() результатом выполнения этой функции 24 октября 2000 года будет 24.10.00 Функция ТДАТА() – дает возможность получить текущую (системную) дату и время. Аргументы у этой функции отсутствуют. Результатом выполнения данной функции является значение текущей даты и времени. Например, = ТДАТА() результатом выполнения этой функции 24 октября 2000 года в 12 часов 43 минуты будет 24.10.00 12:43 ДАТА(год; месяц; день) – возвращает дату из отдельных ее компонент, полученных, возможно, в результате вычислений. Например, =ДАТА(2003;03;01) – возвращает 01.03.2003. ДАТАЗНАЧ(текст) – преобразует текстовую форму даты в числовую. Например, =ДАТАЗНАЧ(“01.03.2003”) – возвращает 01.03.2003. ДЕНЬНЕД(дата; тип) - используется для вычисления порядкового номера дня (от 1 до 7) недели, соответствующей заданной дате. Аргумент дата может быть датой в числовом формате, ссылкой на ячейку, содержащую дату в числовом или текстовом формате, функцию даты. Необязательный аргумент тип дает возможность выбрать желаемый порядок нумерации дней недели. Если этот аргумент равен 1 или отсутствует, то первым днем недели является воскресенье, а последним – суббота. Если тип равен 2, то первым днем считается понедельник, а последним - воскресенье. Если тип равен 3, то нумерация дней недели начинается с нуля, причем 0 соответствует понедельнику, а 6 – воскресенью. Например, пусть в ячейке С5 записана дата 27.01.00, тогда при выполнении оператора =ДЕНЬНЕД(С5) появится значение 5, что соответствует четвергу.

ВРЕМЯ(часы; минуты; секунды) – возвращает дату в числовом формате для заданного времени: десятичную дробь в интервале от 0 до 0,99999999, представляющую время от 0:00:00 (12:00:00 ночи до23:59:59 (11:59:59 вечера). Время(часы; минуты; секунды). Аргумент секунды необязателен, но точку с запятой после аргумента минуты опускать нельзя. При обращении к функции ВРЕМЯ надо придерживаться 24-часового формата, т.е. время 3 часа 15 минут дня (2:15 РМ) следует задавать в виде =ВРЕМЯ(14;15;) Значения аргументов могут вычисляться непосредственно функции. Например, если потребуется найти, время, которое было 35 секундами раньше времени17:0:14, то можно воспользоваться функцией =ВРЕМЯ(17; 0; 14-35) и функция вернет результат 16:59:39. Дату и время можно использовать в формулах и функциях так же, как обычные числа. Предположим, нужно найти дату через 48 дней после5 июля 2003 года. Пусть начальная дата находится в ячейке А1, тогда по формуле =А1+48 будет найдена новая дата, которая составит 37855 (в ОБЩЕМ формате). Если выбрать формат ДАТА, можно получить, например, 22.08.03 (рис. 134). Пусть требуется определить количество дней между датами 05.07.03 и 22.08.03. Запишем в ячейку В6 формулу =В3-А2, получим 48 (в общем формате) (рис.135).

Рис. 134

Рис. 135

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

Таблица 1

Отрасли 1 2 3

Объемы производства отраслей 600 1000 800

Производственное потребление отраслей за предыдущий период 1 250 150 0

2 100 500 300

3 160 0 400

Прогнозируемый конечный спрос 2000 2000 3000

Определить конечную продукцию каждой из отраслей за предыдущий период и план выпуска продукции в следующем периоде считая, что технология производства не изменилась. Приведем математическую постановку задачи. Для решения поставленной задачи можно использовать балансовую модель Леонтьева. Она представляет собой систему уравнений, каждое из которых выражает требование равенства (баланса) между количеством продукции, производимой отдельным экономическим объектом, и совокупной потребностью в этой продукции. В рассматриваемой задаче экономическая система состоит из трех отраслей. Пусть Хi – величина, равная суммарному выпуску продукции отрасли i xij – количество продукции отрасли i, необходимое для того, чтобы отрасль j произвела Xj единиц своей продукции; Yi – конечная продукция отрасли i. Тогда взаимосвязь отраслей в процессе производства и потребления отдельного продукта Xi (i=1,2,3) может быть описана в виде следующих уравнений: Х1 = х11 + х12 + х12 + Y1; Х2 = х21 + х22 + х22 + Y2; (1) Х3 = х31 + х32 + х32 + Y3. Используем понятие коэффициентов прямых затрат (технологического коэффициента) аij:

aij =

xij Xj - количество продукции отрасли i, необходимое для того, чтобы

отрасль j произвела одну единицу своей продукции. Тогда xij=aijXj и система уравнений будет иметь следующий вид: Х1=а11Х1+а12Х2+а12Х3+Y1; Х2=а21Х1+а22Х2+а22Х3+Y2; Х3=а31Х1+а32Х2+а32Х3+Y3.

Или в матричной форме Хт = АXт + Yт ⎛ a11 a12 a13 ⎞ ⎜ ⎟ Где А = ⎜ a 21 a 22 a 23⎟ ⎜ a31 a32 a33 ⎟ ⎝ ⎠

(2)

- матрица прямых затрат.

Хт – вектор-столбец, полученный из вектора Х=(Х1,Х2,Х3) - вектора выпуска продукции в предыдущем периоде, после его транспонирования; Yт – вектор-столбец, полученный из вектора Y=(Y1,Y2,Y3) - вектора конечного спроса в предыдущем периоде, после его транспонирования. Решение задачи По условию задачи известны объемы производства каждой из отраслей за предыдущий период (суммарный выпуск продукции отрасли i): X1=600, X2=1000, X3=800 и значения xij (i,j=1,2,3): х11 = 250 х21 = 150 х31 = 0

х12 = 100 х22 = 500 х32 = 300

х13 = 160 х23 = 0 х33 = 400

Отсюда, согласно (1) можно определить значения Yi, i=1,2,3 конечной продукции каждой из отраслей за предыдущий период: Y1 = 600 – 250 –100 -160 = 90; Y2 = 1000 – 150 – 500 – 0 = 350 Y3 = 800 – 0 – 300 – 400 = 100. Таким образом, вектор конечной продукции за предыдущий период найден Y=(90, 350, 100). Для определения вектора выпуска продукции Х при заданном конечном прогнозируемом векторе спроса Y=(90, 350, 100) надо решить систему уравнений, из которой следует, что Хт = (Е-А)-1 + Yт, где, Е – единичная матрица. S=(Е-А)-1 – называется матрицей полных затрат. Учитывая, что технология производства не изменилась, определим коэффициенты прямых затрат aij:

a11 =

250 = 0,417 600

a12 =

100 = 0,1 1000

a13 =

160 = 0,2 800

a 21 =

150 = 0,25 600

a 22 =

0 =0 600

a 32 =

a 31 =

500 = 0,5 1000

300 = 0,3 1000

a 23 = a33 =

0 =0 8000

400 = 0,5 800

Таким образом, матрица коэффициентов прямых затрат ⎛ 0,417 0,1 ⎜ будет иметь вид A = ⎜ 0,25 0,5 ⎜ 0 ⎝

0 ⎞ ⎟ 0 ⎟ 0,3 0,5 ⎟⎠

Все элементы матрицы А неотрицательные, т.е. A>0. Для того чтобы система уравнений (1) имела единственное неотрицательное решение при любом неотрицательном векторе Y, необходимо, чтобы матрица А была продуктивной. Экономический смысл продуктивности состоит в том, что существует такой план выпуска продукции, при котором каждая отрасль сможет произвести некоторое количество конечной продукции. Известно, что для продуктивности матрицы А>0 необходимо и достаточно, чтобы все главные миноры матрицы (Е-А) были положительными числами, строго меньше единицы. Кроме того, если сумма элементов каждого из столбцов неотрицательной квадратной матрицы А положительна и строго меньше единицы, то все главные миноры матрицы (Е-А) положительны и строго меньше единицы. Суммы элементов каждого столбца матрицы А соответственно равны: 0,417 + 0,25 + 0 = 0,667 < 1 0,1 + 0,5 + 0,3 = 0,9 <1 0,2 + 0 + 0,5 = 0,7 <1 Следовательно, в силу сказанного выше, матрица А продуктивна, выражение (2) имеет смысл и вектор Y неотрицателен. Следовательно, для нахождения плана выпуска продукции Х можно воспользоваться формулой (2). Вычислим матрицу (Е-А): ⎛ 0,583 − 0,1 0,2 ⎞ ⎜ ⎟ (E-A) = ⎜ − 0,25 0,5 0 ⎟ ⎜ 0 − 0,3 0,5 ⎟⎠ ⎝

Для вычисления обратной матрицы воспользуемся формулой: Т 1 [Bij ] , B = det B −1

где [Bij] – присоединенная матрица,

Вij – алгебраические дополнения элементов ij матрицы. Вij = (-1)i+j Mij

,

где Мi – минор элемента Вij. Вычислим значения алгебраических дополнений элементов матрицы (ЕА). Обозначим для простоты описания вычислений Е-А=В

0,5 0 = 0,25 − 0,3 0,5 1+ 2 − 0,25 0 B12 = (−1) = 0,125 0 0,5 1+ 3 0,25 0,5 B13 = (−1) = 0,075 0 − 0,3 B11 = (−1)

B 31 = (−1)

1+1

3+1

− 0,1 − 0,2 = 0,1 0,5 0

− 0,1 − 0,2 = 0,11 − 0,3 0,5 2 + 2 0,583 − 0,2 B 22 = (−1) = 0,291 0 0,5 2 + 3 0,583 − 0,1 B 23 = (−1) = 0,175 0 − 0,3 B 21 = (−1)

B 32 = (−1)

B 33 = (−1)

3+ 3

2 +1

3+ 2

0,583 0,2 = 0,05 − 0,25 0

0,583 − 0,1 = 0,267 − 0,25 0,5

⎛ 0,25 0,125 0,075 ⎞ ⎜ ⎟ Таким образом, [E-A] = Bij = ⎜ 0,11 0,291 0,175 ⎟ . ⎜ 0,1 0,05 0,267 ⎟ ⎝ ⎠

T

Тогда, [E-A] = Bij

T

0,1 ⎞ ⎛ 0,25 0,11 ⎜ ⎟ = ⎜ 0,125 0,291 0,05 ⎟ ⎜ 0,075 0,175 0,267 ⎟ ⎝ ⎠

Det (E-A) = 0,583·0,5·0,5-(-0,1) · (-0,25) ·0,5+(-0,2) ·(-0.25)(-0,3) = 0,118.

)-1

S = (E-A

1 T = B = det B [Bij ] = -1

⎛ 0,25 0,11 0,1 ⎞ ⎛0,218 0,93 0,847⎞ ⎟ ⎜ ⎟ ⎜ 1 ⎜ 0,125 0,291 0,05 ⎟ ⎜1,059 2,466 0,424⎟ = 0,118 ⎜ ⎟ ⎜ ⎟ ⎝0,075 0,175 0,276⎠ ⎝0,635 1,479 2,253⎠

Зная S и Y, вычислим Х: Х1 =2,118 · 2000 + 0,093 · 2000 + 0,847 · 3000 = 8657

Х2 = 1,059 · 2000 + 2,466 · 2000 + 0,424 · 3000 = 8322 Х3 = 0,635 · 2000 + 1,479 · 2000 + 1,234 · 3000 = 10987 Таким образом, вектор выпуска продукции в следующем периоде при заданном векторе конечной продукции Y = (2000, 2000, 3000) равен Х = (8657, 8322, 10987). Используя матричные функции, реализуем балансовую модель в Excel (рис.136 – режим формул, рис.137 – режим решения).

Рис.136

Рис.136 (продолжение)

Рис.137 Задания Откройте рабочую книгу Excel, назовите ее Формулы и функции, дайте листу книги имя Конус и цилиндр.

1. Запишите формулы для вычисления площади боковой и полной поверхности цилиндра и его объема при заданных R и H по формулам: Sбок = 2πRH, Sцил = 2πRH + 2πR2, Vцил = πR2H. Введите в свободные ячейки поясняющий текст. 2. Сместитесь по листу вниз. Запишите формулы для вычисления образующей, площади боковой и полной поверхности конуса и его объема при заданных R и L по формулам: Lобр =

R2 + H 2

1 3

, Sбок = πRL, Sкон = πR(R+L), V= πR2H.

Введите в свободные ячейки поясняющий текст. 3. Перейдите на другой лист. Дайте ему имя Функции. Введите в блок ячеек B2:F2 числа 4, 2, 3, 1, 5, а в блок ячеек B3:F3 числа 1, 0, 0, 1, 1. Введите в ячейки: G2 – формулу для вычисления суммы содержимого ячеек B2:F2. H2 – формулу для вычисления произведения содержимого ячеек B2:F2. G4 – формулу для вычисления суммы произведений содержимого ячеек B2:F2 и B3:F3. G5 – формулу для вычисления корня квадратного из суммы произведений содержимого ячеек B2:F2 и B3:F3. G6 – формулу для нахождения минимального элемента содержимого блока ячеек B2:F2. G7 – формулу для нахождения максимального элемента содержимого блока ячеек B2:F2. G8– формулу для нахождения минимального элемента содержимого блока ячеек B2:F2. G9 – формулу для нахождения среднего арифметического содержимого блока ячеек B2:F2. Используйте для вычисления соответствующие функции. Введите в свободные ячейки поясняющий текст. 4. Перейдите на другой лист. Дайте ему имя Размещения и сочетания. Введите в ячейку D10 формулу для вычисления числа размещений из n элементов по m, используя формулу:

A mn =

n! (n − m)! , где n, m – целые, n, m >=0, m<=n.

Введите в ячейку D13 формулу для вычисления числа сочетаний из n элементов по m, используя формулу:

C mn =

n! m! (n − m)! , где n, m – целые,

n, m >=0, m<=n. Используйте функцию для вычисления факториала. В ячейки B10 и В11 введите значения n и m. Введите в свободные ячейки поясняющий текст. 5. Создайте таблицу для решения системы уравнений ⎧ 4 x1 + 22 + 6 x3 = 7.9 ⎪ ⎨ 2 x1 + 6 x2 + 8 x3 = 3.5 . ⎪6 x + 8 x + 18 x = 11.1 2 3 ⎩ 1

1. Перейдите на другой лист и дайте ему имя Логические функции. Введите в ячейку В2 формулу, с помощью которой можно вычислять значения функции Y=

3Х 7 7-Х

при Х< 0, при 0 ≤ Х < 7, при Х ≥ 7.

Ячейку А2 отведите для ввода значения переменной Х. Проверьте правильность написания формулы, вводя в ячейку значения Х, принадлежащие разным интервалам. Введите в свободные ячейки поясняющий текст. Введите в блок ячеек В5:Е5 какие-нибудь числа. Введите в ячейку А6 формулу, позволяющую проверить, действительно ли содержимое ячейки В5 больше остальных. Если содержимое ячейки В5 больше содержимого других ячеек этого блока, выведите «Да», в противном случае выведите «Нет». Измените содержимое ячеек так, чтобы можно было проверить другой вариант ответа. Перейдите на другой лист и дайте ему имя Премия. Введите в ячейку: А1 – текст «Список сотрудников отдела», С3 – «Месяц:», D3 – «март», А5 – символ №, В5 – текст «Фамилия И.О.» С5 – «Пол», D5 – «Оклад (руб.), Е5 – «Премия».

В ведите в блок ячеек А6:А10 порядковые номера сотрудников, в блок ячеек В6:В10 – фамилии и инициалы сотрудников, в блок ячеек С6:С10 – их пол, В блок ячеек D6:D10 – введите формулы для начисления премии женщинам к празднику 8 марта, если она составляет 10% от оклада. Формулу введите сначала в ячейку Е6, а затем скопируйте в ячейки Е7:Е10. Адрес ячейки D3 сделайте абсолютным, так как при копировании он не должен меняться. Если все сделано правильно, содержимое ячеек должно быть таким, как показано на рисунке 138.

Рис.138 – Перейдите на другой лист и назовите его Скидки. Создайте документ для расчета суммы, которую должен заплатить покупатель за товар с учетом скидок. Если покупатель предъявляет чеки на купленные в данном магазине товары на сумму более 300$ за квартал, он получает скидку на покупаемый в дальнейшем товар в размере 3% от его стоимости, если предъявляет чеки на сумму более 1000$ – 5%.

Глава 4. Основные приемы форматирования При оформлении таблицы можно менять оформление самой ячейки (обрамление, заливку) и стилевое оформление данных (выравнивание данных, шрифт, размер шрифта, начертание и т.д.). Эффективное использование шрифтов, выделение границ и использование цветов представляет информацию на рабочих листах более понятной и удобной. Цвет заливки выделяет часть рабочего листа, заставляет обратить на нее внимание. Границы позволяют заключить в рамку или подчеркнуть ячейку или ячейки выбранной области. 4.1. Использование главного меню Для заливки ячеек следует выбрать команду Формат – Ячейки – вкладка Вид. Появится диалоговое окно вкладки Вид, где можно задать требуемые параметры заливки (рис.139).

Рис. 139 Для задания типа обрамления следует выбрать команду Формат – Ячейки – вкладка Граница. Появится диалоговое окно вкладки Граница, где можно задать требуемые параметры обрамления (рис. 140). Для задания стилевого оформления ячейки необходимо выбрать команду Формат – Ячейки – вкладка Шрифт (рис.141), в окне которой можно установить тип гарнитуры шрифта, а затем команду Формат – Ячейки – вкладка Выравнивание (рис. 142), в окне которой установить нужное выравнивание.

Рис.140

Рис.141

Рис. 142 Обратите внимание на флажок «переносить по словам». Если он включен, то текст в ячейке переносится на другую строку при достижении ее края. Флажок автоподбор ширины автоматически уменьшает размер символов так, чтобы они уменьшались в ширину столбца. С помощью флажка объединение ячеек можно две или более ячейки слить в одну (рис.143). Обратное преобразование осуществляется сбросом флажка.

Рис. 143 По умолчанию в Excel текст выравнивается по левому краю, а числа по правому. Можно изменить заданные установки и выровнять текст и числа по левому краю, по правому краю или по центру. Для этого следует раскрыть список поля Выравнивание по горизонтали (рис.144) и выбрать нужный пункт. В Excel предусмотрены следующие типы выравнивания: по значению – текст выравнивается по левому, а числа по правому краю (задается по умолчанию), по левому краю (отступ) – выравнивает содержимое ячейки по левому краю (можно указать число отступов в счетчике Отступ),

Рис. 144 по центру – выравнивает содержимое ячейки по центру, по правому краю – выравнивает содержимое ячейки по правому краю, с заполнением – заполняет ячейку символами содержимого ячейки, повторяя их по ширине, по ширине – выравнивает текст внутри ячейки так, чтобы все строки заполняли ячейку по ширине, по центру выделения – применяется к диапазону ячеек. Содержимое самой левой ячейки выравнивается по всему диапазону. В Excel можно производить выравнивание содержимого ячейки по вертикали. Для этого следует раскрыть список поля Выравнивание по вертикали (рис. 145) и выбрать нужный пункт. В Excel предусмотрены следующие типы выравнивания по вертикали: по нижнему краю – (происходит по умолчанию), по верхнему краю, по центру, по высоте – увеличивает межстрочное расстояние до заполнения ячейки (рис. 146).

Рис. 145

Рис. 146 Excel позволяет изменять ориентацию текста по вертикали и по горизонтали. Чтобы расположить тест по вертикали, надо щелкнуть по полю с вертикально расположенным словом Текст. Для поворота текста следует использовать счетчик, задающий величину поворота в градусах, или перетащить индикатор – слово Надпись (рис.145, 147).

Рис. 147 4.2. Использование панели инструментов Для форматирования ячеек часто удобно использовать кнопки панели инструментов Форматирование (рис. 148).

Рис. 148 При остановке курсора мыши на любой из кнопок появляются всплывающие подсказки, разъясняющие их значение. – кнопка Шрифт, с помощью которой можно установить тип гарнитуры шрифта. Для этого следует нажать кнопку списка (маленький черный треугольник) и в появившемся меню выбрать шрифт требуемой гарнитуры (рис. 149).

Рис. 149 – кнопка Размер. Используется для выбора размера шрифта. Для этого следует щелкнуть по кнопке списка и выбрать в появившемся списке нужный размер шрифта (рис.150). Размер шрифта измеряется в пунктах.

Рис. 150 – кнопки Полужирный, Курсив, Подчеркнутый. Эти кнопки используются для выбора варианта начертания. – кнопки, позволяющие выбрать тип выравнивания По левому краю, По центру, По правому краю. – кнопка Объединить и поместить в центре. Позволяет объединить выделенные ячейки и поместить текст в центре объединенных ячеек. – кнопка Границы. Для выбора типа границ следует щелкнуть по кнопке списка и в появившейся палитре (рис.151) выбрать нужную границу. Кнопки данной палитры позволяют заключить в рамку или подчеркнуть ячейки выбранной области.

Рис. 151 – кнопка Цвет заливки.

Рис. 152 – кнопка Цвет шрифта.

Рис. 153 Кнопки Цвет заливки (рис. 152) и Цвет шрифта (рис.153) используются аналогично кнопки Границы. Для форматирования ячеек можно использовать контекстное меню. 4.3. Форматирование строк и столбцов При форматировании строк и столбцов можно изменять их ширину и высоту, скрывать и вновь отображать их на экране. Для изменения ширины строки или столбца надо установить курсор в нужную строку или столбец и выбрать команду Формат – Строка (Столбец) (рис. 154,155). Появится окно (рис. 156), в котором следует установить требуемую ширину строки (столбца).

Рис. 154

Рис. 155

Рис. 156 Нужно помнить, что высота строк измеряется в пунктах, а ширина столбцов – в сантиметрах. Увеличить ширину строки (столбца ) можно еще и следующим образом: установить курсор на границу строк (столбцов) в области выделения, нажать левую кнопку мыши и перетащить ее в сторону увеличения или уменьшения. Если требуется убрать на время какие-нибудь строки (столбцы), их можно скрыть. Для этого надо выделить сроку (столбец), выбрать команду Формат – Строка (Столбец) – Скрыть. Скрытые строки и столбцы не удаляются с экрана, а просто становятся невидимыми. Чтобы отобразить их, следует выделить соседнюю с ними строку (столбец) и выполнить команду Формат – Строка (Столбец) – Отобразить.

Примечание. Скрывать и отображать можно целые рабочие листы. Для этого следует выбрать команду Формат – Лист. Перед тем как отобразить рабочий лист, Excel спросит его имя. 4.4. Числовые форматы Excel позволяет представлять числа в разных форматах. Для форматирования можно использовать главное меню, панель инструментов Форматирование и контекстное меню. При форматировании меняется представление числа, а не его значение. По умолчанию используется формат Общий, в котором не отображаются незначащие нули. Например, Excel отобразит число 017, 80 как 17,8. Excel хранит числа с точностью до 15 разрядов, при выводе десятичных дробей на экране отображаются только 9 разрядов после запятой. Например, введем в ячейку число 0,9876543210123456789 и нажмем клавишу Enter. Активизируем ячейку А1. В ячейке А1 будет отображено число 0,987654321, а в строке формул 0,987654321012345 (рис. 157).

Рис. 157 Число, показанное в строке формул – внутреннее представление дроби. Последние цифры: 6, 7, 8, 9 (то есть разряды 16,17, 18, 19 ) потерялись, так как число хранится с точностью до 15 знаков после запятой. В самой же ячейке отображаются только 9 разрядов. Введем в ячейку В2 число 987654321,123456789. активизируем ячейку В2. В самой ячейке В2 будет отображено число 987654321,1, а в строке формул – число 987654321,123456 (рис.158). Последние числа 7,8,9 потерялись, то есть хранятся 15 разрядов, а в ячейке В2 отображается только один разряд дробной части, хотя все вычисления будут выполняться с числом, представленным в строке формул.

Рис. 158

Рис. 159 Для представления числа в нужном формате требуется активизировать ячейку (ячейки) и выбрать команду Формат – Ячейки – вкладка Число. Появится диалоговое окно вкладки Число. Синей подсветкой будет выделен тип формата, по которому отформатировано число активной ячейки (ячеек) (рис.159). Для рассмотренного примера им будет формат Общий, назначенный по умолчанию (пригодный как для числовых, так и для текстовых данных). В поле Числовые форматы перечислены форматы, которые могу быть использованы для представления данных. Выберем формат числовой (рис. 160). В поле Число десятичных разрядов можно установить требуемую точность представления десятичного числа (установить число разрядов после запятой).

По умолчанию задано 2 разряда. В поле Образец будет показан образец числа в выбранном формате. Установим три разряда после запятой. Содержимое ячейки А1 будет иметь вид (рис. 161)

Рис. 160

Рис. 161

Рис. 162 Определим для ячейки В2 следующий формат 3 разряда после запятой, а целую часть разбить через пробелы по три цифры. Для этого установим в поле Число десятичных знаков 3 разряда после запятой и установим флажок Разделитель групп разрядов. В результате форматирования получим (рис. 162). Введем во все ячейки блока А2:G2 число 14. Представим эти числа в различных форматах, например, в общем, числовом, денежном (в рублях, долларах), финансовом, в формате даты, времени, процентном, экспоненциальном. Тогда будем иметь (рис. 163).Суть введенного значения не изменилась – во все ячейки введено число 14, а вид изменился существенно.

Рис. 163 Общий формат – задается по умолчанию. Числовой формат – аналогичен формату Общий, но можно дополнительно установить число десятичных знаков после запятой, разделитель групп разрядов, метод представления отрицательных чисел. Денежный формат – позволяет представлять число со знаком рубля после последней цифры, со знаком доллара перед первой цифрой и с другими денежными знаками. Финансовый формат – позволяет производить выравнивание денежных величин по разделителю целой и дробной части, нулевое значение отображается прочерком. Процентный формат – отображает число в процентах. Экспоненциальный формат – представляет число в экспоненциальном виде. Например, в экспоненциальном виде число 14 = 1,4·10+01. Форматы даты и времени позволяют представлять число Формат Дополнительный применяют для представления чисел, не имеющих математического значения, например, почтовый индекс, телефонный номер, номер паспорта. Над этими числами нельзя производить математические операции, они используются только для представления.

Текстовый формат преобразует число в текстовое представление. Оно перестает быть числом и не может использоваться в вычислениях. Форматы Даты и Времени позволяют представлять число в виде даты или времени. Для создания даты и времени в Excel используются специальные коды. Если не включена одна из версий кода АМ/РМ, Excel представляет время в 24-часовом представлении. Для форматирования чисел удобно использовать кнопки панели инструментов Форматирование (рис. 148). – кнопка Денежный формат. Отображает число с символом валюты (в русской версии Excel – рубль), использует пробел как разделитель между разрядами, десятичную запятую и два десятичных разряда. Выравнивает числа по знаку денежной единицы и по десятичной точке. – кнопка Процентный формат. Отображает число в виде процентов. – кнопка Формат с разделителями. Отображает число как денежный формат, но без символа валюты. – кнопка Увеличить разрядность. В дробной части десятичного числа отображает на один знак больше. – кнопка Уменьшить разрядность. В дробной части десятичного числа отображает на один знак меньше. 4.5. Условное форматирование Условное форматирование позволяет задавать или сбрасывать форматирование ячеек в зависимости от вводимых в них данных. Например, надо в блоке ячеек А1:F1 выделять цветом и начертанием вводимые числа, находящиеся в диапазоне между 18 и 25. Чтобы установить такое условие форматирования для блока заданного блока ячеек, надо выделить этот блок и выбрать команду Формат – Условное форматирование (рис. 164).

Рис.164 Появится диалоговое окно Условное форматирование. В поле значение можно выбрать пункт Значение или Формула. Выберем Значение (рис. 165). Откроем список в следующем поле и выберем нужное значение условия между (рис. 166). В следующие два поля введем соответственно 18 и 25. Нажмем кнопку Формат. Диалоговое окно Формат ячеек (рис. 167).

Рис.1 65

Рис. 166

Рис. 167 Выберем начертание шрифта и цвет. Нажмем ОК и в появившемся окне ОК. Введем в блок ячеек А1:F1 числа. Введенные числа, принадлежащие интервалу 18–25, будут окрашены в выбранный (синий) цвет и будут написаны курсивом (рис. 168).

Рис. 168

Глава 5. Примеры создания электронных документов 5.1. Пример 1 Создать электронный документ «Счет за ремонт автомашины». В нем должны быть перечислены виды произведенных работ, их стоимость, предусмотрено вычисление стоимости всех выполненных работ, налога на добавочную стоимость, составляющего 20% от стоимости работ, спецналога, составляющего 1,5% от стоимости работ, суммы, подлежащей оплате. Выполните следующие действия: 1. Откройте новый лист рабочей книги и дайте ему имя «Счет». 2. Введите в таблицу данные: в ячейки: А1 – текст «Счет за ремонт автомашины», А3 – символ №., В3 – текст «Наименование работ», С3 – текст «Стоимость работ», в ячейки А4, А5, А6 введите соответственно числа 1, 2, 3 (номера видов работ по порядку). 3. Введите наименования работ в ячейки: В4 – «Регулировка зажигания», В5 – «Замена диска сцепления», В6 – «Регулировка тормозов» и стоимости работ в ячейки: С4 – 30, С5 – 75, С6 – 44. 4. Введите текстовые данные в ячейки: В7 – «Итого:» В8 – «Ндс:» В9 – «Спец. налог:» В10 – «К оплате:» 5. Введите формулы в ячейки: С7 – =СУММ(С4:С6) С8 – =С7*20% С9 – =С7*1,5% С10 – =СУММ(С7:С9).

Рис. 169

Рис. 170 5/2. Отформатируйте документ «Счет». Измените ширину столбцов, так как столбец с именем № слишком широкий для хранящихся в нем данных, а столбец «Стоимость работ» закрывает собой столбец «Наименование работ». Для уменьшения ширины столбца А установите указатель мыши на границе столбцов в строке имен (бордюре) и, нажав левую кнопку мыши, перетащите границу столбца влево до ширины символа №. Подгоните ширину столбцов В и С. Для этого выделите столбцы В и С и выберите команду Формат – Столбец – Автоподбор ширины. Ширина столбцов изменится так, чтобы в столбцах уместился самый длинный заголовок. Для выбора гарнитуры шрифта, размера, начертания и выравнивания используйте кнопки панели инструментов. Оставьте предлагаемый по умолчанию вид шрифта Arial. Для заголовка документа установите полужирное начертание и размер шрифта, равный 14,

для заголовков столбцов – полужирное начертание и размер шрифта, равный 12, для остальных данных – размер данных, равный 12 и обычное начертание. После изменения шрифта, возможно, следует опять осуществить подгонку ширины столбцов. Объедините ячейки блока А1:С1 и поместите заголовки документа в центре. Для этого выделите блок ячеек А1:С1 и нажмите кнопку Объединить и поместить в центре. Для ячеек блока А3:С3 установите выравнивание по центру, для блока ячеек А4:А6, В7:В10 и С4:С10 – выравнивание по правому краю, для блока ячеек В4:В6 – выравнивание по левому краю. С помощью меню кнопки Граница выполните обрамление документа: выделите строки и столбцы блока ячеек А3:С11, нажав кнопку Все границы (рис. 171), а затем выделите А1:С11 и сделайте обрамление всего документа, нажав кнопку Толстая внешняя граница (172).

Рис. 171

Рис. 172

Выполните заливку блока ячеек А10:С10, так как именно в этом блоке помещена главная информация, сколько необходимо заплатить за ремонт автомашины. При заливке ячеек необходимо учитывать, на каком принтере будет печататься документ и не будет ли заливка ухудшать видимость текста. Предпочтение следует давать светлым тонам. Можно выделить блок ячеек А1:С11 и выполнить команду Сервис – Параметры – вкладка Вид – сбросить флажок Сетка. В столбце «Стоимость работы» не учтена единица измерения. Можно ввести ее в заголовок столбца, сделав его «Стоимость работы (руб.)», а можно назначить денежный формат ячейкам столбца, где записаны стоимости работ. Для второго случая следует выделить блок ячеек С4:С10 и, выполнив команду Формат – Ячейки – Денежный, выбрать пункт р. В результате получите бланк счета, представленный на рисунке 173 (режим решения) и рисунке 174 (режим формул).

Рис. 173

Рис. 174 6.2. Пример 2 Создать электронный документ «Прогноз финансовой деятельность предприятия». Исходными данными являются показатели производства предприятия на 2003г.: объем продаж (шт.), цена 1 шт. (руб.), расход. Прогнозные допущения на следующие пять лет: рост цен составит 5%, рост объема продаж – 4%. С учетом имеющихся данных требуется составить прогноз показателей производства предприятия на следующие пять лет: роста объема продаж, дохода, прибыли. Для вычисления дохода и прибыли используйте формулы: <доход> = <цена> · <объем продаж>,

<прибыль> = <доход> - <расход>. Так как рост объема продаж составляет 4%, то объем продаж, например, на 2004 год вычисляется по формуле < объем продаж (2004г)> = < объем продаж (2003г) > + < объем продаж (2003г) > ·4% = = [< объем продаж (2003г) > + 1] ·4%. Так как рост цен составляет 7%, то цена, например, на 2004 год вычисляется по формуле < цена (2004г)> = < цена (2003г) > + < цена (2003г) > · 7% = = [< цена (2003г) > + 1] · 7%. Выполните следующие действия: 1. Перейдите на новый рабочий лист и дайте ему имя «Прогноз». 2. Введите в ячейки таблицы текстовые данные. В ячейку: А1 – «Прогноз финансовой деятельности предприятия», А3 – «Год», А4 – «Показатели производства», А5 – «Объем продаж», А6 – «Цена (руб.)», А7 – «Доход (руб.)», А8 – «Расход (руб.)», А9 – «Рост цен(%)», А10 – «Рост объема продаж (%)», А11 – «Прибыль (руб.)». 3. В блок ячеек В3:G3 введите годы рассматриваемого периода: 2003, 2004, … , 2008. Используйте маркер заполнения для создания прогрессии. 4. В ячейки В5, В6, В8 введите показатели производства за 2003 год. 5. В ячейку В7 – формулу для вычисления дохода = В5*В6, а в ячейку В11 – формулу для вычисления прибыли = В7-В8. 6. В ячейку С5 введите формулу для вычисления объема продаж, прогнозируемого на 2004 год = В5*(1+$B$10), а в ячейку С6 введите формулу для вычисления цены, прогнозируемой на 2004 год = В6*(1+$B$9). В формулах используются абсолютные адреса ячеек $B$9 и $В$10, так как при копировании формул в другие ячейки они не должны меняться. Относительные адреса ячеек будут меняться при копировании, что нам и нужно. 7. Скопируйте содержимое ячейки С5 в блок ячеек D5:G5,

содержимое ячейки С6 в блок ячеек D6:G6, содержимое ячейки В7 в блок ячеек С7:G7, содержимое ячейки В11 в блок ячеек С11:G1. Для копирования используйте маркер заполнения. В режиме формул таблица будет иметь вид (рис.175), в режиме решения (рис.176). 8. Отформатируйте полученную таблицу как показано на рисунке 176.

Рис.175

Рис.176 5.3. Пример 3 Создать электронный документ отчета о продаже телевизоров торговой фирмой. В период с 1998 г. по 2002 г. было продано телевизоров (шт.): в 1998 г. – 187; в 1999 г. – 210; в 2000 г. – 234; в 2001 г. – 238; в 2002 г. – 240; в том числе продано телевизоров цветного изображения: в 1998 г. – 118; в 1999 г.– 136; в 2000 г. – 186; в 2001 г. – 204; в 2002 г. – 203. Определить: удельный вес телевизоров цветного изображения в общем объеме продаж; изменение объема продаж телевизоров по сравнению с 1998г.; на сколько увеличился объем продаж всех телевизоров и телевизоров цветного изображения в 2002 г. по сравнению с 1998г.

Удельный вес телевизоров цветного изображения в общем объеме продаж определяется по формуле: <число телевизоров цветного изображения> / <всего телевизоров>. Например, 1998 г.:

118 136 =0,6310 = 63,10%; 1999 г.: =0,6476 = 64,76%; 187 210

и т. д. Изменение объема продаж всех телевизоров по сравнению с 1998 г. определяется по формуле: <Продано телевизоров всего> / <Продано телевизоров в 1998 г.>. Например, 1998 г.:

187 210 =1 = 100%; 1999 г.: =0,1123 = 112,30%; и т. д. 187 187

Изменение объема продаж цветных телевизоров по сравнению с 1998 г. определяется следующим образом: <Продано цветных телевизоров > / <Продано цветных телевизоров в 1998 г.>. Например, 1998 г.:

118 136 =1 = 100%; 1999 г.: =0,11525 = 115,25%; и т. д. 118 118

<Увеличение объема продаж телевизоров в 2002 г. по сравнению с 1998 г.> = <объем продаж в 2002 г.> – <объем продаж в 1998 г.>, т.е. для всех телевизоров 128,34% - 100% = 28,34%. Постройте таблицу. 1. Откройте новый лист рабочей книги и дайте ему имя «Продажа телевизоров». 2. Введите в таблицу исходные данные и формулы для вычисления показателей, описанных выше: – в ячейку A1 введите текст «Продажа телевизоров». – в ячейку А3 – «Показатели». – в блок ячеек А5:А13 – заданные в условии показатели и вычисляемые.

– В блоки ячеек B3:F3 и B5:F6 – заданные в условии статистические данные. – В ячейку В7 введите формулу =В6/В5. 3. Скопируйте содержимое ячейки В7 в блок ячеек С7:F7, используя маркер заполнения. Относительные адреса, имеющиеся в формуле, при копировании изменятся – подстроятся на нужные для наших вычислений адреса ячеек. 4. Введите в ячейку В9 формулу =В5/$B$5. В числителе используйте относительный адрес ячейки, чтобы при копировании он подстроился под нужные адреса ячеек. В знаменателе – абсолютный адрес ячейки, так как он не должен меняться при копировании. 5. Скопируйте содержимое ячейки В9 в блок ячеек С9:F9. 6. Введите в ячейку В10 формулу =В6/$B$6. В числителе используйте относительный адрес ячейки, чтобы при копировании он подстроился под нужные адреса ячеек. В знаменателе – абсолютный адрес ячейки, так как он не должен меняться при копировании. 7. Скопируйте содержимое ячейки В10 в блок ячеек С10:F10. 8. Введите в ячейку В12 формулу = F9- B9, в ячейку В13 формулу = F10- B10. Таблица готова. 2. Отформатируйте таблицу. – Выделите ячейку А1. Оставьте предлагаемый по умолчанию вид шрифта Arial. Для заголовка документа установите полужирное начертание и размер шрифта, равный 14. Объедините ячейки блока А1:F1 и поместите заголовок документа в центре. Для этого выделите блок ячеек А1:F1 и нажмите кнопку Объединить и поместить в центре. – Так как текст, вводимый в ячейку А7, А12, А13 довольно длинный, целесообразно для этих ячеек установить формат Переносить по словам, выполнив команду Формат –Ячейки – вкладка Выравнивание – флажок Переносить по словам. – Для изменения ширины столбцов используйте или команду Автоподбор ширины или перетащите указателем мыши границы столбцов до нужной ширины – Для блоков ячеек А3:А3 и В3: F3 установите выравнивание по левому краю, для блока ячеек В5:F10 – выравнивание по правому краю, для блока ячеек В4:В6 – выравнивание по левому краю. – Для блоков ячеек В7:F7 и В9:F10 установите процентный формат. – Выполните обрамление документа как показано на рисунках 177 (режим решения) и 178 (режим формул).

– Выделите блок ячеек А1:F13 и выполните команду Сервис – Параметры – вкладка Вид – сбросьте флажок Сетка.

Рис. 177

Рис.178

Задания 1. Составьте таблицу «Показатели производства» на год. Известен план выпуска продукции и выполнение плана в каждом квартале. Определить процент выполнения план в каждом квартале и за весь год (рис.179).

Рис. 179 186 204 =0,7949 = 79,49%; 2001 г.: =0,8571 = 85,71%; 234 238 203 =0,8458 = 84,58%. 2002 г.: 240

2000 г.:

2. Составьте предприятия.

таблицу

расхода

топлива

на

производственные

нужды

Расчет топлива на производственные нужды предприятия характеризуется в отчетном периоде следующими данными (рис.180):

Рис. 180 Определить: общее потребление условного топлива по плану и фактически; процент выполнения плана по общему расходу топлива; удельные веса фактически израсходованного топлива по видам (расчет топлива до о,1%). Средние калорийные коэффициенты перевода в условное топливо: Мазут – 1,37; уголь – 0,9; газ – 1,2 тыс.м3. Для определения общего потребления топлива необходимо предварительно перевести расходы топлива в единицы условного топлива, т.е. для каждого вида топлива необходимо <расход топлива> · <калорийный коэффициент перевода>,

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

Уф %. Упл

Удельный вес израсходованного топлива: <расход факт. (условного) топлива >/Уф. Постройте таблицу. 1. Откройте новый лист рабочей книги и дайте ему имя «Расход топлива». 2. Введите в таблицу исходные данные и формулы для вычисления показателей, приведенные выше. В готовом виде таблица будет иметь вид: рис. 181 – режим решения, рис. 182 – режим формул.

Рис.181

Рис. 182 3. Перевозка груза автотранспортным предприятием характеризуется следующими данными (тыс. т): 1999 г. – 2500; 2001 г. – 2650; 2002г. – 2750, в том числе по договорной клиентуре – соответственно 1310; 1425; 1560. Представьте приведенные данные в виде электронной таблицы. Определите: удельный вес перевозок грузов по договорной клиентуре в общем объеме перевозок; изменение объема перевозки всех грузов по сравнению с 1998г., в том числе и по договорной клиентуре; на сколько увеличился объем всех перевозок и перевозок по договорной клиентуре в 2002 г. по сравнению с 1998г.

Глава 6. Построение диаграмм Данные из рабочих листов Excel можно представить в виде самых разнообразных диаграмм. Диаграмма – это графическое представление данных рабочего листа. Диаграммы упрощают сравнение и восприятие числовых данных. Диаграммы могут строиться как непосредственно на рабочих листах с данными, так и на отдельных листах – листах диаграммы. Диаграммы, создаваемые на рабочих листах, называются внедренными диаграммами. Диаграммы создаются по элементам данных – отдельным числам рабочего листа и по рядам данных – наборам значений, которые требуется изобразить на диаграмме. Для упорядочения значений в рядах данных используются категории. Ряды данных – это те значения, которые нужно вывести на диаграмме, категории – это «заголовки», над которыми эти диаграммы откладываются. Диаграммы создаются с помощью Мастера диаграмм. Мастер диаграмм – это специальная программа, представляющая ряд диалоговых окон, с помощью которых легко построить диаграмму. Мастер помогает выполнить все необходимые для создания диаграммы действия, проверить выделение данных, выбрать тип диаграммы, решить, нужно ли добавить названия и легенду. Независимо от того, какая диаграмма создается – внедренная или на отдельном листе, данные диаграммы автоматически связываются с тем рабочим листом, на основе которого она создана. При изменении данных рабочего листа диаграмма соответственно обновляется. 6.1. Технология построения диаграммы Для создания диаграммы следует: Нажать кнопку Мастер диаграмм на панели Стандартная или выбрать команду Вставка – Диаграмма. Откроется диалоговое окно Мастер диаграмм (шаг 1 из 4). Выбрать тип и вид диаграммы из предлагаемого набора. Нажать кнопку Далее. Откроется диалоговое окно Мастер диаграмм (шаг 2 из 4). Ввести диапазон ячеек, на основании которых строится диаграмма. Это можно сделать либо, непосредственно вводя данные в поле ввода, либо выделить нужный диапазон ячеек непосредственно на рабочем листе. Можно выделить нужный блок ячеек до вызова Мастера диаграмм. Тогда в поле ввода вариант данных, на основе которых строится диаграмма, уже будет готов. Нажать кнопку Далее. Откроется диалоговое окно Мастер диаграмм (шаг 3 из 4). Во вкладке Заголовок ввести в соответствующие поля Название диаграммы, название оси Х и название оси Y. Нажать кнопку Далее. Откроется диалоговое окно Мастер диаграмм (шаг 4 из 4).

Определить, куда следует поместить диаграмму: на отдельный лист или оставить на имеющемся листе. Нажать кнопку Готово. Для удаления диаграммы следует выделить область диаграммы, щелкнув по ней левой клавишей мыши и нажать кнопку Delete. 6.2. Построение гистограммы Пусть требуется построить диаграмму на рабочем листе с таблицей «План выпуска продукции» (рис.183). В нем имеется два ряда плановых показателей выпуска изделий (Изделие 1, Изделие 2), выпускаемых предприятиями (Предприятие 1, Предприятие 2, Предприятие 3, Предприятие 4, Предприятие 5).

Рис. 183 Для построения диаграммы следует выполнить следующие действия: Выделить диапазон А3:С8, в пределах которого находятся данные.

Рис. 184

Рис.185

Выбрать команду Вставка – Диаграмма или щелкнуть по кнопке Мастер диаграмм на панели инструментов Стандартная. Откроется диалоговое окно Мастер диаграмм (шаг 1 из 4): тип диаграммы (рис. 184). Требуется выбрать тип диаграммы. Остановимся на обычной гистограмме (столбчатой), которую Мастер предлагает по умолчанию. (На первом шаге можно легко изменить тип диаграммы). Нажмем кнопку Далее. Откроется диалоговое окно Мастер диаграмм (шаг 2 из 4): источник данных диаграммы (рис. 185). В появившемся окне показано, какой вид будет иметь диаграмма выбранного типа. Справа от диаграммы представлена легенда. Легендой называется специальная область, которая определяет закраску или цвета точек данных диаграммы (цветные квадратики). На этом шаге предлагается подтвердить выбранный диапазон данных и указать, как располагаются ряды данных: в строках или столбцах. В нашем примере ряды данных расположены по умолчанию в столбцах (переключатель установлен в положении Ряды в столбцах). Они отображаются в виде столбиков различного цвета: для Изделия 1 – синий цвет (левый столбик), для Изделия 2 – бордовый (правый столбик). Названия предприятий располагаются вдоль горизонтальной оси Х. Если установить переключатель в положение Ряды в строках, картинка изменится (рис. 186). Теперь цветные столбики соответствуют именам предприятий, а названия изделий расположатся вдоль горизонтальной оси. Снова переключимся в положение Ряды в столбцах и выберем вкладку Ряд в окне мастера (рис. 187). На этой вкладке в поле Ряд показаны имеющиеся ряды данных, в поле Имя – их названия, в поле Значение – занимаемый диапазон, а также подписи по оси Х (рис. 187, 188). Например, значения ряда Изделие 1 находятся в диапазоне $В$4:$В$8 (рис. 187), значения ряда Изделие 2 находятся в диапазоне $С$4:$С$8 (рис. 188), подписи по оси Х находятся в ячейках $А$4:$А$8 (рис.187, 188). Имя ячейки, которое будет представлено в легенде, записано в ячейке $В$3 – Изделие 1 и $С$3 – Изделие 2. На втором шаге легко изменяется диапазон области данных, добавляются и удаляются ряды, меняются их имена и подписи по оси Х.

Рис. 186

Рис.187

Рис.188

Рис. 189 Для перехода к третьему шагу Мастера надо нажать кнопку Далее. Появится диалоговое окно Мастер диаграмм (шаг 3 из 4): параметры диаграммы (рис. 189). В нем можно задать множество параметров,

сгруппированных по отдельным вкладкам. Во вкладке Заголовки можно ввести название диаграммы и названия по осям. Введем название диаграммы : План выпуска изделий на 2003г. Оси Х дадим имя: Названия предприятий. Оси Y – Количество выпускаемых изделий (шт.). Во вкладке Оси можно включить и отключить подписи по осям диаграммы. Во вкладке Линии сетки можно включить, отключить, настроить эти линии. Во вкладке Легенда можно изменить ее положение и размеры. Вкладка Подписи данных позволяет переделать подписи значений или категорий. Во вкладке Таблица данных можно включить опцию Таблица данных, тогда вместе с диаграммой будет выводиться и таблица с исходными данными. Выберем местонахождение диаграммы на имеющемся листе и нажмем ОК (рис.190). Получим диаграмму, расположенную на рабочем листе (рис. 191).

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

Рис.191 6.2. Добавление новых данных к уже существующей диаграмме К уже существующей диаграмме можно добавить новые данные. Введем в таблицу в блок ячеек D3:D8 данные о плане выпуска Изделия 3 (рис. 192). Выполним следующие действия: Щелкнем правой кнопкой мыши по области данных диаграммы. Появится контекстное меню. Выберем пункт Исходные данные. Появится диалоговое окно Исходные данные (рис. 193). Выберем вкладку Ряд. Щелкнем по кнопке Добавить. В поле Ряд появится имя нового ряда Ряд 3. Щелкнем по полю Имя, а затем по ячейке D3. В поле Имя появится номер ячейки $D$3. Щелкнем по Ряд 3, и надпись будет заменена на новую – Изделие 3. Щелкнем по полю Значения, а затем выделим блок ячеек D4:D8 таблицы. В поле Имя появится $D$4:$D$8.

Нажмем кнопку ОК. Получим новую диаграмму (рис. 194).

Рис.192

Рис.193

Рис. 194 Добавим в исходную таблицу еще одну строку (рис. 195).

Рис. 195 Добавим новые данные к диаграмме, представленной на рисунке 194. Для этого следует: Активизировать область построения диаграммы. Синяя рамка вокруг диапазона А3:D8 покажет область исходных данных. «Ухватить» мышью синий маркер рамки и расширить область, которая этой рамкой охватывается, т. е. до А3: D9. Диаграмма будет

изменена, и в ней появятся данные о плане выпуска продукции предприятием Предпр. 6 (рис. 196).

Рис. 196 6.3. Круговые диаграммы Круговая диаграмма показывает долю каждого элемента ряда данных в общей сумме. Круговая диаграмма содержит только один ряд данных. Если выбрать несколько рядов, будет выбран только первый, остальные будут проигнорированы, причем сообщений об ошибке не появится. При создании круговой диаграммы суммируется выделенный ряд данных, а затем значение каждого элемента делится на общую сумму. Построим круговые диаграммы для определения доли каждого вида изделия в общем плане выпуска изделий и доли каждого предприятия в выпуске изделий всех типов (рис. 195). Для этого подсчитаем, сколько всего изделий каждого типа планируется выпустить в 2003г. и количество изделий всех типов, которые планируется выпустить каждым предприятием.

Рис. 197 Для построения круговой диаграммы: выделим область данных: блок ячеек В3:D3, и, нажав кнопку Ctrl, блок ячеек В10:D10. Щелкнем по кнопке Мастер диаграмм на панели инструментов Стандартная.

Рис. 198 Откроется диалоговое окно первого шага Мастер диаграмм Выберем тип диаграммы Круговая и вид, показанный на рисунке 198. Нажмем кнопку Далее. Нажмем кнопку Далее. Появится диалоговое окно второго шага Мастер диаграмм. Оставим переключатель в положении, предлагаемом по умолчанию.

Нажмем кнопку Далее. Появится диалоговое окно третьего шага Мастер диаграмм. Введем название диаграммы: Доля изделий в общем плане выпуска. Нажмем кнопку Далее. Появится диалоговое окно четвертого шага Мастер диаграмм. Оставим переключатель в положении: на имеющемся листе. Нажмем кнопку Готово. Щелкнем правой кнопкой мыши по области диаграммы. В контекстном меню выберем пункт Формат рядов данных. Во вкладке Подписи данных установим переключатель в положение Доля. Получим следующую диаграмму (рис. 199).

Рис. 199 Для построения круговой диаграммы, определяющей долю каждого предприятия в плане выпуска изделий всех типов, выделим область данных А4:А9 и Е4:Е9. Произведем все описанные выше действия, выбрав тип диаграммы, указанный на рисунке 200.

Рис. 200 Получим диаграмму, показанную на рисунке 201.

Рис. 201 Рассмотренные выше диаграммы создавались на листе, на котором бала таблица используемых данных. Можно создавать диаграммы на отдельном листе. Для этого в диалоговом окне Мастер диаграмм (шаг 4 из 4): размещение диаграммы (рис.190) надо установить переключатель Поместить диаграмму на отдельном листе. 6.4. Точечные диаграммы

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

Рис. 202 В первом случае была выбрана точечная диаграмма, вид которой выделен и показан на рисунке 203, во втором случае – на рисунке 204. В точечной диаграмме каждая точка строится с использованием пары координат: одна – из ряда X, а другая – из ряда Y. При этом ряд значений X должен содержаться в левом столбце (или верхней строке), интервала данных, а ряды значений в последующих столбцах (или строках).

Рис. 203

Рис. 204 Задания

1. Постройте круговую диаграмму в документе «Счет за ремонт автомашины», которая будет показывать долю каждого вида работы в общей сумме стоимости ремонта автомашины. 2. Постройте гистограмму в документе «Прогноз финансовой деятельности предприятия», показывающую увеличение прибыл предприятия в следующем пятилетии. 3. Постройте диаграммы по своему выбору в документе «Продажа телевизоров». 4. Постройте диаграммы по своему выбору в документе «Расход топлива».

Заключение В данном учебном пособии рассмотрены основные приемы работы в Excel. Если вы внимательно прочитали все главы учебного пособия и выполнили на компьютере все разобранные в пособии упражнения и задания для самостоятельной работы, то научились быстро создавать простейшие электронные документы в виде таблиц. Вы научились вводить числовые и текстовые данные в электронные таблицы, узнали, как производить ввод и вычисление по формулам, как выполнять копирование формул, научились определять, в каких случаях целесообразно использовать абсолютные и относительные ссылки, чтобы быстро заполнять клетки таблицы однотипными данными. Вы познакомились с большим количеством функций и научились использовать их для вычислений, научились форматировать документы Excel, строить диаграммы на основе данных, находящихся на рабочем листе и настраивать формат готовой диаграммы. К сожалению, рамки данного учебного пособия не позволили ознакомиться с имеющимся в Excel аппаратом быстрого и удобного извлечения и формирования обобщающихся сводок. Доступ к этим средствам открывает меню Данные. Команды этого меню дают возможность производить сортировку, фильтрацию, консолидацию данных. Excel имеет средства для численного анализа данных, которые являются доступными через меню Сервис. Команда этого меню Подбор параметра позволяет найти значение аргумента, удовлетворяющее заданному значению функции. С его помощью можно быстро получить результаты, которые трудно получить непосредственно. Гораздо более мощной является команда этого же меню – Поиск решения. С ее помощью можно искать решение системы уравнений, решение задач оптимизации. Excel дает возможность прогнозировать процессы. Например, если поведение какой-либо системы в течение некоторого периода может быть описано в виде таблицы значений функции от своего аргумента, прогноз поведения этой системы в будущем можно получить с помощью Excel. Освещение этих вопросов будет представлено во второй части учебного пособия.

Литература 1. Информатика. Базовый курс: Учебник для вузов/ Под ред. С.В. Симоновича.– СПб.: Питер, 2001. – 638с. 2. Алексеев А., Евсеев Г., Мураховский В., Симонович С. Новейший самоучитель работы на компьютере. / Под ред. С.В. Симоновича. – Москва: ДЕСС КОМ, 2000. – 654с. 3. Экономическая информатика. Учебник для вузов/ Под ред. В.В. Евдокимова. – СПб.: Питер, 1997. 4. Комягин В.Б., Коцюбинский А.О., Excel 7.0 в примерах: Практ. Способ. – М.: Нолидж, 1996. – 432с. 5. Попов А., Excel: практическое руководство.: Москва: ДЕСС КОМ, 2001. – 301с. 6. Ефимова М.Р., Ганченко О.И., Петрова Е.В., Практикум по общей теории статистики: Учеб. пособие. – М.: Финансы и статистика, 2000. – 280с.

Предметный указатель Абсолютная ссылка……………………………………………………..48 Автоматическое заполнение ячеек…………………………………….29 Автоматическое суммирование………………………………………..75 Ввод данных…………………………………………………………….15 Ввод формул…………………………………………………………….39 Ввод функций…………………………………………………………...76 Вставка рабочего листа………………………………………………….6 Вставка строки (столбца)………………………………………………24 Выделение диапазона ячеек……………………………………………14 Выделение рабочего листа……………………………………………..10 Гистограмма……………………………………………………………147 Замена относительной ссылки на абсолютную……………………….49 Заполнение группы ячеек………………………………………………20 Изменение высоты строки…………………………………………….120 Изменение ширины столбца……………………………………………26 Копирование и перенос…………………………………………………20 Копирование формул…………………………………………………...47 Круговые диаграммы………………………………………………….156 Логические функции……………………………………………………95 Массивы…………………………………………………………………67 Математические функции……………………………………………...80 Матричные функции……………………………………………………86 Название книги…………………………………………………………..5 Объединение ячеек……………………………………………………118 Окно Excel……………………………………………………………….. 8 Относительные ссылки…………………………………………………47 Очистка…………………………………………………………………..25 Панель инструментов Форматирование……………………………121 Переименование листов………………………………………………… 6 Перемещение по рабочему листу……………………………………...12 Перемещение по рабочей книге………………………………………..11 Переход к невидимым листам………………………………………….11 Пользовательские списки………………………………………………35 Построение диаграмм…………………………………………………146 Редактирование………………………………………………………….15 Смешанные ссылки……………………………………………………..48 Статистические функции……………………………………………….91 Точечные диаграммы………………………………………………….160 Удаление рабочего листа……………………………………………….. 6 Удаление ячеек………………………………………………………….25

Условное форматирование……………………………………………116 Форматирование чисел………………………………………………..125 Форматирование ячеек………………………………………………...130 Библиографический список 7. Информатика. Базовый курс: Учебник для вузов/ Под ред. С.В. Симоновича.– СПб.: Питер, 2001. – 638с. 8. Алексеев А., Евсеев Г., Мураховский В., Симонович С. Новейший самоучитель работы на компьютере. / Под ред. С.В. Симоновича. – Москва: ДЕСС КОМ, 2000. – 654с. 9. Экономическая информатика. Учебник для вузов/ Под ред. В.В. Евдокимова. – СПб.: Питер, 1997. 10. Комягин В.Б., Коцюбинский А.О., Excel 7.0 в примерах: Практ. Способ. – М.: Нолидж, 1996. – 432с. 11. Попов А., Excel: практическое руководство.: Москва: ДЕСС КОМ, 2001. – 301с. 12. Ефимова М.Р., Ганченко О.И., Петрова Е.В., Практикум по общей теории статистики: Учеб. пособие. – М.: Финансы и статистика, 2000. – 280с.