Процессор электронных таблиц microsoft excel. Табличный процессор microsoft excel. Очистка данных в ячейках и удаление ячеек

1. Понятие табличного процессора

2. Краткая характеристика MS Excel

3. Назначение MS Excel

Список использованной литературы

1. Понятие табличного процессора

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

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

Функции табличных процессоров весьма разнообразны:

· создание и редактирование электронных таблиц;

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

· оформление и печать электронных таблиц;

· построение диаграмм, их модификация и решение экономических задач графическими методами;

· создание многотабличных документов, объединенных формулами;

· работа с электронными таблицами как с базами данных: сортировка таблиц, выборка данных по запросам;

· создание итоговых и сводных таблиц;

· использование при построении таблиц информации из внешних баз данных;

· создание слайд-шоу;

· решение оптимизационных задач;

· решение экономических задач типа “что – если” путем подбора параметров;

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

Наиболее популярными электронными таблицами для персональных компьютеров являются табличные процессоры Microsoft Excel, Lotus 1-2-3, Quattro Pro и SuperCalc. И если после своего появления в 1982 году Lotus 1-2-3 был фактически эталоном для разработчиков электронных таблиц, то в настоящее время он утратил свои лидирующие позиции. Результаты тестирования продемонстрировали явное преимущество Excel по многим параметрам .

2. Краткая характеристика MS Excel

Microsoft Excel (также иногда называется Microsoft Office Excel) - программа для работы с электронными таблицами, созданная корпорацией Microsoft для Microsoft Windows, Windows NT и Mac OS. Она предоставляет возможности экономик-статистических расчетов, графические инструменты и, за исключением Excel 2008 под Mac OS X, язык макропрограммирования VBA (Visual Basic для приложений). Microsoft Excel входит в состав Microsoft Office и на сегодняшний день Excel есть одним из наиболее популярных программ в мире .

Ценной возможностью Excel есть возможность писать код на основе Visual Basic для приложений (VBA). Этот код пишется с использованием отдельного от таблиц редактора. Управление электронной таблицей осуществляется с помощью объектно-ориентированной модели кода и данных. С помощью этого кода данные входных таблиц будут мгновенно обделываться и отображаться в таблицах и диаграммах (графиках). Таблица становится интерфейсом кода, разрешая легко работать изменять его и руководить расчетами.

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

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

В Microsoft Excel есть два основных типа объектов: книга и письмо.

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

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

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

В Microsoft Excel очень много разнообразных функций, среди которых такие :

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

2. Функции даты и времени – большинство функций этой категории ведает преобразованиями даты и времени в разные форматы. Две специальные функции СЕГОДНЯ и ТДАТА вставляют в каморку текущую дату (первая) и дату и время (вторая), обновляя их при каждом вызове файла или при внесение любых изменений в таблицу.

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

6. Тексту – В этой группе десятка два команд. С их помощью можно сосчитать количество символов в воротничке, включая пробелы (ДЛСТР), узнать код символа (КОДСИМВ), узнать, какой символ стоит первым (ЛЕВСИМВ) и последним (ПРАВСИМВ) в строке текста, поместить в активную каморку некоторое количество символов из другой воротнички (ПСТР), поместить в активную каморку весь текст из другого каморки большими (ПРОПИСН) или сточными буквами (СТРОЧН), проверить, или совпадают две текстовые каморки (СОВПАД), найти некоторый текст (ПОИСК, НАЙТИ) и заменить его другим (ЗАМЕНИТЬ).

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

8. Работа с базой данных – здесь можно найти команды статистического учета (БДДИСП - дисперсия по выборке из базы, БДДИСПП - дисперсия по генеральной совокупности, ДСТАНДОТКЛ - стандартное отклонение по выборке), операции со столбцами и строками базы, количество непустых (БСЧЕТА) или (БСЧЕТ) ячеек и т.д.

9. Мастер диаграмм – встроенная программа EXCEL, что упрощает работу с основными возможностями программы.

3. Назначение MS Excel

табличный процессор формула текст

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

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

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

Табличный процессор Microsoft Excel – это программа, предназначенная для проектирования электронных таблиц и выполнения различных расчётов в них, решения математико-статистических и экономико-математических задач и др.

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

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

Электронная таблица – это двумерный массив – матрица, состоящая из столбцов и строк. Столбцы обозначаются буквами латинского алфавита A, B, C, D, … , а строки - цифрами 1, 2, 3, 4, … В Excel можно сформировать таблицу (матрицу) максимальным размером 256 столбцов X 16 384 строк.

В одной книге (файле) по умолчанию имеется 16 рабочих листов, имеющих имена: Лист1, Лист2, … , Лист16. Каждый лист содержит одну электронную таблицу. Вход в рабочие листы книги осуществляется нажатием на кнопки с их именами. Перелистывание книги осуществляется кнопками прокрутки, расположенными на одной линии с именами листов: «I◄ », «◄», « », « I ».

Алгоритм переименования листа: выделить старое имя листа двойным щелчком левой кнопки мыши à удалить старое имя клавишей «Delete» à ввести новое имя.

Алгоритм добавления листа: меню «Вста вка» à команда «Л ист» à перед именем текущего рабочего листа появляется имя нового листа.

Алгоритм удаления текущего листа: меню «П равка» à команда «Удалить л ист». Команда «Удалить л ист», после её выполнения не отменяется.

Место пересечения столбца и строки электронной таблицы называется ячейкой. Каждая ячейка имеет свой адрес (или имя), состоящий из имени столбца и номера строки, например: A1, D3, F5 и т.д.

Группа рядом расположенных ячеек может объединяться в блок. Адрес (или имя) блока обозначается адресами двух угловых ячеек, записываемых через двоеточие. Например: блок B2:D3, состоит из шести ячеек: B2, C2, D2, B3, C3, D3.

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

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

Правая часть отражает содержимое активной ячейки. Средняя часть содержит три кнопки:

«X » - отмена последнего действия (дублирование клавиши «Esc»;

«√ » – ввод, выполняет функцию клавиши «Enter»;

«= » – вводит знак «= » в правую часть строки формул, одновременно в левой части строки формул появляются функции, которые можно использовать для создания формул.

Формула – это выражение служащее для выполнения математической и (или) логической операции. В состав формул входят: адреса ячеек, числовые константы, арифметические операции и функции.

Запись формулы производится одновременно в активную ячейку и строку формул. После записи формулы нажимается клавиша Enter, что приводит к появлению в активной ячейке результата, выполненной по формуле операции.

Запись формулы начинается со знака «=». Примеры записи формул. Операции сложения и вычитания: =B5+C5+D5- E5. Операции умножения и деления: =B6*C6/D6. Операции с использованием скобок: =(B7+C7)/D7. Операция вычисления процента: =E5/D5%. Операция возведения в степень: =B5^C5, где: B5 – число, возводимое в степень, C5 – число, показатель степени.

Для вычисления итоговых сумм по столбцам и строкам применяется команда «Авто сумма». Алгоритм авто суммирования: выделяется столбец (или строка) вместе с ячейкой, в которой должна быть подсчитана сумма à кнопка «S » – «Авто сумма» à в ячейке появляется итоговая сумма.

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

#### - ширина ячейки не позволяет отобразить число в заданном формате.

#ДЕЛ/0! – выполнение деления на ноль.

#ИМЯ? – неверный набор имени ячейки. Возможно, для набора имён ошибочно использовалась кириллица, а не латиница.

#ЧИСЛО! – неверное применение математических формул. Например, извлечение квадратного корня из отрицательного числа.

#ЗНАЧ! – использован недопустимый тип аргумента. Например: привлечение ячейки, в которой находится текст, к выполнению арифметической операции.

#ПУСТО! – неверное указание пересечения двух областей, не имеющих общих ячеек.

Функция – это выражение позволяющее проводить сложные математические или логические вычисления. Синтаксически функция состоит из имени и аргумента, который записывается в скобки. Например, функция суммирования чисел ячеек B5+C5+D5+E5+G5 может быть записана так: СУММ(B5:G5). Если перед именем функции записать знак равенства «=», то функция превращается в формулу: = СУММ(B5:G5).

Для вставки функций в формулы используется кнопка fx – «Вставка функций». При нажатии на кнопку открывается окно «Мастер функций». В Excel используется более двухсот функций, которые подразделяются на следующие категории: финансовые, дата и время, математические, статистические, текстовые, логические и другие. Некоторые из этих функций будут рассмотрены ниже на примере выполняемых заданий.

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

Задание 14. Создать таблицу:

Расчёт доплат водителям за класс и стаж работы

Алгоритм создания таблицы:

1. Ввод заголовка таблицы: установить указатель ячейки на ячейку В1 à ввести текст заголовка: «Расчёт доплат водителям за класс и стаж работы» à клавиша «Enter» (или щелчок мыши за пределами строки ввода).

2. Ввод имён показателей шапки таблицы : Расширить столбец А до размера, позволяющего вводить в графу «Фамилия И.О. водителя», соответствующие показатели. à Ввести текст в ячейки; А2 - «Фамилия И.О.», А3 - «водителя», В2 – «Оклад», С2 – «% доплаты за:», С3 – «класс», D3 – «стаж» и т.д.

3. Форматирование ячеек информационной части таблицы : графу «Фамилия И.О. водителя» не форматировать à в графе «Оклад» выполнить: выделить информационную часть графы à меню «Форм ат» à команда «Я чейки …» à в окне «Формат ячеек» выделить пункт «Число» àв меню «Числовые форматы:» выбрать формат «Числовой» à «Число десятичных з наков:» установить 2 à клавиша ОК. Аналогично отформатировать ячейки остальных числовых граф.

4. Ввод входных данных .

5. Ввод формул расчёта выходных данных : в ячейку E4 вводится формула =B4*C4/100 à в ячейку F4 - формула = B4*D4/100 à в ячейку G4 - формула = B4+E4+F4. Алгоритм копирования формулы: установить указатель ячейки на ячейку с формулой à меню «П равка» à команда «К опировать» à выделить ячейки, в которые копируется формула à меню «П равка» à команда «В ставить». Итоги по графам таблицы подсчитать, используя команду «Авто сумма».

6. Обрамление таблицы: выделить обрамляемый блок ячеек à меню «Форм ат» à команда «Я чейки …» à в окне «Формат ячеек» выделить пункт «Граница» à в диалоговом окне выбирается расположение обрамления, толщина линий и другие параметры. Если шапка таблицы сложная многоуровневая, то вышеуказанные операции могут повторяться в разных частях шапки многократно до достижения желаемого результата.

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

Рассмотрим примеры записи некоторых функций. Для удобства функции записываются в виде формул. В приводимых примерах перед формулой в кавычках указывается адрес ячейки, в которую вводится формула и выводится результат вычисления, например – «В1».

«В1» =КОРЕНЬ(А1) – извлечение квадратного корня. Если в ячейку А1 ввести 123, то в ячейке В1 выведется результат 11,09054.

«С1» =ОТБР(В1) – отбрасывание дробной части числа (выделение целой части числа). Если в ячейке В1 находится число 11,09054, то в ячейке С1 выведется результат 11.

«С2» =ОСТАТ(А2;В2) – вычисление остатка, получаемого в результате деления числа ячейки А2 на число ячейки В2. Если ввести в ячейку А2 число 17, в ячейку В2 число 6, то в ячейке С2 выведется результат 5.

«В3» =РИМСКОЕ(А3) – преобразование числа из арабской формы записи в римскую. Если в ячейку А3 ввести число 1468, то в ячейке В3 выведется результат MCDLXVIII.

«С4» =СТЕПЕНЬ(А4;В4) – возведение числа ячейки А4 в степень ячейки В4. Если ввести в ячейку А4 число 5, в ячейку В4 число 3, то в ячейке С4 выведется результат 125.

«В13» =СУММЕСЛИ(А1:А10;1;В1:В10) – вычисление суммы чисел блока ячеек В1:В10 по критерию 1, введённому в блок ячеек А1:А10 (см. рис. 3). В ячейке В13 накопится и выведется сумма чисел ячеек В1+В4+В7+В10 = 12+19+17+13 = 61.

Критерий – это число или оператор, определяющие адреса ячеек блока для суммирования. Если число записывается без кавычек, то оператор записывается в кавычках и отражает одну из операций отношения: “=”, “>” , “<”, “>=”, “<=”, “<>”.

«В14» = СУММЕСЛИ(А1:А10;”>1”;В1:В10) – вычисление суммы чисел блока ячеек В1:В10 по критерию, значение которого больше 1, введённому в блок ячеек А1:А10 (см. рис. 3).В ячейке В14 накопится и выведется сумма чисел ячеек В2+В3+В5+В6+В8+В9 = 15+11+14+18+16+15 = 89.

«В15» =МАКС(В1:В10) – определение максимального числа в блоке ячеек В1:В10 (см. рис. 3). В ячейке В15 выведется число 19.

«В16» =МИН(В1:В10) – определение минимального числа в блоке ячеек В1:В10 (см. рис. 3). В ячейке В16 выведется число 11.

«В17» =ЕСЛИ(А1=1;В1+В4+В7+В10;В2+В5+В8) – вычисление одной из двух сумм в зависимости от условия. Если условие А1=1 выполняется, то в ячейке В17 (см. рис. 3) накопится и выведется сумма чисел ячеек В1+В4+В7+В10 = 12+19+17+13 = 61. Если условие А1=1 не выполняется, то в ячейке В17 накопится и выведется сумма чисел ячеек В2+В5+В8 = 15+14+16 = 45.

Функция «ЕСЛИ» позволяет создавать разветвления. В неё можно вкладывать функции «И» и «ИЛИ».

«В18» = ЕСЛИ(И(А2>А1;А3>А2);В1*В2;В3*В4) – здесь операция В1*В2 выполнится только в том случае если оба условия выполнимы, иначе выполнится операция В3*В4.

Данные таблиц можно сортировать. Для этого необходимо выделить информационную часть таблицы, в пределах которой предполагается провести сортирование, и нажать на одну из двух кнопок с соответствующим названием: «Сортировка по возрастанию» или «Сортировка по убыванию». Сортирование будет произведено по показателю графы (или части графы), в которой после выделения остался указатель ячейки.

Сортирование можно провести по алгоритму: меню «Д анные» à команда «Со ртировка …» à в диалоговом окне «Сортировка диапазона» задать необходимые параметры.

Построение диаграмм, на основе использования данных таблиц, осуществляется при помощи кнопки «Мастер диаграмм» или по алгоритму: меню «В ид» à команда «Панели и нструментов 4» à пункт «Диаграммы» à на появившейся панели инструментов «Диаграммы» задать необходимые параметры.

Алгоритм печати таблицы: меню «Ф айл» à команда «П ечать…» à в окне «Печать» задать необходимые параметры à кнопка ОК.

Если таблица превышает размеры листа бумаги, то перед печатью её необходимо разбить на страницы по алгоритму: меню «С ервис» à команда «Па раметры…» à в окне «Параметры» отметить галочкой пункт «а вто разбиение на страницы» à границы страниц отметятся пунктирными линиями.

Чтобы увидеть, как будет выглядеть таблица перед выводом на печать, нажимается кнопка «Предварительный просмотр». Если таблица разбита на множество страниц, то для их просмотра используются кнопки «Далее» и «Назад». Для выхода из режима просмотра нажимается кнопка «Закрыть».

Задание 15. Создать таблицу и построить диаграмму. Коэффициент сезонности определяется как отношение максимального среднемесячного к среднегодовому числу работников. Вычисляется с точностью до двух десятичных знаков. Данные таблицы рассортировать по возрастанию по показателю «В среднем за период» и построить вторую диаграмму.

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

Задание 16. Сформировать документ «Ведомость расчёта и выдачи стипендии». Входные данные: Фамилии И.О. студентов, оценки за четыре экзамена. Выходные данные: средний балл, сумма начисленной стипендии. Сумма начисленной стипендии зависит от среднего балла (СБ). При СБ равном 5,0 – 500 руб.; при СБ большем или равном 4,5, но меньшем 5,0 – 400 руб.; при СБ большем или равном 3,5, но меньшем 4,5 – 300 руб.; при СБ меньшем 3,5 – стипендия не начисляется.

Ведомость должна иметь не менее 10 строк и итоговую строку.

Задание 17. Создать таблицу «Справка о работе звена комбайнёров на уборке зерновых культур». В строку «Max» вывести Фамилию И.О. комбайнёра, намолотившего наибольшее количество зерна. В строку «Min» - Фамилию И.О. комбайнёра, намолотившего наименьшее количество зерна. В этих же строках вывести и количества намолоченного ими зерна.

Справка о работе звена комбайнёров на уборке зерновых культур

Задание выполнить несколькими способами.

1. Используя функцию «ЕСЛИ».
2. Используя функцию «ПРОСМОТР».
3. Используя функции «ИНДЕКС» и «ПОИСКПОЗ».
4. Попытайтесь найти свой вариант выполнения задания.

Задание 18. Дан список сотрудников СХПК «Колос» на 01.01.15. Определить возраст и стаж работы каждого сотрудника, средний возраст и средний стаж работы сотрудников, количество сотрудников имеющих возраст ниже среднего, количество сотрудников имеющих стаж работы ниже среднего.

Задание 19. Сформировать документ:

Ведомость итогов экзаменационной сессии

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

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

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

Функции табличных процессоров весьма разнообразны:

создание и редактирование электронных таблиц;

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

оформление и печать электронных таблиц;

построение диаграмм, их модификация и решение экономических задач графическими методами;

создание многотабличных документов, объединенных формулами;

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

создание итоговых и сводных таблиц;

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

создание слайд-шоу;

решение оптимизационных задач;

решение экономических задач типа “что – если” путем подбора параметров;

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

Наиболее популярными электронными таблицами для персональных компьютеров являются табличные процессоры Microsoft Excel, Lotus 1-2-3, Quattro Pro и SuperCalc. И если после своего появления в 1982 году Lotus 1-2-3 был фактически эталоном для разработчиков электронных таблиц, то в настоящее время он утратил свои лидирующие позиции. Результаты тестирования продемонстрировали явное преимущество Excel по многим параметрам.

3. Классификация элементов объекта

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

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

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

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

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

Рассмотрим основные пункты меню Excel 7.0 При открытии или создании новой рабочей книги в верхней части экрана располагается основное меню Excel. Оно состоит из 9 раскрывающихся подменю которые в свою очередь содержат пункты меню или раскрывающиеся подменю второго порядка.

Основное меню имеет следующие подменю сгруппированные по типам работы.

Файл - Подменю для работы с файлами, их открытия закрытия и сохранения.

Правка - Подменю для работы с клетками таблицы.

Вид - Подменю для изменения внешнего вида таблицы и представления данных.

Вставка - Подменю для добавления ячеек, строк, столбцов и других элементов.

Формат - Подменю для изменения вида представляемых данных в таблице.

Сервис - Подменю проверок и изменения настроек системы.

Данные - Подменю обработки данных таблицы их сортировки и группировки.

Окно - Подменю изменения вида окна и переходов между окнами.

Подменю помощи

Подробная характеристика элементов объекта

Табличный процессор Excel фирмы Microsoft предназначен для ввода хранения обсчета и выдачи больших объемов данных в виде удобном для анализа и восприятия информации. Все данные хранятся и обрабатываются в виде отдельных или связанных таблиц. Одна или несколько таблиц составляет "рабочую книгу", в этом случае таблицы называются рабочими листами этой книги. Листы можно удалять добавлять или перемещать из одной рабочей книги в другую. Физически на диске сохраняется вся книга в виде отдельного файла с расширением "xls".

Так как Excel – табличный редактор, то и основную информацию я буду отображать в таблицах. Файл:

1.Назначение, функции.

Каждая ячейка имеет адрес. Адреса могут быть текущими и фиксированными. Столбцы нумеруются буквами латинского алфавита, а строки – цифрами.

Фиксированный адресат отмечается знаками $. Текущий адресат показывает просто координаты столбца и ячейки.

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

Основные функции:

Защита данных. Организована через меню Файл в диалоговом окне Сохранение и реализовано с помощью паролей на открытие файлов, разрешение записи и чтение.

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

Ввод формул. Excel интерпретирует вводимые данные либо как текст (выравнивание по левому краю), либо как числовое значение (выравнивание по правому краю), либо в виде формулы со ссылками на ячейки данных. Для редактирования формул необходимо использовать клавишу F2 или двойной щелчок мышью.

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

Групповые имена. Для упрощения задач, связанных с целой группой ячеек, используются групповые имена. Для присвоения имени выделенной группе ячеек в меню Вставка открывается подменю Имя и директива Присвоить. Имя группы должно начинаться с буквы и содержать не более 255 символов. Не допускается использование пробелов, также имя не должно совпадать с адресами ячеек. Заголовки строк и столбцов также могут использоваться в качестве имён этих областей. Реализуется с помощью директивы Создать из меню ВставкаИмя. Адресация может осуществляться ссылкой на другой лист в той же книге, ссылкой на лист в другой книге, при этом Excel автоматически обновляет ссылки только для открытых книг. Когда книга закрыта, ссылка содержит полный путь к данным. Эффективное использование ссылок обеспечивает:

Слияние данных нескольких книг

Создание различных представлений одних и тех же данных

Последовательную разработку больших и сложных моделей обработки данных

Построение и оформление диаграмм. Excel обладает специальным конструктором, позволяющим улучшить качество документа и разрабатывать трёхмерные диаграммы. Конструктор называется ChartWizard. Он обеспечивает построение, выбор типов, организацию надписей на осях, внедрение дополнительных объектов и актуализацию диаграммы.

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

Создание простых баз данных. Для организации поиска записей в базе данных применяются фильтры. Фильтры могут быть использованы только для одной базы данных на листе. Фильтры находятся в меню Данные.Реализуются фильтры в двух вариантах:

Автофильтр

Расширенный фильтр

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

Структурная схема табличного процессора Excel.

Главное меню

Автоформат

Автошаблон

Дополнения

2.Дополнительные средства Excel.

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

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

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

Создание таблицы подстановки с двумя переменными: используют одну формулу с двумя наборами значений, при этом формула должна ссылаться на 2 различных ячейки ввода. Ниже формулы вводят значение подстановки для первой переменной. Значение подстановки для второй переменной в строку правее формулы. Далее выделяют диапазон ячеек, содержащий формулу, и оба набора данных подстановки. Далее входят в меню ДанныеТаблица и выбирают подставлять значение по столбцам или строкам.

Мастер подстановок .

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

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

Для решения оптимизационных задач в Excel используются 2 алгоритма математического программирования:

Метод Ньютона.

Метод Градиентного спуска.

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

Модуль анализа данных (используется в математической статистике) . Может использоваться для решения статистических задач.

ТАБЛИЧНЫЙ ПРОЦЕССОР EXCEL И

ЯЗЫК ПРОГРАММИРОВАНИЯ

VISUAL BASIC

Методические указания

Для студентов заочного отделения

Часть 1

Составители: асс. Л.Ю. Кошкина

доц. Понкратова С.А.

ст. преп. Смирнова М.А.

Табличный процессор Excel и язык программирования Visual Basic : Методические указания для студентов заочного отделения. Часть 1 / Казан. гос. технол. ун-т; Казань, 2003, с. 56

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

Рассмотрены вопросы создания программ на языке программирования Visual Basic, встроенного в электронную таблицу Excel, а также создание макросов, диалогов.

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

Подготовлены на кафедре химической кибернетики.

Печатаются по решению Экспертного совета по информатизации.

Рецензенты: проф. Зиятдинов Н.Н.,

доц. Харитонов Е.А.

Работа с табличным процессором Microsoft Excel.

Таблица – форма организации данных по столбцам и строкам.

Электронная таблица – компьютерный эквивалент обычной таблицы.

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

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

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

Табличный процессор Excel позволяет:

1. Решать математические задачи: выполнять табличные вычисления (в том числе как обычный калькулятор), вычислять значения и исследовать функции, строить графики функций (например, sin, cos, tg и др.), решать уравнения, работать с матрицами и комплексными числами и т. п.

2. Осуществлять математическое моделирование и численное экспериментирование. (Что будет, если? Как сделать, чтобы?).

3. Проводить статистический анализ, осуществлять прогнозирование (поддержку принятия решений) и оптимизацию.

4. Реализовать функции базы данных – ввод, поиск, сортировку, фильтрацию (отбор) и анализ данных.

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

6. Наглядно представлять данные в виде диаграмм и графиков.

7. Вводить и редактировать тексты, как в текстовом процессоре, создавать рисунки с помощью графического редактора Microsoft Office.

8. Осуществлять импорт-экспорт, обмен данными с другими программами, например вставлять текст, рисунки, таблицы, подготовленные в других приложениях, и т. п.

9. Осуществлять многотабличные связи (например, объединять отчеты филиалов фирм).

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

Запуск Excel осуществляется через Главное меню Windows с помощью команды Пуск – Программы – Microsoft Excel. Файл программы Excel – excel.exe – обычно находится в папках Program Files – Microsoft Office – Office.

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

Документом, т.е. объектом обработки Excel, является файл с произвольным именем и расширением *.xls. В каждом файле *.xls может размещаться 1 книга , а в книге – от 1 до 255 рабочих листов (электронных таблиц). По умолчанию в каждой книге содержится 3 рабочих листа. Рабочий лист имеет табличную структуру и может состоять из любого числа страниц.

Рабочие листы можно удалять (Правка – Удалить лист ), добавлять новые (Вставка – Лист ), переименовывать (щелкаем по ярлычку листа сначала левой клавишей мыши, затем правой, в появившемся контекстном меню выбираем команду Переименовать ).

Электронная таблица Excel состоит из 65536 строк и 256 столбцов. Строки нумеруются числами (от 1 до 65536), а столбцы обычно обозначаются буквами латинского алфавита A, B, C, …, Z . После столбца Z следуют столбцы AA, AB, AC, BA, BB …IV .

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

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

Ячейка может содержать следующие данные: число, текст, формулы, а также оставаться пустой. Ввод данных осуществляется непосредственно в текущую ячейку или в строку формул. Завершение ввода – нажать . Отмена внесённых изменений клавиша - . Удаление содержимого текущей ячейки - .

Адреса ячеек могут быть относительными (А1, В2:С8 - при копировании, перемещении изменяются) и абсолютными ($A$2, $B$4:$C$9 – при копировании, перемещении остаются неизменными), смешанными (A$2, $B4 – в которых постоянным является только один из компонентов).

Блок (диапазон) ячеек – группа последовательных ячеек.

Блок ячеек может быть выделен:

· Непосредственным набором с клавиатуры начального и конечного адресов ячеек, формирующих диапазон;

· Выделением блока с помощью мыши (несмежные блоки при нажатой клавише ) или клавиш навигации.

Применительно к ячейке и блоку ячеек выполняются следующие действия:

· Форматирование

· Копирование

· Вставка

· Удаление

· Перемещение

· Заполнение

· Очистка форматов, содержимого, значений

Формат ячеек таблицы

Ячейки рабочего листа имеют заданный формат, который устанавливается командойФормат – Ячейки или командой контекстного меню Формат ячеек . Эти команды имеют несколько вкладок: Число, Выравнивание, Шрифт, Граница, Вид, Защита .

· Вкладка Число – задаёт форматы представления данных в ячейке.

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

· Вкладка Шрифт – изменяет шрифт, начертание, размер, цвет, подчеркивание и эффекты текста в выделенных ячейках.

· Вкладка Граница – создаёт рамки (обрамление) вокруг выделенного блока ячеек.

· Вкладка Вид – позволяет задать закраску ячейки (цвет и узор).

· Вкладка Защита – управляет скрытием формул и блокировкой ячеек (запрет редактирования данных ячеек).

Автозаполнение числами

Для заполнения ячеек числами выполнить Правка – Заполнить – Прогрессия. Данную процедуру удобно использовать при табулировании функции.

Пример : Заполнить столбец А числами от 1 до 10 с шагом 0,5.

Вычисления в электронной таблице Microsoft Excel

Формулы

Ввод формул начинается со знака «=».

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

Если строка формул отсутствует, включить её можно с помощью Вид – Строка формул.

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

1. Набрать в строке формул адреса ячеек (=В1+В2);

2. Щёлкнуть левой кнопкой мыши по нужной ячейке;

3. Использование поля имени, где выбирается нужное имя.

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

Построение диаграмм

Диаграмма – это графическое представление числовых данных. Каждая ячейка с данными находит своё отображение на диаграмме – в виде точек, прямоугольников, линий и прочих графических объектов (т.е. точкой данных ).

Для построения диаграммы нужно:

§ Выделить данные в таблице;

§ Нажать кнопку Мастер диаграмм на панели инструментов Стандартная (или Вставка – Диаграмма);

§ В открывшемся окнеМастер диаграмм (шаг 1 из 4): тип диаграммывыбрать нужный тип, вид диаграммы нажать кнопкуДалее;

§ В окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы указать исходные данные (диапазон данных (адрес блока данных таблицы) и их расположение (в строках или в столбцах)); нажать кнопкуДалее;

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

ü В Заголовках можно задать название диаграммы и заголовки осей.

ü В Осях можно показать или отключить оси X и Y.

ü Линии сетки – аналог миллиметровой бумаги – помогают более точно определить значение данных.

ü Легенда – расшифровка линий диаграммы, соответствует именам рядов.

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

ü Таблица данных – можно добавить на график таблицу исходных данных.

§ В окне Мастер диаграмм (шаг 4 из 4): размещение диаграммы указать месторасположение диаграммы. Диаграмму можно разместить на текущем листе (внедренная), либо на отдельном.

§ Завершить создание диаграммы с помощью кнопки Готово.

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

Для изменения размеров диаграммы:

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

2. Отбуксируйте один из привязчиков, изменив таким образом размер диаграммы.

Удаление диаграммы:

1. Выделите её, щелкнув по ней левой кнопкой мыши.

2. Нажмите клавишу Delete.

Тренды

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

Тренд можно строить для диаграмм типа:

§ Линейчатый график;

§ Гистограмма;

§ Диаграмма с областями;

§ XY-точечная.

Построение тренда осуществляется по следующей технологии:

§ Построить диаграмму для одного ряда данных;

§ Выделить эту диаграмму, щелкнув по ней левой кнопкой мыши. На диаграмме должны появится маркеры;

§ Вызвать контекстное меню и выполнить команду Добавить линию тренда;

§ В диалоговом окне Линия тренда на вкладке Тип выбрать тип тренда, а на вкладке параметры установить параметры: Количество периодов прогноза, Показывать уравнение на диаграмме, Поместить на диаграмму величину достоверности аппроксимации;

§ Нажать кнопку ОК.

Выделив линию тренда и вызвав в контекстном меню команду Форматировать линию тренда можно отформатировать, либо удалить линии тренда – команда Очистить.

Пример: Построение тренда.

Для этого:

1. Создайте таблицу в Excel, представленную на рис. 1.

2. Рассчитайте средний балл по информатике и высшей математике.

3. Отформатируйте таблицу.

4. Постройте гистограмму распределения оценок по информатике по группам.

5. Постройте линейный тренд для гистограммы.

6. Постройте полиномиальный тренд для гистограммы.

7. Оформите диаграмму и линии тренда так, как представлено на рисунке 2.

8. Сохраните работу в своей папке.

Рис. 1. Таблица Средний балл для построения диаграммы и тренда

Решение :

1. Создайте таблицу, представленную на рис. 1.

2. Используя Мастер функций, в ячейку В7 введите формулу =СРЗНАЧ(В3:В6). Скопируйте эту формулу в ячейку С7.

· По центру;

· Объединение ячеек – установите флажок.

5. Постройте диаграмму распределения по группам оценок, полученных по информатике. Для этого, вызовите Мастер диаграмм , нажав соответствующую кнопку на панели инструментов. Выберите на вкладке Стандартные обычный тип гистограммы и нажмите кнопку Далее. В строку Диапазон установите курсор и выделите блок ячеек А2:В7. Оформите заголовки и названия осей так, как показано на рис. 2. Закончите построение диаграмм.

6. Постройте линейный тренд для гистограммы. Для этого, установите указатель мыши на один из столбиков гистограммы т щелкните левой кнопкой мыши так, чтобы появились на всех столбиках черные метки. Для выделенной гистограммы вызовите контекстное меню, щелкнув правой кнопкой мыши. Выполните команду Добавить линию тренда . В диалоговом окне Линия тренда на вкладке Тип выберите окошко Линейная. На вкладке Параметры установите параметры:

· Прогноз: вперед на 1 период;

· Показывать уравнение на диаграмме: установите флажок;

· Поместить на диаграмму величину достоверности аппроксимации: установите флажок.

Нажмите кнопку ОК. На диаграмме появится линия тренда и описывающее ее уравнение.

7. Постройте полиномиальный тренд для гистограммы, воспользовавшись технологией п. 5.

8. Оформите диаграмму и линии тренда так, как представлено на рисунке 2.

9. Сохраните файл в своей папке под своим именем.

Рис. 2 . Гистограмма и тренды

Процедура Подбор параметра

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

Решение задачи производится через команду Подбор параметра меню Сервис . В диалоговом окне устанавливается целевой ячейки, её значение, адрес ячейки параметра.

Процедура Подбор параметра доступна только в том случае, если она отмечена в диалоговом окне Надстройки , которое открывается с помощью Сервис – Надстройки .

Пример : Решить уравнение , используя процедуру Подбор параметра.

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

Решение:

1. В ячейку А1 введём текст «Значение параметра», в А2 – «Уравнение».

2. Ячейке В1 присвоим имя х (Вставка – Имя – Присвоить ) и введем в неё начальное значение х, равное, например 1.

3. В ячейку В2 введём формулу =(х-2)^2*2^x.

4. Выполнить команду Сервис – Подбор параметра .

5. Установить адрес целевой ячейки $B$2, значение целевой ячейки – 1, изменяя значение ячейки $B$1 (рис.3).

Таким образом, процедура Подбор параметра будет автоматически изменять значение в ячейке В1, то есть х, до тех пор, пока значение целевой функции в ячейке В2 не станет равным заданному.

Ответ: х=1,3801 (при у=0,9999).

Матричные операции

Дана система линейных алгебраических уравнений

Найти:

1. определитель;

2. x 1 , x 2 , x 3;

3. матрицу коэффициентов а i , j умножить на число 5.

Решение:

Метод обратной матрицы заключается в следующем: пусть дана система линейных алгебраических уравнений вида А*Х=В, где А – матрица коэффициентов при неизвестных; Х – вектор неизвестных; В – вектор свободных членов. Тогда Х=А -1 *В, где А -1 – обратная матрица матрицы А.

Рис. 4. Матричные операции

1. В ячейки B1:D3 введите матрицу коэффициентов при неизвестных. В ячейки F1:F3 введите вектор свободных членов (рис. 4).

2. Для нахождения определителя матрицы щёлкнем по ячейке B5. Далее в диалоговом окне Мастера функций, которое вызвали при помощи команды меню Вставка – Функция, выберем функцию =МОПРЕД(B1:D3 ) из категории Математические. При помощи мыши выделим матрицу А, находящуюся в ячейках B1:D3. Нажать .

3. Неизвестные x 1 , x 2 , x 3 найдём методом обратной матрицы. Для этого в ячейкахB7:D9 вычислим обратную матрицу. Выделим блок ячеек B7:D9. Далее Вставка – Функция, категория – Математические, функция =МОБР(B1:D3). .

4. Результатом умножения исходной матрицы и обратной матрицы будет единичная матрица, элементы которой равны нулю, кроме элементов главной диагонали, они равны 1. Выделим блок ячеек B11:D13. Далее Вставка – Функция, B1:D3; B7:D9). Нажать . Далее щелкнуть мышью в строке формул. Нажать комбинацию клавиш .

5. Выделим блок ячеекI1:I3, в котором вычислим неизвестные x 1 , x 2 , x 3 . Для этого выполним Вставка – Функция, категория – Математические, функция =МУМНОЖ(B7:D9; F1:F3). Нажать . Далее щелкнуть мышью в строке формул. Нажать комбинацию клавиш .

6. Результатом умножения матрицы на число будет матрица.Выделим блокB15:D17. Перейдём в строку формул, где, начиная со знака равно, введём =B1:D3*5. Нажать .

7. Введите комментарии.

Использование надстроек

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

Наиболее часто используемые надстройки:

§ Пакет анализа;

§ Поиск решения (оптимизация);

§ Автосохранение;

§ Мастер шаблонов.

Поиск решения

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

Решение ЗЛП в Excel осуществляется посредством программы Поиск решения .

Запуск программы Поиск решения осуществляется с помощью команды Сервис – Поиск решения. Эта программа доступна в том случае, если в диалоговом окне Надстройки помечен Поиск решения .

В диалоговом окне Поиск решения находятся несколько окон ввода:

§ Установить целевую ячейку – устанавливается адрес целевой ячейки, в которой находится целевая функция, в виде формулы, ранее введенная.

§ Равной: либо максимальному, либо минимальному значению (включается переключатель), либо значению (указывается в окошечке).

§ Изменяя ячейки – указываются ячейки, участвующие в расчёте целевой функции.

§ Ограничения – после нажатия кнопки добавить появляется окно Добавление ограничения, в котором в левом окне ввода указывается ячейка, затем выбирается знак, в правом окне ввода указывается ограничение.

Задача: Составить штатное расписание хозрасчётной научно-исследовательской лаборатории, т.е. определить сколько сотрудников, на каких должностях и с каким окладом нужно принять на работу. Общий месячный фонд заработной платы составляет 60000 руб. При решении принять во внимание, что для нормальной работы лаборатории необходимо 8-12 лаборантов, 2-4 единицы технического персонала, 4-8 инженера, 1 завхоз, 2 заведующих отделения, 1 заведующий лабораторией.

За основу берётся оклад лаборанта, а все остальные вычисляются исходя из него: =А*Х+В, где Х – оклад лаборанта, А и В – коэффициенты, которые для каждой должности определяются решением совета трудового коллектива. Оклад лаборанта должен быть не менее 1500 руб.

Решение: Заполним таблицу данными, приведенными на рис.5.

Рис. 5. Штатное расписание лаборатории

1. Перейти в ячейку D3. Расчёт заработной платы производится по формуле: =В3*$В$11+С3 (т.е. =А*Х+В). Далее эту формулу скопировать в ячейки D4:D8.

2. Произвести расчёт суммарной заработной платы в ячейках F3:F8.

3. Итоговое значение заработной платы, находящееся в ячейке F9

=СУММ(F3:F8)

4. Воспользуемся командой Сервис – Поиск решения для решения задачи оптимизации, предварительно установив курсор в ячейку F9, предназначенную для целевой функции. Появляется диалоговое окно Поиск решения, в котором надо установить значения в следующие окна ввода:

Рис. 7. Диалоговое окно Добавление ограничения

Появится окно Поиска решения , нажать кнопку Выполнить . Появится окно Результаты поиска решения, нажать кнопку ОК и проанализировать полученные результаты.

Макросы

Макрос – это записанная последовательность команд и действий, сохранённая под уникальным именем, которую может выполнить Excel.

Excel Visual Basic – это язык макросов, т.е. вы можете писать программу на этом языке традиционным образом, а можете записать макрос с помощью команды Сервис – Макрос и использовать текст макроса наравне с остальным текстом вашей программы.

Microsoft Excel имеет встроенное средство для записи макроопределений – макрорекордер. Он записывает последовательность нажатий клавиш и перемещений мыши в виде операторов языка Visual Basic. В дальнейшем макрос можно редактировать, как обычный текстовый файл.

Тексты программ на языке Visual Basic хранятся в специальных листах рабочей книги. Эти листы носят название модулей .

Выполнение макроса

Чтобы выполнить макрос, выберите команду меню Сервис – Макрос – Макросы.

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

Кнопки

Для удобства вызова макроса на выполнение макросу можно назначить кнопки:

§ Кнопка на панели инструментов . Вид – Панели инструментов – Настройка. Среди Команд выбрать Макросы – Настраиваемая кнопка и перенести её с помощью мыши на любую панель инструментов. Для работы с этой кнопкой нажать правую кнопку мыши (Назначить макрос). Редактирование кнопки возможно только в режиме настройки.

§ Командная кнопка . Вид – Панели инструментов – Формы . В панели инструментов Формы нужно выбрать кнопку – Кнопка. С помощью мыши нарисовать кнопку и правой кнопкой мыши Назначить макрос.

§ Графический объект. Вставка – Рисунок – Картинки . Правой кнопкой мыши Назначить макрос.

Объекты

Объект – это то, чем вы управляете с помощью программы на языке Excel Visual Basic:

§ Cell (ячейка),

§ Range (диапазон),

§ Chart (диаграмма),

§ WorkSheet (рабочий лист),

§ WorkBook (рабочая книга).

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

Методы

Метод – это программа, которая выполняет определённые действия над объектом.

Свойства

Свойства – это совокупность характеристик и атрибутов, описывающих объект.

Переменные и константы

Важно различать следующие понятия:

§ Ячейка памяти – это элементы оперативной памяти, в которых хранятся данные в виде байтов.

§ Переменная – именованная область памяти, отведенная для временного хранения данных, которые могут изменяться при выполнении программы.

§ Значение переменной – это содержимое тех ячеек памяти, в которых хранится переменная.

§ Идентификатор – это лишь обозначение переменной, принятое в программе.

§ Константа – именованный элемент, сохраняющий постоянное значение в течение выполнения программы.

Тип данных

Тип указывает, что может хранить переменная: целое число, строку, дату и т.п.

Для эффективного использования памяти необходимо правильно выбрать тип переменной (табл.1).

Табл.1 . Типы данных

Объявление переменных

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

При втором способе неявного объявления переменная декларируется просто указанием её имени в тексте программы.

Приоритет операций

К основным арифметическим операциям относятся: сложение (+), вычитание (-), умножение (*), деление (/).

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

Скобки допускается вкладывать друг в друга произвольное число раз. Использование квадратных или фигурных скобок не допускается.

Ввод данных

Ввод данных в программе

Ввод данных в программе осуществляется при помощи оператора присваивания. Оператор присваивания определяется знаком присваивания. (=), слева от которого располагается идентификатор переменной, а справа – некоторое выражение.

Пример : х=3.

With Элемент_1

Элемент_2

Элемент_3

End With

Пример 2 :

Вывод результатов

Элементарные функции

Табл. 2 . Функции в среде Visual Basic и таблице Excel

Пример 1

где у=2/3, а=27.

Решение в таблице Excel:

1. Переименовать Лист1 в Линейные_процессы,

2. В ячейки А1:А3 ввести комментарии к Примеру 1,

3. Ячейку В1 переименовать в а, В2 – в у,

4. Ввести в В1 число 27, в В2 формулу =2/3,

5. Ввести в ячейку В3 формулу, используя для вставки функций Мастер функций, вставку аргументов в формулу лучше производить при помощи мыши,

6. По завершении ввода формулы нажать Enter.

Пример 2

,

где х=ln2, y=0.28*10 2 .

Решение в таблице Excel:

Создание диалога

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

Для создания диалога (формы) необходимо вставить в рабочую книгу лист с заготовкой нового диалога. Вставка осуществляется

Первый шаг при работе с экранной формой – это установка значений её свойств (рис. 8). Свойство Name (Имя) – имя формы, которое используется в программе для ссылки на экранную форму. Свойство Caption (Заголовок) – текст, который отображается в строке заголовка.

Рис. 8. Окно свойств

Создание всех элементов управления в поле диалога происходит с помощью кнопок на панели инструментов Формы (рис. 10).

Рис. 10. Панель инструментов Формы

Пример:

Создать диалог вычисления z

где у=2/3, а=27.

Решение:

1. Вставить в рабочую книгу лист с заготовкой нового диалога. Сервис – Макрос – Редактор Visual Basic, Insert – User Form (Вставка – Диалог).

2. Вызвать окно свойствView – Properties Window (Вид – Окно свойств) иОкно проекта (View – Project Explorer).