Использование табличного процессора Microsoft Excel
Решение ряда задач приводит к обработке прямоугольных таблиц данных разных типов (текстовых, числовых).
Применение для таких задач средств алгоритмических языков высокого уровня оказалось во многих случаях не эффективно. Текстовые процессоры дают возможность ведения и форматирования таблиц, но они плохо приспособлены для вычислений.
Вышеуказанные причины вызвали появление программ, называемых электронными таблицами, объединяющими в себе возможности: текстовых процессоров по:
· созданию и форматированию таблиц;
· математической обработке табличных данных;
· визуализации результатов в форме таблиц, диаграмм, графиков.
История развития программ обработки электронных таблиц насчитывает около двадцати лет, налицо огромный прогресс в этой области программного обеспечения.
Примерами программ - электронных таблиц являются Lotus 1-2-3 (Lotus), Microsoft Excel. В настоящее время наибольшее распространение получил программный комплекс Microsoft Excel.
Документом Excel является рабочая книга - это файл с произвольным именем и расширением.xlsx. Рабочая книга состоит из рабочих листов (в количестве от 1 до 256, каждый имеет свое имя). Один из рабочих листов является активным, т.е. в настоящий момент с ним работает пользователь. Рабочий лист представляет собой таблицу, в которой может содержаться до 65536 строк и до 256 столбцов. Строки нумеруются числами (от 1 до 65536), столбцы - латинскими буквами А, В, С,..., Y, Z, АА, АВ, АС,.., AZ, ВА,..., IV
На пересечении столбцов и строк находятся ячейки. Каждая ячейка имеет адрес, состоящий из указания столбца и строки, на пересечении которых она находится, например Al, B5, АВ234.
Адреса ячеек используются при обращении к их содержимому. Одна из ячеек является активной, она выделяется черной рамкой, в правом нижнем углу которой находится маркер заполнения (черный квадратик). Ввод данных производится в активную ячейку. Перемещение активной ячейки осуществляется клавишами управления курсором, Tab, Shift+Tab, Enter, PageUp, Page Down, Home, End или щелчком мыши.
Диапазоны. Иногда в Excel требуется работать не с одной ячейкой, а с группой ячеек в виде прямоугольника, такая группа называется диапазон. Прежде чем работать с диапазоном, следует его выделить. Выделяется диапазон протягиванием мышью по диагонали диапазона. Выделенный диапазон на рабочем поле показывается инверсным цветом, вокруг него располагается рамка с маркером заполнения, как на активной ячейке.
Щелчок вне выделенного участка отменяет выделение. Диапазоны (прямоугольные участки) выделяются для выполнения т.н. групповых операций (очистка, заполнение прогрессиями, копирование формул, однотипное форматирование данных и т.п.).
Элементы управления. Окно Excel содержит заголовок с названием файла и программного средства(Microsoft Excel), ленту с вкладками, рабочее поле, строку состояния.
Важным элементом окна Microsoft Excel является с трока формул, располагающаяся под панелями инструментов и главным меню:
В строке формул слева находится окно со стрелкой вниз, которое называется селектором диапазонов и функций, далее – кнопка отмены ввода, кнопка подтверждения, что формула введена правильно, кнопка вызова списка всех встроенных функций и, наконец, поле для набора формулы. Если необходимо ввести формулу, то ее следует начинать со знака "равно" и не использовать пробелы при конструировании формулы.
Если строка формулы не видна, ее можно показать командой Вид | Строка формул.
Для того, чтобы отобразить формулу непосредственно в ячейке, нужно выделить эту ячейку и нажать F2 (выход из этого режима – Esc).
Для того, чтобы отобразить все формулы перейдите на вкладку Формулы и щелкните по кнопке Показать формулы в группе Зависимости Формул.
Часть I. Создание таблицы и построение диаграммы
Запустите Excel (Пуск | Программы | Microsoft Office | Microsoft Excel. Табличный процессор предоставит вам файл с именем Книга1.xlsx. Имя файла (без расширения) Вы видите в заголовке окна. Сразу же измените это имя, сделав его "своим", для чего выполните команду Файл | Сохранить как … Выберитепапку c:\work. В поле "Имя файла" замените слово Книга1 на ФамилияИО (здесь ФамилияИО – ваши собственные) и щелкните кнопку "Сохранить". Проверьте результат: имя в заголовке окна должно измениться на ФамилияИО.
Измените наименование первого листа, для этого сделайте двойной щелчок по ярлыку листа и после того, как слово Лист1 выделится, наберите Вход, и нажмите Enter.
Вам необходимо создать следующую таблицу:
Ввод текста.
Обратите внимание, что заголовки столбцов занимают 2 строки. Выделите ячейки A1 и A2 и на вкладке Главная нажмите кнопку “Объединить и поместить в центре” в группе Выравнивание. В этой же группе нажмите кнопки “Выровнять посередине” и “Выровнять по центру”. Наберите заголовок «Показатели».
Закончив ввод в ячейку, измените ширину столбца на минимально необходимую. Для этого поместите курсор на разделительную линию между столбцами в адресной строке (самая верхняя строка с обозначениями столбцов), курсор должен принять вид двунапрвленной черной стрелочки; сделайте двойной щелчок мышью. Ширина столбца должна измениться.
Аналогично создайте заголовок второго столбца.
Щелкните правой кнопкой мыши по ячейке С1 и выберите в контекстном меню Формат ячеек. В появившемся диалоговом окне выберите вкладку Выравнивание. Установите флажок в строке переносить по словам. То же самое сделайте для ячеек первого столбца. Наберите заголовки строк.
Когда курсор мыши принимает на границе вид двунаправленной стрелочки, можно изменять положение границ путем их перетаскивания (при нажатой левой кнопке мыши). Измените размер первого столбца так, чтобы текст в таблице выглядел так же как на рисунке.
Для того, чтобы ввести надпись “Отчетный год”, выделите ячейки D1 и E1 и нажмите кнопку на панели инструментов “Объединить и поместить в центре”.
Наберите остальные заголовки столбцов.
Выделите весь диапазон ячеек таблицы, щелкните правой кнопкой мыши и в появившемся контекстном меню выберите “Формат ячеек”. В появившемся диалоговом окне выберите вкладку Выравнивание. Установите выравнивание по вертикали по центру.
Для заголовков столбцов установите выравнивание по горизонтали по центру с помощью кнопки на панели инструментов.
Ввод данных:
Заполните ячейки С3, С4, С5, D3, D4, D5, E3, E4,E5 числами в соответствии с приведенной ниже таблицей (вводить можно в любом порядке):
Адрес | Число | Адрес | Число | Адрес | Число |
С3 | D3 | E3 | |||
С4 | D4 | E4 | |||
С5 | D5 | E5 |
Ввод формул:
В ячейку F3 введите формулу, представляющую следующий текст, начинающийся со знака " равно " =МИН(D3;E3). (Эта формула заканчивается круглой скобкой, и точка в нее не включается).
В ячейку F4 введите формулу = МАКС(D4;Е4), в ячейку F5 — формулу = СУММ(F3;F4).
При вводе формул нужно помнить, что буквы в адресах ячеек латинские. Набирая формулу, адреса ячеек можно вводить, не пользуясь клавиатурой, а только указывая нужную ячейку мышью вместо нажатия клавиш.
Диапазон смежных ячеек указывают, используя двоеточие, например диапазон A1: B2 включает в себя 4 ячейки. Через точку с запятой можно добавить к диапазону другие ячейки.
Работа с двумя листами:
Перейдите на Лист2 и измените название листа на Выход.
Введите формулы-ссылки, показанные ниже. Для ввода формулы в первую ячейку выделите ее, нажмите знак =, перейдите на лист Вход, щелкнитепо ячейке A1 и нажмите Enter. В ячейке А1 листа Выход Вы должны получить формулу: =Вход!А1
Аналогичным образом в ячейку А2 листа Выход вставьте формулу =Вход!А3
Установите для ячейки А2 перенос по словам и выравнивание по вертикали по центру.
С помощью маркера заполнения (протащив его вниз при прижатой левой кнопке мыши) скопируйте формулу из ячейки А2 в ячейки А3 и А4.
Введите в ячейку А5 текст Средняя, в ячейку B1 – Выполнение плана и в С1 – Динамика.
Введите в ячейку B2 листа Выход формулу =Вход!E3*100 / Вход!D3 (эта формула отражает выполнение плана в процентах).
Скопируйте формулу в ячейки B3 и В4.
В ячейку С2 введите формулу =Вход!E3*100 / Вход!C3 (Количество фактически произведенной продукции * 100 / Количество продукции за базисный год). Скопируйте эту формулу в ячейки С3 и С4.
В ячейки В5 и С5 введите формулы, вычисляющие соответственно средние значения ячеек В2, В3, В4 и С2, С3, С4. Используя команду Формат ячеек, отформатируйте вычисленные числовые значения до 2 знаков после запятой.
Вы должны получить таблицу, показанную на рисунке:
Для того, чтобы задать символ, оделяющий дробную часть числа от целой, выберите Файл | Параметры | Дополнительно и в строке «разделитель целой и дробной части » задайте, например, точку.
Построение диаграммы
Щелкните по ярлыку листа Вход. На этом листе выделите ячейки D3: F5. Всего должно быть выделено 9 ячеек. Выполните команды Вставка ® Гистограмма. Выберите вариант «Объемная гистограмма» (первую слева).
На листе должна появиться диаграмма. При этом в меню появится вкладка «Работа с диаграммами»
Перейдите на вкладку Конструктор. Нажмите кнопку Выбрать данные.
По умолчанию, ряды соответствуют строкам таблицы. Выберем в качестве рядов столбцы. Для этого в появившемся окне «Выбор источника данных» нужно выделить название ряда (Ряд 1) и нажать в этом окне кнопку Изменить.
Появится окно «Изменение ряда». В поле «Имя ряда» задайте «план». Для этого можно щелкнуть по кнопке с красной стрелочкой справа от поля и щелкнуть по ячейке таблицы, содержащей эту надпись.
После этого щелкните по стрелочке справа от поля «Значения». Появится окно «Изменение ряда». Выделите ячейки первого столбца выделенного диапазона (D3:D5). Щелкните по кнопке со стрелочкой. В предыдущем окне щелкните по кнопке “OK”.
Измените аналогично Ряд 2 и Ряд 3, сопоставив им столбцы E и F. Закройте окно кнопкой OK.
Перейдите на вкладку Макет и щелкните кнопку «Название диаграммы». Вберите вид «Над диаграммой» и наберите на клавиатуре Показатели (набираемые символы отображаются в строке формул). Нажмите <Enter>. Цвет надписи можно изменить на вкладке Главная.
Выделите диаграмму и на вкладке «Работа с диаграммами» нажмите кнопку «Переместить диаграмму». Выберите в появившемся окне «На отдельном листе» и название просто «Диаграмма».
Проверьте результат: в книге должен появиться еще один лист с введенным Вами названием и на этом листе должна находиться диаграмма. Перетащите этот лист так, чтобы он стал вторым.
Лепестковая диаграмма
Лепестковая диаграмма, благодаря внешнему виду также называемая диаграммой-паутиной или диаграммой-звездой, представляет значения каждой категории вдоль отдельной оси, которая начинается в центре диаграммы и заканчивается на внешнем кольце.
Перейдите на лист Выход и выделите ячейки с числовыми данными (B2: C5).
. Выполните команды Вставка ® Другие. Выберите вариант «Заполненная лепестковая». Выберите на ленте стиль диаграммы. Для каждого столбца будет построена своя поверхность.
Значения во втором столбце больше, чем в первом, поэтому, чтобы данные второго столбца не закрывали данные первого, сначала построим диаграмму для второго столбца, а сверху - для первого. Как описано в предыдущем разделе измените Ряд1 и Ряд2.
Щелкните правой кнопкой мыши по закрашенной области диаграммы (в таблице должен быть выделен соответствующий столбец). Выберите на появившейся панели цвет диаграммы и тип заливки Градиентная. То же самое сделайте для второй области.
Вы должны получить диаграмму такого типа как показано ниже. Каждая ось соответствует одной строчке в таблице.
Решение уравнений с помощью надстройки Exсel
“Поиск решения”
Этот метод используется для поиска значения аргумента функции, которое обеспечивает требуемое значение самой функции.
В качестве примера рассмотрим поиск корня уравнения: x3 –sin(x) -.5 = 0.
Представим функцию в табличной форме и построим ее график, который позволит определить корень уравнения приближенно.
Сделайте надпись в первой строке таблицы как показано на рисунке (ниже). Выделите ячейки A1: J1 и нажмите пиктограмму на вкладке Главная: Объединить и поместить в центре.
Для того, чтобы найти корень функции, можно построить ее график, определить отрезок, на котором график проходит через ноль, а затем определить корень с помощью надстройки “Поиск решения”.
Значения sin() находятся в пределах от -1 до 1, поэтому имеет смысл рассматривать график функции только на отрезке [-2; 2]. Вычислим значения функции на этом отрезке, пройдя его с шагом 0.5. Для того, чтобы построить таблицу значений используем автозаполнение: введите в ячейку В2 значение -2, а в ячейку С2 – значение -1,5, выделите эти ячейки и протащите маркер автозаполнения (черный крестик в правом нижнем углу выделенных ячеек) до ячейки J2 включительно.
В ячейку В3 введите формулу =B2^3-SIN(B2)-0,5 и, используя маркер автозаполнения, скопируйте ее во все ячейки до J3 включительно. Вы должны получить таблицу, показанную на рисунке.
Теперь выделите ряд значений в строке Y и нажмите кнопку Вставка | График. Выберите первый вид. Вы должны увидеть график.
Зададим подписи по оси X. Нажмите кнопку Конструктор | Выбрать данные. В правой части появившегося окна нажмите кнопку “Изменить”. Выделите ячейки с данными строки X и нажмите кнопку OK.
Удалите легенду. На вкладке Макет нажмите кнопку Сетка и задайте вертикальные линии сетки.
Поставьте курсор на какое-нибудь число на оси X на диаграмме и щелкните правой кнопкой мыши. В контекстном меню выберите Формат оси. В появившемся окне в разделе Выравнивание установите направление подписей оси так, чтобы они шли снизу вверх, а не горизонтально.
На графике видно, что корень уравнения находится на отрезке от 1 до 1.5. Значение функции, наиболее близкое к нулю, находится в ячейке H3.
Поставьте курсор в ячейку H3 и выберите команду Данные | Поиск решения. Если этой команды нет на линейке, нужно выбрать на ленте Файл | Параметры. В появившемся окне выберите Надстройки и в нижней части окна нажмите кнопку «Перейти ». Появится окно, в котором нужно установить флажок в строке “Поиск решения”.
Появится окно, в котором нужно задать значения, как показано на рисунке ниже.
При нажатии на кнопку «Найти решение» в ячейке H2 будет установлено значение 1.1185, являющееся корнем уравнения.
Установите формат чисел в ячейках Н2 и H3 с точностью 4 знака после запятой. Для этого выделите эти ячейки, щелкните правой кнопкой мыши и в появившемся меню выберите «Формат ячеек». В появившемся окне выберите вкладку Число, в списке выберите формат «Числовой” и задайте 4 знака после точки.
Решение задач оптимизации с помощью надстройки
“Поиск решения”
Задача. Завод производит электронные приборы трех видов (прибор А, прибор В и прибор С), используя при сборке микросхемы трех типов. Расход микросхем задается следующей таблицей:
Прибор А | Прибор Б | Прибор В | |
Тип 1 | |||
Тип 2 | |||
Тип 3 |
Стоимость изготовления приборов одинакова.
Ежедневно на склад завода поступает 400 микросхем типа 1 и по 500 микросхем типов 2 и 3.
Необходимо определить, каково оптимальное соотношение дневного производства приборов различного типа, если производственные мощности завода позволяют использовать запас поступивших микросхем полностью?
Поскольку стоимость приборов одинакова, наиболее оптимальным с точки зрения получения максимальной прибыли будет выпуск максимального количества приборов любых типов.
Постройте таблицу, как показано на рисунке.
A | B | C | D | E | F | |
Кол-во приборов | ||||||
Прибор А | Прибор Б | Прибор В | Всего | |||
Запас | Расход | |||||
Расход комплектующих |
В ячейках А4: А6 указан дневной запас комплектующих. В ячейках С4: Е6 – таблица расхода комплектующих.
В ячейках С3:Е3 будет размещено количество приборов каждого типа, которое необходимо выпустить. В начале занесите в эти ячейки нули, нужные значения в результате решения задачи будут подобраны автоматически. Это и будет решением задачи.
В ячейках В4: В6 нужно указать формулы для расчета расхода комплектующих по типам. В ячейке В4 формула будет иметь вид:
= $C$3*C4 + $D$3*D4 + $E$3*E4
( расход на 1 прибор * кол-во приборов)
Для того, чтобы задать абсолютную ссылку на ячейку, набирая формулу, щелкните в нужной ячейке и нажмите F4.
Скопируйте формулу в остальные ячейки методом автозаполнения.
В ячейку F3 занесите формулу, вычисляющую общее число произведенных приборов, для этого задайте в этой ячейке =СУММ(C3:E3).
Выберите команду Данные| Поиск решения.
В качестве целевой ячейки должна быть указана F3.
В поле «Изменяя ячейки» задайте диапазон подбираемых параметров: C3:E3.
У каждой задачи имеются ограничения. Чтобы указать их для данной задачи, щелкните по кнопке “Добавить”.
Добавьте ограничение B4:B6 <= A4:А6 как показано на рисунке. Это условие показывает, что дневной расход комплектующих не должен превосходить запасов.
Добавьте еще 2 условия: значения в ячейках С3:Е3 должны быть целыми (нельзя выпускать недоделанные приборы) и неотрицательными.
Щелкните по кнопке «Найти решение».
В результате в ячейке F3 вы должны получить максимально возможное кол-во приборов, которое можно произвести, и в ячейках С3:Е3 должно быть указано, какие приборы и в каком количестве лучше всего выпускать.