Назначение табличных процессоров

Введение 

      Microsoft Excel входит в состав пакета MS Office и является одним

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

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

Важно отметить, что одни и те же действия, связанные  с обработкой и

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

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

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

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

Табличный процессор Excel

 
 
 
 
Назначение  табличных процессоров

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

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

  Самые известные табличные процессоры — Microsoft Excel, Lotus 1-2-3, Corel, Works. Умение работать с электронными таблицами — решительное преимущество при устройстве на работу в частную компанию.

  Информационная  структура таблицы описывается  строками с номерами 1, 2, 3,... и столбцами с буквами А, В, С,... (рис. 15.1). Поэтому ячейки (или клетки) таблицы обозначаются координатами А2, СЗ, D4, как в шахматах или игре «Морской бой».

  В ячейки можно ввести данные, задать формат их представления (проценты, руб.), установить формулы вычисления зависимых значений. Например, в ячейки столбцов «Цена за штуку», «Количество, шт.» вводят данные, а в ячейки столбца «Общая стоимость» — формулы умножения цены за штуку на количество штук.

  Ячейки  с формулами показывают значения результата вычисления, причем если данные в ячейках столбцов В и С изменить, значения ячеек столбца D будут пересчитаны. Это позволяет изучать сценарии «что, если». Для табличных расчетов характерны относительно простые формулы и большие объемы исходных данных.

  Версии  табличного процессора Microsoft Excel 97/2000/ХР/2003 обмениваться файлами, так как создают их совместимыми по формату. В состав программы Excel входит система управления базами данных (СУБД), текстовый редактор, модуль деловой графики для построения наглядных диаграмм по табличным данным. Название Excel — изящная игра слов: Excellent cells — превосходные ячейки. А еще англ. excel [ik'sel] - превосходить, поэтому можно рекомендовать ударение эксель.

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

Формулы 

Microsoft Excel  обрабатывает данные при помощи формул и встроенных стандартных функций.

      Формула — уравнение или выражение. В ячейке электронной таблицы формула — введенное выражение, всегда начинающееся со знаки равенства = и содержащее далее константы (числа), операторы, функции и ссылки - адреса ячеек. Введенные в формулу адреса ячеек определяют, как значение ячейки зависит отданных в других ячейках текущего листа, листов той же книги или других книг. Например, в ячейке С5 формула =A4*D7 будет умножать данные из ячейки А4 на данные из ячейки D7. В ячейке листа формула видна только в момент ввода или редактирования, а после ввода ячейка выводит результат исчислений. Когда табличный курсор выделяет ячейку с формулой, формулу видно в строке формул.

  В ячейке Excel математические формулы могут содержать не более 240 символов.

  Константа представляет собой готовое (не вычисляемое, постоянное) значение, которое введено в ячейку: текст, целые и дробные десятичные числа (в том числе процентные и денежные форматы), даты, время. Например, число 210, дата 09.04.2004 и текст «Осужден по статье» являются константами, а выражение =(35+590)*23 и =0,13*CУMM(C9:D29) являются формулами, в которые входят числовые константы 35, 590, 23 и 0,13. Сами формулы не являются константами.

  Операторы определяют действия в формуле (сложение, умножение, деление, сравнение, а также объединение). Арифметические операторы +, —, *, / вводят знаки плюс, минус, умножить, разделить, например: =(B4+25*C4)/(D5—F5). Оператор процентов % записывается вплотную к числу или адресу ячейки и соответствует делению на 100. формула =(1+5%) вычислит значение 105%, а формула =С5*(1+5%) о эквивалентна =С5*1,05 и вычисляет 105% от числа в ячейке С5. Оператор возведения в степень  (крышка) находится на клавише 6_англ.

Импример, формула =3^Л4 означает З⁴ и возвратит значение 81.

  Внимание! Термин «возвращать» в отношении формул и функций табличный обработки данных означает «выводить в качестве значения ячейки». Набираются и вводятся данные, формулы, функции, и послу исчисления возвращается результат — число, текст.

  Формула =5+2*3 возвращает число 11, но формула =(5+2)*3, с теми же числами, сначала вычислит значение 7 в скобках, потом умножит на 3 и возвратит число 21.

  Операторы сравнения: = (равно), < (меньше), > (больше), <= (меньше или равно), >= (больше или равно), <> (не равно) — применяются в качестве условий и критериев логических и статистических функций.

  Операторы ссылки: двоеточие между адресами первой и последнем ячейки прямоугольного диапазона (C9:D29) и точка с запятой, которая отделяет несколько параметров, например CP3HA4(B9:B29;D9:D29;200)

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

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

  Например, ячейка с формулой =2*2 независимая, а ячейка с формулой =В9*К15 зависимая и пересчитает значение заново, если изменить данные в ячейке К15, на которую формула ссылается. Пересчет значений в зависимых ячейках при изменении

Функции в Microsoft Excel

Понятие функции, категории  функций, применение Мастера функций

Слово «функция» (от лат. functio — исполнение) в обычном понимании означает деятельность, обязанность, работу, роль. В математике функция — переменная величина, зависимая от изменения независимой переменной - аргумента. Функция может быть представлена  соответствием у —f(x), а также формулой, таблицей, графиком, диаграммой

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

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

  Имя функции можно выбрать по списку Мастера функций (см ниже).

  Аргументами функции могут быть числа, текст, логические значения (ИСТИНА или ЛОЖЬ), адреса ячеек, диапазоны адресов (массивы) и формулы с константами и другими функциями (применяются множенные функции).

  В Excel ХР/2003 при вводе функции появляется всплывающая подсказка с синтаксисом ее структуры.

  Для выбора функции из списка и определения  ее параметров привводе удобно пользоваться Мастером функций. Окно Мастера функций открывается командой Вставка, Функция или нажатием клавиш Shift+F3. Кроме того, окно выбора и ввода функций в Excel 2000 открывает кнопка fx, а в Excel ХР/2003 распахивает примыкающая кнопка Σ Другие функции (правее кнопки Σ).

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

  Вторая  панель Мастера функций появляется после того, как имя функции выбрано. Когда имя просто введено с клавиатуры или надо исправить ранее введенную функцию, то надо в строке формул нажми, в Excel 2000 кнопку = Изменить формулу, а в Excel ХР/2003 кнопку fx Вставка функции. Появляющаяся панель формул наглядно отображает функцию, строки для ввода ее аргументов, покакает возвращаемое функцией значение.

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

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

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

Статистические  функции

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

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

=МАКС - возвращает  наибольшее значение из набора  значении,

=МИН — возвращает  наименьшее значение из набора  значений.

=СРЗНАЧ —  возвращает среднее значение набора значений.

Варианты применения функции СУММ показаны на примерах: =СУММ(С5;Н12;К8), =CyMM(G5:G7;K8:L15;280),

=CyMM(G5.G7), =CyMM(G5:G7).

  Требуется соблюдать синтаксис функции: вводить сначала знак равенства, затем имя функции (русскими буквами), в скобках — аргумент функции (число, имена ячеек, диапазон ячеек). При наличии у функции нескольких аргументов их требуется располагать в скобках в правильном порядке, отделяя точкой с запятой. Например, функция СУММ(В5:С12;Е6:Е11;500) складывает значения двух диапазонов ячеек и еще число 500. Первый диапазон - это прямоугольный блок задаваемый адресом левой верхней ячейки В5 и правой нижней С2 (между адресами крайних ячеек диапазона стоит двоеточие). После точки с запятой указан второй диапазон — Е6:Е11.

  Функцию СУММ можно вставить кнопкой Автосумма (на панели Стандартная) с греческой буквой [Σ] — сигма. Если поставить табличный курсор в ячейку, где надо разместить значение «Итого», и нажать кнопку Автосумма, то программа вставит функцию суммировании ни диапазону ближайших ячеек с числами, выделит рамкой диапазон Если диапазон ячеек, который выделен программой, не устраивает, можно исправить границы диапазона с клавиатуры или изменим мерцающую рамку диапазона указателем мыши. В последних версии Excel кнопку [Σ] можно распахнуть и выбрать варианты различных функций.

Статистическая  функция =СЧЕТ(диапазон) подсчитывает, см ни ко чисел в диапазоне —  количество чисел (числовых ячеек) в интервалах и массивах. Считает только ячейки с числами без пустых и текстовых данных.

  Статистическая  функция =СЧЕТЗ (диапазон) ведет счет заполненных ячеек в диапазоне с числами или текстом, но не пустых (в названии функции буква 3, но не цифра 3).

  Статистическая  функция =СЧЕТЕСЛИ(диапазон;критерий) считает, сколько раз в диапазоне ячеек встречается значение, удовлетворяющее критерию.

Таблица 15.4

Данные о женщинах-заключенных

  По  таблице 15.4 функция = СЧЕТ(В1:11;B3:I3) в диапазоне ячеек с Числовыми данными о наличии детей вычислит, сколько женщин-за-ключенных имеют детей и поэтому подлежат амнистии. Функция =СЧЕТ(A1:I1;F3:I3) подсчитает общее число женщин, а функция СЧЕТЕСЛИ(В1:13; «>1») — сколько женщин имеют более одного ребенка (критерий — неравенство в кавычках).

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

Логические функции проверяют, выполняются ли какие-нибудь условия: если выполняются, это расценивается как ИСТИНА, если нет ЛОЖЬ. В зависимости от того, что дает проверка условий, истину или лож, логические функции отображают какое-нибудь текстовое или числовое значение или выполняют вычисление по формуле.

  Простые логические функции И(; ;), ИЛИ(; ;), НЕ() после проверки условий отображают одно из двух значений: ИСТИНА или ЛОЖЬ. Функция И требует выполнения всех условий, функция ИЛИ - выполнения хотя бы одного условия из перечисленных, а НЕ требует невыполнения условия.

  Логическая  функция =И( ; ;) может содержать несколько проверяемых условий (логических значений через точку с запятой); возврата значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. Пример: =H(G15>0;D4=13%*b5).

  Логическая  функция = ИЛИ( ; ; ) может содержать несколько условий (через точку с запятой); возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ. Пример: =ИЛИ(G15>0;D4=13%*b5).

  Функция =НЕ( ) проверяет условие и меняет значение ИСТИНА на ЛОЖЬ, а ЛОЖЬ на ИСТИНА, то есть логическое значение своп о аргумента меняет на противоположное; используется в случаях, когда необходимо быть уверенным в том, что указанное условие не выполняется. Пример: =HE(G15>0).

  Логическая  функция ЕСЛИ( ; ; ) проверяет условие (гипотезу) и отображает одно из двух значений. Функция имеет три аргумента  (or деленных точкой с запятой), но условием является только первым аргумент; синтаксис функции:

=ЕСЛИ (условие;  значение если истина; значение  если ложь).

Или короче:

  =ЕСЛИ(условие;  если да; если нет).

  Если  условие выполняется (истина), то отображается значение если истина (записанное после точки с запятой на второй позиции), если условие не выполняется (ложь), то отображается после второй точки с запятой значение если ложь.

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

    Логическое выражение задается операторами «больше», «меньше», «равно», «больше или равно», «меньше или равно»; по результатам проверки выражения выдается логическое значение ИСТИНА или ЛОЖЬ. Если выражение дает логическое значение ИСТИНА, то функции ЕСЛИ возвращает значение своего второго аргумента («если да»). Возвращает, то есть выдает за значение ячейки. Если условие дает ЛОЖЬ, то функция возвращает значение своего третьего аргумента («если нет»)

  Функция =ЕСЛИ(С12>3;Я20;45) означает: если в ячейке С12 значение больше чем 3, то вывести (возвратить) значение ячейки R20, а если нет, то возвратить число 45. Функция =ЕСЛИ(F5>0;20%*F5;10%*F5) возвращает 20% от введенного числа F5 или 10% от числа в зависимости от знака из ячейки F5.

  Функция с текстом в кавычках =ЕСЛИ(Р8>0;«Сектор Приз»; « Вы банкрот») возвращает в свою ячейку текст в зависимости от знака числа из ячейки F8.

  В логической функции логическое выражение  само может состоять   

из вложенных  логических функций, что увеличивает сложность условий:

=ЕСЛИ(ИЛИ(А5>=20;А5<3);С5-В5;0), =ЕСЛИ(И(В4>0;С4>0);А4*В4;А4/С4).

Функции дат и времени

Программа Excel для Windows использует систему дam 1900, хранит даты как ряд последовательных номеров: по умолчанию 1 января 1900 г. Иметь номер 1, а 16 февраля 2001-го — номер 36938, так как интервал в днях между этими датами составляет 36938. Конец отсчета — 31 декабря 9999 г. Годы с 00 до 29 воспринимаются как 2000 и 2029, годы 1900 -1929, 2030 год и далее надо вводить полностью. В Excel для Mmintosh система дат 1904— отсчет дат ведется с 1904 г.

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

  Функция =ДАТА(год; месяц; день) возвращает День. Месяц. Год, то есть аргументы вводятся в обратной последовательности, а выводится Mpoi[ейский европейский (русский) стандарт даты. Например, функция »ДАТА(2006;12;1) возвратит 01.12.06.

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

  Рассмотрим  случай, когда в ячейку С5 введена  функция =ТДАТА() и формат ячейки настроен так, чтобы показывать дату и время, например 1.12.06 11:15. В других ячейках можно вывести избирательные сведения с помощью функций, ссылающихся на ячейку С5.

  Функция =ДЕНЬНЕД(С5) выведет день недели 6 (шестой день не- Пи - суббота).

    Функция =ГОД(С5) выведет 2006.

    Функция =МЕСЯЦ(С5) выведет 12 (декабрь).

    Функция =ЧАС(С5) выведет 11 (часов).

    Функция =МИНУТЫ(С5) выведет 15 (минут).

    Формат значений времени и дат устанавливает команда Формат, Ячейки, Число, Дата (или Время) по образцам или обозначениям формата:

  ДД.ММ.ГГ — означает без пробела День.Месяц.Год, разделенные точкой, без точки в конце, например 31.12.98 и 13.08.07;

ЧЧ:ММ:СС или  ЧЧ:ММ (без пробела) — означает, что  часы, минуты и секунды будут представлены в виде 11:35:22 или 23:40.

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

Число дней между  датами вычисляется по формуле разности, с записью каждой даты в кавычках по шаблону ="ДД.ММ.ГТ" "ДД-ММ.ГГ". Например, формула ="30.06.2012"-"01.09.2007" возвратит число 1764 — дни за 5 лет обучения в вузе с учетом високосных лет. Еще пример: срок завершения работ по контракту 31.12.2006, фактп ческий срок завершения работ 14.11.2007. Опоздание в днях вычисляет формула -"14.11.2007"-"31.12.2006".

  Формулы вычислений с датами удобно записывать с адресами ячеек, в которых введены  даты. Например, формула =К4-С4 из адреса ячейки К4 с поздней датой вычитает адрес ячейки С4, со держащей раннюю дату. Кавычки к адресу ячейки при такой записи не нужны. Если в С40 ввести прибытие по расписанию 10:14, и D40 фактическое прибытие 17:08, то время опоздания составит =C40-D40.

  Чтобы узнать дату через определенное число дней, дни можно прибавлять к дате и вычитать. Например, прибавить 60 дней ="1.01.2006"+60 или вычесть 100 дней =СЕГОДНЯ()-100.

  При вычитании и сложении можно применять  функцию =ДАТА(год;месяц;день), у которой  обратный порядок аргументом разделенных точкой с запятой. Например:

=ДАТА(1945;05;09)-ДАТА(1941;06;22).

  Аргументы функции можно представить адресами ячеек, в которых записаны числа, например: =ДАТА(С40;040;Е40).

      Текстовые функции

Функция =ТЕКСТ(значение;формат) форматирует  число (или значение из формулы, из другой ячейки) и преобразует его в текст. Формат в кавычках описывается так, как в окне команды формат, ячейка, число. Например, формат "0%" представит число 0,413 в виде текста 41% формат "0,0%" - в виде 41,3%; число 32, 365 в формате "0руб." представится текстом 32,00 руб., а в формате "0,00руб." — текстом 32,37руб.; Функция =ТЕКСТ(С5;"ДДДД") прочитает дату, записанную в ячейке С5, например 29.03.07, и возвратит день недели слоном четверг; в формате «ДДД» (три буквы) будет выведена сокращенная запись дня недели - Чт, а в формате "ДД" (две буквы) - просто само число 29.

  Функция объединения. Функция =СЦЕПИТЬ(Текст1;Текст2;...)

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

Например, поданным в ячейках табл. 15.5 функция =СЦЕПИТЬ(А2; В2;"осужден на";Е2;"лет по статье";С2;""; D2) Выведет объединенный текст: «Свидригайлов Альберт осужден на 9 лет по статье 35 УК РФ». В функции через точку с запятой указаны Адреса ячеек и в кавычках — сцепляющий текст вместе с пробелами.

Таблица 15.5

Данные об осужденных

  Если  в ячейку D3 ввести время 10:00, а в ячейку ЕЗ — 20:00, то функция

  =СЦЕПИТЬ(«Посетите  нашу адвокатскую контору по  рабочим Дням с ";"TEKCT(D3;"44:MM");" до ";"ТЕКСТ(ЕЗ;"ЧЧ:ММ")") выведет объединенный текст «Посетите нашу адвокатскую контору но рабочим дням с 10:00 до 20:00». Функция ТЕКСТ отображает число в формате времени «часы:минуты», если же функцию не применить, то из ячейки ЕЗ вместо 20:00 будет отображено число 0,8833.

  Если на часах компьютера 23 сентября 2007 г., то функция

  =ЦЕПИТЬ(«Балансовый отчет от"; ТЕКСТ(СЕГОДНЯ(),"ДД ММММ ГГГГ")) но тратит текст Балансовый отчет от 23 сентября 2007, а функция –

  СЦЕПИТЬ("Сводка преступлений, г. Элиста"; 11ХСТ(СЕГОДНЯ(),"ДД.ММ.ГГ")) но тратит текст Сводка преступлений, г.Элиста, 30.09.07.