Возможности excel для решения технических задач

Обновлено: 08.01.2025

На этой странице разберем подробные решения транспортной задачи (алгоритм и примеры разных типов) с использованием пакета электронных таблиц MS Excel (надстройка Поиск решения).

Как решить транспортную задачу в Excel

Ручное решение транспортной задачи занимает очень много времени и сил (скажем, даже для учебной задачи типа 3*5 решение может составлять от 4 до 10 страниц расчетов!). Тогда как решение в Эксель для задачи размерности как 3*3, так и 5*7 потребует буквально 10-15 минут и немного опыта (правда, если уже составлена математическая модель).

Использовать можно любую версию программы - 2003, 2007, 2010 и так далее, главное, включить использование надстройки Поиск решения (интерфейс может немного отличаться в разных версиях).

Алгоритм решения ТЗ в Эксель

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

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

Минимальные затраты на перевозку составят 435. План перевозок:
Из 1 карьера 10 тонн везем на 1-й участок, 15 тонн на 3-й.
Из 2 карьера 20 тонн везем на 1-й участок.
Из 3 карьера 20 тонн везем на 3-й.
Из 4 карьера 10 тонн везем на 1-й участок, 20 тонн на 2-й, 5 тонн на 3-й.

Транспортные задачи: примеры в Excel

Задача 1. Решить транспортную задачу вручную (методом потенциалов) и в программе Эксель.

Задача 2. Исходные данные задачи приведены схематически: внутри прямоугольника заданы удельные транспортные затраты на перевозку единицы груза, слева указаны мощности поставщиков, а сверху - мощности потребителей.
Сформулировать экономико-математическую модель исходной транспортной задачи, найти оптимальный план закрепления поставщиков за потребителями, установить единственность или не единственность оптимального плана, используя Поиск решений.

Задача 3. Имеется 3 нефтеперерабатывающих завода, 4 спиртовых завода, 3 завода по производству синтетического каучука.
Схема кооперационных связей (см. файл).
Далее приведены производственные показатели предприятий.
Также заданы расстояния между предприятиями.
Необходимо найти решение транспортной задачи с ориентацией на спрос СК и минимизацией транспортных суммарных затрат.

Задача 4. Используя метод потенциалов, решить транспортную задачу. Выполнить проверку, используя табличный редактор Microsoft Excel Компания владеет тремя заводами А1, А2, А3. Соответствующие объемы производства равны 600, 300 и 330 единиц продукции. Компания обязалась поставить в города В1, В2, В3 и В4 соответственно 350, 350, 230 и 300 единиц. При заданных в таблице стоимостях перевозок единицы продукции составьте план ее распределения, чтобы общая стоимость перевозок была наименьшей.

Задача 5. Свести задачу к виду ТЗ и решить с помощью надстройки «Поиск решения»
Четыре ремонтные мастерские могут за год отремонтировать соответственно 400, 500, 450 и 550 машин при себестоимости ремонта одной машины в 500, 700, 650 и 600 рублей. Планируется годовая потребность в ремонте пяти автобаз: 550, 350, 300, 375 и 400 машин.
Ремонт машин с 1 автобазы должен осуществляться в 100% случаев силами ремонтных мастерских.
На 4 АБ возможно самостоятельное проведение ремонтных работ (бесплатное) в объеме, не превышающем 8% от планируемой годовой потребности этой мастерской. Платное (на стороне) - совсем не возможно.
Вторая, третья и пятая АБ могут «ремонтироваться» на стороне, стоимость ремонта +трансп.расходы каждой машины в таком случае составит 695 руб.
Дана матрица, характеризующая транспортные расходы на доставку машины с j-й автобазы в i-ю ремонтную мастерскую. Определить минимальную годовую потребность в кредитах на выполнение указанного объема работ по всем автобазам

Аннотация научной статьи по компьютерным и информационным наукам, автор научной работы — Назарьева Виктория Алексеевна

Рассматриваются вопросы автоматизации локальных задач без применения непосредственного программирования. Автоматизация производится за счет встроенных возможностей функционального характера редактора электронных таблиц Microsoft Excel, позволяющего не только производить расчеты, но и создавать соответствующий интерфейс. Предложенная методика используется в учебном процессе в рамках практических работ по дисциплине «Информационные технологии конструкторско-технологического обеспечения машиностроительного производства».

Текст научной работы на тему «Использование средств MS Excel в учебном процессе для решения технологических задач»

ИСПОЛЬЗОВАНИЕ СРЕДСТВ MS EXCEL В УЧЕБНОМ ПРОЦЕССЕ ДЛЯ РЕШЕНИЯ ТЕХНОЛОГИЧЕСКИХ ЗАДАЧ

Расчет размерных цепей, автоматизация процесса

V.A. Nazarieva MS ECXEL USE IN EDUCATIONAL PROCESS WITHIN TECHNICAL TASKS SOLUTION

Dimensional circuit calculation, automation of the process

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

Одной из таких дисциплин является дисциплина «Информационные технологии конструкторско-технологического обеспечения машиностроительного производства». В рамках этой дисциплины студенты учатся решать локальные задачи конструкторской и технологической подготовки производства с использованием информационных технологий.

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

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

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

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

Шаблон создается в виде таблицы и имеет вид, показанный на рис. 1.

Пользователем заполняются графы «Увеличивающие размеры» и «Уменьшающие размеры».

Для расчета размера замыкающего звена используется формула:

где АД - номинальный размер замыкающего звена; ТАув - сумма номинальных размеров увеличивающих звеньев; ТАум - сумма номинальных размеров уменьшающих звеньев.

Microsoft Excel - расчет размерных, цепей.хЬ

1] Файл [Правка Вид Вставка Формат Сервис Данные Окно Справка

% ооо *ii ï,i iw iw _ • > • д • .

А 1 В 1 С D 1 Е 1 F G H 1 1 1 J 1 К L

1 2 3 4 У В &J шчивающие эазмеры поле допуска Уменьшающие эазмеры поле допуска Замыкающий размер

зН 1 1 S! £ К аз аз 3 аз X X 33 X ^ 33 “ Ь 33 33 2 X 33 “ -1 к 8 о J3J 1 1 5 ^ Н 33 03 =5 зз X X 35 X х зз =: 33 33 5 33 2 Ц 33 “ — к 1 из 1 1 Ё ^ М 33 33 3 зз X X 33 X ^ зз =: 33 » 5 33 2 J3 33 “ -1 к g

Рис. 1. Таблица шаблона для исходных данных

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

«=СУММ(А3:А5)» для ячейки А6 и «=СУММ(Е3:Е5)» для ячейки Е6.

Знак «:» между именами ячеек указывает на то, что в сумму будут включены все ячейки указанного диапазона, таким образом, обеспечивается возможность ввода любого количества звеньев. Для добавления новых звеньев необходимо произвести вставку новых строк, что реализуется через меню: Вставка->Строки, при выделенной нижней строке с исходными данными (рис. 2).

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

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

Далее в ячейки В, J3 и КЗ, соответствующие номинальному размеру и верхнему и нижнему отклонениям замыкающего размера соответственно, вводятся формулы для расчета этих параметров:

«:=А6-Е6» для ячейки В,

«=B6-G6» для ячейки J3,

«=С6-Р6» для ячейки КЗ.

Рис. 2. Добавление строк для увеличения количества звеньев размерной цепи

Графы «поле допуска» являются необязательными и введены согласно методике расчета размерных цепей для получения дополнительных значений замыкающего размера:

Графа «поле допуска» рассчитывается по формуле:

верхнее отклонение - нижнее отклонение, которая вводится в ячейки Б3 и Н3 и протягивается на ячейки Б4:Б5 и Н4:Н5 соответственно. В ячейках Б6 и Н6 уже введены формулы для подсчета суммы полей допусков.

Для определения дополнительных величин вводятся поля Адшах, Адшт, ТАд и в соответствующие им ячейки записываются формулы:

Все ячейки, ссылки на которые содержатся в формулах, содержат соответствующие расчетам суммы.

Для наибольшего удобства использования созданного шаблона рекомендуется неизменяемые поля выделить каким-либо цветом посредством использования инструмента «заливка».

Пример. Рассчитать размер замыкающего звена по схеме, показанной на рис. 3.

Как видно из схемы, увеличивающие размеры

Microsoft Excel - расчет размерных цепей.х15

I Файл Правка Вид I Вставка Формат Сервис Данные Окно Справка

imes New Roman 12

Ув еличив аюши I_________размеры

3 i « 3 ж 33 S3 s

S о <D аз S СЭ X ä § о =. pq ^ о Нижнее

Разрыв страницы /ж Функция.

О Схематическая диаграмма. Объект.

%, s - èi si ia®ioo% - т ;

размеры поле допуска Замыкающий размер

и и X X u g 2 К ^ ь 35 3 х , É « 3 2 X £] s s; s ^ о м 03 аз S аэ X а, § о =. ь 03 аз X х 33 g 2 к ^ ь

А3 = 5_0,03 5_0,03 А4 = 5-0,03 А5 = 140-од

Рис. 3. Эскиз узла (а) и его размерная цепь (б)

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

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

^ 1 Файл Правка Вид Вставка Формат Сервис Данные Окно Справка

i Не можете найти то, что вам нужно? Попробуйте сервис подбора литературы.

100% - й iw . <2% . А

А | В С D Е 1 F I G н j к L

1 Ув еличив ающи е размеры поле допуска Уменьшающие размеры поле допуска Замыкающий размер

2 зз! 3 X „ 3 ? 3 £ * Ш з: ^ о К 03 аз К щ и * 5 ЕX О аз гч Я g Ь 03 аз ж 3 3 3 2 к g ь 3 1 в. 3 i X П 1 £ о К аз ® S аз К ^ £ * £ &н О аз ц О аз и ? аз 2 3 я 2 К ё Ь >х 3 1 О. 3 I х S i £■ О к аз 03 * аз ИЗ Я и * 5 £Х О аз ^ Я g Ь Нижнее отклонение

3 101 0,14 0Л4 5 -0,03 0,03 1 0:4 0

4 50 0:1 од 5 -0,03 0,03

6 151 0,24 0 0,24 150 0 *0,16 0,16

В АД max 1,4 АД min 1 ТАД 0,4 Es(AA) 0,4 ЕЦАД) 0

Рис. 4. Пример выполненного расчета размерной цепи

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

Этот подход, несмотря на простоту исполнения, имеет ряд преимуществ перед традиционным способом решения задач автоматизации - программированием.

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

2. Для создания подобных шаблонов требуется наличие на компьютере ОС MS Windows и приложения MS Office редактор электронных таблиц MS Excel, которые в настоящее время повсеместно и широко используются, в то время как для создания программного обеспечения требуется соответствующая среда разработки.

3. Разработанный шаблон занимает один рабочий лист рабочей книги MS Excel, которая хранится в одном файле, легко переносится с одного компьютера на другой и занимает минимальный объем памяти (»20 кб). В то же время даже простейшая программа, имеющая интерфейс с полями ввода-вывода (что необходимо в данном случае), состоит из нескольких файлов, имеющих гораздо больший объем (»2 Мб). Кроме того, функционирование программного обеспечения зачастую бывает невозможно на различных единицах ПК, так как может использоваться множество файловых библиотек или ресурсов среды разработки, отсутствующих на некоторых компьютерах.

1. Харрис М. Освой самостоятельно программирование для Microsoft Excel 2000 за 21 день: учеб. пособие / М. Харрис; пер. с англ. М.: Издат. дом «Вильямс», 2000. 880 с.

2. Якушев А.И. Взаимозаменяемость, стандартизация и технические измерения: учебник для втузов; 6-е изд., перераб. и дополн. / А.И. Якушев, Л.Н. Воронцов, Н.М. Федотов. М.: Машиностроение, 1987. 352 с.

Назарьева Виктория Алексеевна -

кандидат технических наук, ассистент кафедры «Технология машиностроения» Саратовского государственного технического университета Статья поступила в редакцию 18.03.08, принята к опубликованию 22.05.08

Пользователи Excel давно и успешно применяют программу для решения различных типов задач в разных областях.

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

Решение задач оптимизации в Excel

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

В Excel для решения задач оптимизации используются следующие команды:

Подбор параметров («Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра») – находит значения, которые обеспечат нужный результат.
Поиск решения (надстройка Microsoft Excel; «Данные» - «Анализ») – рассчитывает оптимальную величину, учитывая переменные и ограничения. Перейдите по ссылке и узнайте как подключить настройку «Поиск решения».
Диспетчер сценариев («Данные» - «Работа с данными» - «Анализ «что-если»» - «Диспетчер сценариев») – анализирует несколько вариантов исходных значений, создает и оценивает наборы сценариев.

Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».

Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» - 250 рублей. «3» - 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.

Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:

На основании этих данных составим рабочую таблицу:

Количество изделий нам пока неизвестно. Это переменные.
В столбец «Прибыль» внесены формулы: =200*B11, =250*В12, =300*В13.
Расход сырья ограничен (это ограничения). В ячейки внесены формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»). То есть мы норму расхода умножили на количество.
Цель – найти максимально возможную прибыль. Это ячейка С14.

Активизируем команду «Поиск решения» и вносим параметры.

После нажатия кнопки «Выполнить» программа выдает свое решение.

Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.

Решение финансовых задач в Excel

Чаще всего для этой цели применяются финансовые функции. Рассмотрим пример.

Условие. Рассчитать, какую сумму положить на вклад, чтобы через четыре года образовалось 400 000 рублей. Процентная ставка – 20% годовых. Проценты начисляются ежеквартально.

Оформим исходные данные в виде таблицы:

Так как процентная ставка не меняется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).

Ставка – 20%/4, т.к. проценты начисляются ежеквартально.
Кпер – 4*4 (общий срок вклада * число периодов начисления в год).
Плт – 0. Ничего не пишем, т.к. депозит пополняться не будет.
Тип – 0.
БС – сумма, которую мы хотим получить в конце срока вклада.

Вкладчику необходимо вложить эти деньги, поэтому результат отрицательный.

Для проверки правильности решения воспользуемся формулой: ПС = БС / (1 + ставка) кпер . Подставим значения: ПС = 400 000 / (1 + 0,05) 16 = 183245.

Решение эконометрики в Excel

Для установления количественных и качественных взаимосвязей применяются математические и статистические методы и модели.

Дано 2 диапазона значений:

Значения Х будут играть роль факторного признака, Y – результативного. Задача – найти коэффициент корреляции.

Для решения этой задачи предусмотрена функция КОРРЕЛ (массив 1; массив 2).

Решение логических задач в Excel

В табличном процессоре есть встроенные логические функции. Любая из них должна содержать хотя бы один оператор сравнения, который определит отношение между элементами (=, >, =, Пример задачи. Ученики сдавали зачет. Каждый из них получил отметку. Если больше 4 баллов – зачет сдан. Менее – не сдан.

Ставим курсор в ячейку С1. Нажимаем значок функций. Выбираем «ЕСЛИ».
Заполняем аргументы. Логическое выражение – B1>=4. Это условие, при котором логическое значение – ИСТИНА.
Если ИСТИНА – «Зачет сдал». ЛОЖЬ – «Зачет не сдал».

Решение математических задач в Excel

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

Условие учебной задачи. Найти обратную матрицу В для матрицы А.

Делаем таблицу со значениями матрицы А.
Выделяем на этом же листе область для обратной матрицы.
Нажимаем кнопку «Вставить функцию». Категория – «Математические». Тип – «МОБР».
В поле аргумента «Массив» вписываем диапазон матрицы А.
Нажимаем одновременно Shift+Ctrl+Enter - это обязательное условие для ввода массивов.

Возможности Excel не безграничны. Но множество задач программе «под силу». Тем более здесь не описаны возможности которые можно расширить с помощью макросов и пользовательских настроек.

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

Основной особенностью MS Excel выступает его возможность использовать формулы для того, чтобы описывать между значениями разных ячеек различные связи. Следует отметить, что по заданным формулам расчёт будет выполняться автоматически. Если человек изменяет значение какой-либо ячейки, то сразу же автоматически произойдёт перерасчёт значений всех остальных ячеек, которые связаны с ней формульными отношениями, благодаря чему обновится вся таблица и данные, которые находятся в ней.

В основные возможности электронных таблиц входит:

автоматизация всех итоговых вычислений;

над большими наборами данных можно производить однотипные расчёты;

можно разрешать задачи с помощью подбора значений с различными параметрами;

можно обрабатывать результаты экспериментов;

производить табулирование функций и формул;

подготавливать табличные документы;

проводить поиск наиболее оптимальных значений для выбранных параметров;

строить графики и диаграммы по уже введённым данным.

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

Можно использовать Excel:

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

для составления отчётов;

для планирования и расчётов;

в качестве тестовой оболочки для проверки знаний.

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

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

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

Рассмотреть программу Excel.

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

Решить задачу с использованием элементов управления.

Решить систему уравнений графическим способом.

Провести анализ проделанной работы, сделать выводы.

Методы исследования:

Анализ литературы по проблеме исследования.

ГЛАВА I. ПРОГРАММА MICROSOFT OFFICE EXCEL

1.1. Область применения табличного процессора Excel

Программа MS Excel, являясь лидером на рынке программ обработки электронных таблиц, определяет тенденции развития в этой области. К значительным достижениям программы Excel можно отнести появление трехмерных документов (блокнотов). Дополнительные возможности программы облегчают решение сложных прикладных задач. В программу Excel встроена удобная подсистема помощи, которая в любой момент готова выдать необходимую справку. Описанные до сих пор новшества касаются в основном комфорта в работе и быстрого освоения программы. Одним из важнейших функциональных расширений программы, предназначенным для профессионалов, является встроенная в Excel среда программирования Visual Basic (VBA) для решения прикладных задач. Благодаря VBA фирме Microsoft удалось не только расширить возможности языка макрокоманд Excel, но и ввести новый уровень прикладного программирования, поскольку VBA позволяет создавать полноценные прикладные пакеты, которые по своим функциям выходят далеко за рамки обработки электронных таблиц.
Программа MS Excel позволяет решать сложные задачи, связанные с финансовой и бухгалтерской работой предприятий: проводить широкий круг сложных вычислений в электронных таблицах, легко и быстро анализировать большой объем данных; а также приобретать навыки записи и редактирования макросов, с целью применения их для часто повторяющихся операций и автоматизации работы. Программа Microsoft Excel используется при составлении бизнес-анализа.

1.2. Структура документа Excel

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

Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру. В окне документа отображается только текущий (активный) рабочий лист, с которым и ведётся работа. Каждый рабочий лист имеет название, которое отображается на ярлычке листа в нижней части окна. С помощью ярлычков можно переключаться к другим рабочим листам, входящим в ту же рабочую книгу. Чтобы переименовать рабочий лист, надо дважды щёлкнуть мышкой на его ярлычке.
Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист содержит 256 столбцов, пронумерованных буквами латинского алфавита. Строки последовательно нумеруются цифрами, от 1 до 65536.
На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами для хранения данных. Каждая ячейка имеет свой адрес. Адрес ячейки состоит из имени столбца и номера строки, на пересечении которых расположена ячейка, например, A1, B5. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках. В текущий момент времени активной может быть только одна ячейка, которая активизируется щелчком мышки по ней и выделяется рамкой. Эта рамка в программе Excel играет роль курсора. Операции ввода и редактирования данных всегда производятся только в активной ячейке.

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

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

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

1.3. Возможности программы Excel

«Простые задачи должны решаться просто». Этому постулату как нельзя лучше отвечают вычислительные возможности программы MS Excel, которые без оговорки можно назвать безграничными. Программа MS Excel (электронные таблицы) предназначена для работы с таблицами данных, преимущественно числовых. Особенность электронных таблиц заключается в возможности применения формул для описания связи между значениями различных ячеек. Расчёт по заданным формулам выполняется автоматически. Изменение содержимого какой-либо ячейки приводит к пересчёту значений всех ячеек, которые с ней связаны формульными отношениями и, тем самым, к обновлению всей таблицы в соответствии с изменившимися данными.

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

проведения однотипных расчётов над большими наборами данных;

решения задач путём подбора значений параметров;

табулирования формул (функций);

обработки результатов экспериментов;

проведение поиска оптимальных значений параметров;

подготовки табличных документов;

построения диаграмм и графиков по имеющимся данным.

Возможности табличного процессора MS Excel позволяют создавать электронные пособия (учебники), состоящие из многих станиц (листов), переходы в которых можно создавать с помощью гиперссылок и кнопок.

Рассмотрев возможности и структуру табличного процессора MS Excel, нами было решено рассмотреть задачи на создание таблиц с использованием элементов управления, а также решить систему уравнения средствами Excel .

ГЛАВА II . ИССЛЕДОВАНИЕ ВОЗМОЖНОСТЕЙ MS EXCEL

2.1. Создание таблиц с использованием элементов управления

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

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

Для вставки элементов управления на лист необходимо отобразить вкладку Разработчик .

В MS Excel 2007 это можно сделать через меню Кнопка офис/ Параметры Excel / Основные/ Показывать вкладку Разработчик на ленте.

Для добавления какого либо элемента управления на вкладке Разработчик в группе Элементы управления используется кнопка Вставить .

Выделяют следующие элементы управления:

Надпись

Переключатель

Текстовое поле

. Список

Рамка

Поле со списком

Кнопка

Поле с раскрывающимся списком

Флажок

Полоса прокрутки

Свойства элемента управления

Текст программы

Счетчик

Сетка

Отобразить окно

ЗАДАНИЕ 1. Используя данные Приложения 1, создать таблицу транспортных перевозок из Екатеринбурга в города Свердловской области с учетом тарифа перевозок и оформить ее с помощью элементов управления; выполнить соответствующие вычисления, используя формулы и вычисления. Предполагаемый результат: При выборе города назначения автоматически определяется расстояние, а при выборе веса груза должна рассчитываться стоимость перевозки с учетом указанного тарифа.

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

Далее мы будем работать с ячейками, выделенные жёлтым и красным цветом.

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

Перенесём элемент управления на лист электронной таблицы и придадим ему нужный размер (рис.2).

Затем из вызванного контекстного меню элемента управления выбираем команду Формат объекта. Появится окно Формат элемента управления (рис.3).

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

В поле Связь с ячейкой вводим ячейку, на которую будет ссылаться формула (А1). Указываем Количество строк списка , выводящихся на экран. Установим флажок Объемное затенение (по желанию). Нажимаем ОК.

Теперь, чтобы автоматически отобразить расстояние, воспользуемся функцией ИНДЕКС. В ячейку В7 ( Расстояние, км ) введем формулу: =ИНДЕКС(В10:В50;А1), в которой указывается столбец ссылка на ячейку, содержащая номер строки.

Аналогично, добавляем элементы управления в ячейку С7 ( Вес груза) , привязывая ее к ячейке связи В1, а в ячейку D 7 введём формулу =ИНДЕКС( F 10: F 16;В1).

В ячейку F 7 введём формулу для вычисления стоимости перевозки: =В7* D 7.

Конечный вариант таблицы изображён на рисунке 4.

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

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

2.2. Решение систем уравнений средствами электронных таблиц

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

Для графического решения подойдут средства построения диаграмм.

Рассмотрим конкретный пример.

ЗАДАНИЕ 2 . Решить систему уравнений

Преобразуем данную систему:

Для оценки решений воспользуемся диаграммой, на которой отобразим графики обеих функций. Для этого, на рабочем листе (Лист1) MS Excel создадим таблицу со следующими значениями (рис.5):

1 строка – строка заголовков;

столбец А: заполняем ячейки А2:А22 числами от -10 до 10 с шагом 1;

при заполнении столбца В в ячейку В2 заносим формулу =А2-1 , которую затем копируем до ячейки В22;

при заполнении столбца С в ячейку С2 заносим формулу =А2*А2-3, копируем ее до ячейки С22.

Рис.5. Таблица с данными для приблизительного поиска решений

С помощью мастера диаграмм выберем тип диаграммы График и построим диаграмму первоначальной оценки решений (рис. 6).

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

Рис.6. Диаграмма первоначальной оценки решения

Поэтому, для уточнения значения решений (Лист 2) построим графики в интервалах от -3 до -1, где находится первое решение, и от 0 до 2, где находится второе решение с шагом 0,1 (рис. 7).

Рис.7. Таблицы с данными для уточнения решений

Составляем новую таблицу для - 2 ≤ x ≤ 0 (рис.7). Строим точечную диаграмму для получения первого решения (рис.8).

Рис.8. Поиск первого решения

Составляем новую таблицу для 1 ≤ x ≤ 3 (рис.7) . Строим точечную диаграмму для получения второго решения (рис.9).

Рис.9. Поиск второго решения

Решением нашей системы будут координаты точек пересечения графиков:(–1;–2) и (2;1).

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

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

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

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

Основное назначение табличного процессора – автоматизация расчётов в табличной форме.

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

Когда в повседневной жизни встречаются ситуации, связанные с обработкой информации в объёмах, неподъёмных ручками, но не требующих применения дорогих и тяжелых программных продуктов, типа 1С, САПР и тому подобное, в этом поможет Excel .

Ефимова О.В., Морозов В.В., Угринович Н.Д. Курс компьютерной технологии с основами информатики. –М.: АБФ, ACT, 1999. –482 с.

Каратыгин С. и др. Базы данных: Простейшие средства обработки информации. Электронные таблицы. Системы управления базами данных. Т.1 /Каратыгин С., Тихонов А., Долголаптев В. –М.: ABF, 1995. –533 с.

Практикум по экономической информатике: Учебное пособие. Часть 1. /Под ред. Шуремова Е.Л., Тимаковой Н.А., Мамонтовой Е.А. – М.: Изд-во «Перспектива», 2000. – 300 c.

Попов А.А. Excel: практическое руководство. - М.: ДЕСС КОМ, 2001. – 301 с.

Microsoft Excel. Версия 2002. Шаг за шагом: Практ. пособие/ Пер. с англ. – М.: Издательство ЭКОМ, 2003. – 368 с.

Стоцкий Ю., Васильев А., Телина И. Office 2007. Самоучитель. – Спб.: Питер, 2007. – 524 с.

Читайте также: