Часть I. Создание таблицы и построение диаграммы

Использование табличного процессора 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
“Поиск решения”

Этот метод используется для поиска значения аргумента функции, которое обес­пе­чи­вает требуемое значение самой функции.

В качестве примера рассмотрим поиск корня уравнения: x³ –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 должно быть указано, какие приборы и в каком количестве лучше всего выпускать.