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

Табличный процессор. Формулы и функции. Наиболее часто  встречающиеся функции. Типы ссылок. Операции копирования и перемещения  формул. Правило относительной ориентации клетки.

 

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

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

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

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

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

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

1. Решать математические задачи: выполнять разнообразные табличные вычисления, вычислять значения функций, строить графики и диаграммы и т.п.;
2. Осуществлять численное исследование (Что будет, если? Как сделать, чтобы?);
3. Проводить статистический анализ;
4. Реализовать функции базы данных – ввод, поиск, сортировку, фильтрацию (отбор) и анализ данных;
5. Устанавливать защиту на отдельные фрагменты таблицы, делать их невидимыми;
6. Наглядно представлять данные в виде диаграмм и графиков;
7. Вводить и редактировать тексты;
8. Осуществлять обмен данными с другими программами, например, вставлять текст, рисунки, таблицы, подготовленные в других приложениях;
9. Осуществлять многотабличные связи.

 

Внешний вид электронных  таблиц:

 

В электронных таблицах можно  оформлять и выполнять как  простые, так и сложные расчеты.

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

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

Абсолютная ссылка в формуле  используется для указания фиксированного адреса ячейки. При перемещении или  копировании формулы абсолютные ссылки не изменяются. В абсолютных ссылках перед неизменяемым значением адреса ставится знак доллара: $А$1.

Функция Автозаполнения позволяет заполнять (копировать) данными область ячеек по введенным данным или формулам.

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

 

Формулой в Excel называется последовательность символов, начинающаяся со знака равенства «=», в которую могут входить постоянные значения, ссылки на ячейки, имена, функции или операторы. Результатом работы формулы является новое значение, получаемое по уже имеющимся данным. Если значения в ячейках, на которые есть ссылки в формулах, меняются, то результат изменяется автоматически.

 

Понятие функции

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

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

 

Правила синтаксиса при записи функций

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

Аргументы функции записываются в круглых скобках сразу после названия функции и отделяются друг от друга точкой с запятой «;». Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Нельзя вставлять пробелы между названием функции и скобками. В этом случае Excel выдаст сообщение об ошибке «#ИМЯ?».

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

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

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

Мастер функций вызывается командой Вставка/Функции или нажатием кнопки Мастер функций на панели инструментов Стандартная.

 

Внесение изменений  в формулу

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

Для внесения изменений в  формулу надо щелкнуть мышью в  строке формул или нажать клавишу «F2». Затем следует внести изменения и щелкнуть мышью на кнопке ввода в строке формул или нажать клавишу «Enter». Если надо внести изменения в формулу непосредственно в ячейке. Где она записана, то необходимо дважды щелкнуть мышью на ячейке с этой формулой.

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

 

Использование ссылок

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

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

В ссылке на ячейку указываются  заголовки строки и столбца рабочего листа соответственно. В Excel столбцы помечены латинскими буквами (А, В, С, D, ... Z; АА, АВ, AC, AD,... AZ; ВА, ВВ, ВС и т.д.), а строки пронумерованы. Такие ссылки называются ссылками типа А1.

 

Перемещение, копирование  и распространение формул

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

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

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

быть как вся ссылка, так и ее часть. Например, если записать D$7, то абсолютным будет только номер строки 7; запись $D7 означает неизменяемость символа столбца D; а запись $D$7 - что вся ссылка является абсолютной. Если в формуле указан интервал ячеек G3:L9, то управлять можно каждым из четырех символов: «G», «3», «L» и «9», помещая перед ними символ «$».

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

 

Формулы преобразования текста

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

строками текста в Excel обеспечивают функции категории Текстовые. Они позволяют:

· заменять отдельные символы в тексте;

· искать вхождение одного фрагмента текста в другой с учетом регистра;

· заменять новым текстом старый текст в текстовой строке;

· возвращать заданное число символов из строки текста;

· преобразовывать число в текст и наоборот;

· проверять идентичность двух текстов;

· объединять несколько текстовых фрагментов в один;

· определять количество символов в текстовой строке;

· определять числовой код первого символа в текстовой строке;

· определять самые левые и самые правые Символы текста;

· определять символ по его коду;

· удалять все непечатаемые символы из текста;

· повторять фрагмент текста заданное число раз;

· приводить все буквы в тексте к верхнему регистру;

· приводить к верхнему регистру первую букву в каждом слове текста;

· удалять из текста пробелы;

· приводить все буквы в тексте к нижнему регистру.

 

Логические функции

Логические функции являются неотъемлемыми компонентами многих формул. Они используются всякий раз, когда необходимо выполнить те или иные действия в зависимости от выполнения каких-либо условий.

В Excel имеются следующие логические функции: ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ.

Функция ЕСЛИ(арг_лог,арг1,арг2) возвращает значение арг1, если арг_лог при вычислении приобретает значение ИСТИНА, и арг2, если арг_лог приобретает значение ЛОЖЬ.

Если арг_лог имеет значение ИСТИНА и значение арг1 опущено, то возвращается значение ИСТИНА. Если арг_лог имеет значение ЛОЖЬ и значение арг2 опущено, то возвращается значение ЛОЖЬ.

При конструировании более  сложных проверок в качестве значений аргументов арг1 и арг2 могут быть вложенными до семи функций ЕСЛИ.

 

Правило относительной  ориентации клетки

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

Клетка со ссылкой С2 содержит формулу-шаблон сложения двух чисел, находящихся в ячейках А1 и В4. Эти ссылки являются относительными и отражают ситуацию взаимного расположения исходных данных в ячейках А1 и В4 и результата вычисления по формуле в ячейке С2. По nравилу относительной ориентации клеток ссылки исходных данных воспринимаются системой не сами по себе, а так, как они расположены относительно клетки С2: ссылка А1 указывает на клетку, которая смещена относительно клетки С2 на одну клетку вверх и на две клетки влево; ссылка В4 указывает на клетку, которая смещена относительно клетки С2 на две клетки вниз и одну клетку влево.

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

1.                     Биллиг В.А., Дехтярь М.И. VBA и Office ХР. Офисное программирование. –М.: Русская редакция, 2004. –693 с.  
2.                     Гарнаев А. Использование MS Excel и VBA в экономике и финансах. –СПб.: БХВ–Петербург, 2002. –420 с.  
3.                     Ефимова О.В., Морозов В.В., Угринович Н.Д. Курс компьютерной технологии с основами информатики. –М.: АБФ, ACT, 1999. –482 с.  
4.                     Каратыгин С. и др. Базы данных: Простейшие средства обработки информации. Электронные таблицы. Системы управления базами данных. Т.1 /Каратыгин С., Тихонов А., Долголаптев В. –М.: ABF, 1995. –533 с.  
5.                     Ковальски С. Excel 2000 без проблем. – М.: Бином, 2000. –210 с.  
6.                     Информатика: учебник. Курносов А.П., Кулев С.А., Улезько А.В., Камалян А.К., Чернигин А.С., Ломакин С.В.: под ред. А.П. Курносова Воронеж, ВГАУ, 1997. –238 с.  
7.                     Информатика: Учебник. /Под ред. Н.В. Макаровой – М.: Финансы и статистика, 2002. –768 с.  
8.                     Пакеты прикладных программ: Учеб. пособие для сред, проф. образования / Э. В. Фуфаев, Л. И. Фуфаева. —М.: Издательский центр «Академия», 2004. –352 с.