Использование функций в формулах таблиц EXCEL.

Использование функций в формулах таблиц EXCEL.

Электронная таблица — это программа для обработки и хранения числовых данных, которая работает в режиме диалога с пользователем. Самым распространенным среди пользователей является табличный процессор Microsoft Exсel. Он позволяет создавать и форматировать таблицы любых видов и используются для решения экономических, инженерных задач, а также задач прогнозирования, планирования, статистики. Основные функции электронных таблиц Excel:

o ввод исходной информации в ячейки (форматирование таблиц);

o редактирование и корректировка исходных данных;

o отображение на экране, запись на ВЗУ или вывод на принтер расчетной таблицы данных.

Данные организованы в виде прямоугольной таблицы. Рабочее поле имеет вид сетки, столбцы пронумерованы буквами, а строки — числами. Электронная таблица имеет большие размеры. Наиболее часто используются таблицы: 256 столбцов на 16384 строк. Пересечение строки и столбца образует ячейку таблицы, имеющую свой уникальный адрес. Основное свойство электронной таблицы - изменение числового значения в ячейке приводит к мгновенному пересчету формул, содержащих имя этой ячейки. Для указания адресов ячеек в формулах используются ссылки.

В Microsoft Excel есть два вида представления ссылок

* Стиль ссылок R1C1 (здесь R — row (строка), C — column (столбец)).

Включить стиль ссылок R1C1 можно в Office 2003, включив в настройках «Сервис» –> «Параметры» –> закладка «Общие» –> галочка «Стиль ссылок R1C1»

Этот стиль наиболее полезен при вычислении позиции строки и столбца в макросах, а также при отображении относительных ссылок.

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

Каждая ячейка может содержать число, текст или формулу. Часть ячеек содержит исходную, первичную информацию, а часть — производную (результаты операций над первичными данными).

Число. Последовательность символов, в которую входят цифры, а также знаки «+», «-» (в начале последовательности) или «,» (как разделитель целой и дробной части), считается числом.

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

+ (сложение); - (вычитание); * (умножение); / (деление); ^{^}(возведение в степень).

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

Если в ячейку D4 ввести формулу =A5+D3*H12, то в ней будет отображен результат, равный произведению чисел, помещенных в ячейки D3 и Н12, сложенному с числом из ячейки А5.

Текст. Любая последовательность символов, не являющаяся ни числом, ни формулой, считается текстом.

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

Текст - это комбинация букв, цифр и пробелов. Для ввода данных необходимо выбрать ячейку и начать ввод. В ячейке отобразится курсор (точка вставки). В строке формул также отобразится содержимое ячейки, при этом включатся кнопки. Ввод данных можно осуществлять непосредственно в строке формул. Для завершения ввода необходимо нажать клавиши Enter или Tab или кнопку в виде зеленой галочки в строке формул. По умолчанию текст в ячейке выравнивается по левому краю. Отменяет ввод клавиша Esc на клавиатуре или кнопка в виде красного крестика в строке формул. В стандартных ячейках таблицы размещается 8-9 символов. При вводе более длинного текста, он визуально располагается и на соседних ячейках. Если один раз щелкнуть на текущей ячейке и снова ввести данные, то старые данные замещаются новыми. Если дважды щелкнуть по текущей ячейке или выделить ячейку и один раз щелкнуть по строке формул, то можно редактировать содержимое, не удаляя уже введенные данные.

Числа - это цифры от 0 до 9 и символы "+", "-", "/", ".", ",", "%", "$", "(", ")". Для ввода отрицательного числа надо поставить перед ним знак "-" или взять число в скобки, для ввода дроби надо ввести целую часть, пробел и дробную часть. Целая часть десятичной дроби отделяется от ее дробной части запятой. По умолчанию числа в ячейке выравниваются по правому краю. Пример. -7, (7), 0 2/5, 2 1/3. Если после ввода числа в ячейке появляется последовательность символов

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

В Microsoft Excel даты и время суток интерпретируются как числа. При вводе значений даты или времени происходит их автоматическое распознавание. Для корректного ввода данных необходимо использовать коды форматов. День, Месяц и Год вводятся цифрами или буквами. При вводе данных цифрами в качестве разделителя используется точка, пробелы не допустимы. Если месяц вводится буквенным обозначением, то в качестве разделителя используется пробел. При вводе времени в качестве разделителя используется ": ", Часы, Минуты и Секунды вводятся одной или двумя цифрами. По умолчанию значения даты и времени выравниваются в ячейке по правому краю. В строке формул дата отображается в формате дд.мм.гг, а время - в формате чч:мм:сс. Если ввод не корректен, то введенные значения интерпретируются как текст, который выравнивается в ячейке по левому краю. Если после ввода даты и (или) времени в ячейке появляется последовательность символов, то надо увеличить ширину столбца.

Пример. Можно вводить даты так: 1.1.01, 07.05.01, 01 сен 2001. Можно вводить время так: 1:10, 13:10:57, 8:1:2.

Внешнее представление времени или даты на листе зависит от формата, назначенного ячейке. Выбор типа данных активной ячейки или диапазона производится командой меню Формат / Ячейки / Вкладка_Число.

Вычисления в таблицах осуществляются при помощи формул. При вводе формулы можно использовать клавиатуру и мышь, либо только клавиатуру. Перед формулой надо ввести знак равенства"=". Адреса ячеек, которые необходимо ввести в формулу, вводятся щелчком на соответствующей ячейке.Завершение ввода - нажатие на клавишу Enter. Можно вводить данные и традиционным способом - непосредственным вводом в ячейку адресов других ячеек, используемых для вычислений. Но в этом случае часто встречаются ошибки. Наиболее типичная из них - использование в адресах ячеек строчных русских букв вместо прописных латинских. Если ячейка содержит формулу, то на рабочем листе в ней отражен результат вычисления. Если сделать ячейку активной, то в строке формул отображается сама формула. Если в соседних ячейках для расчетов используется одна и та же формула, то ее повторно не вводят, а заполняют соответствующие ячейки протаскиванием маркера заполнения.

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

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

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

(Автосуммирование),

(Мастер функций) или кнопку " " в строке формул. При вводе функции перед ее именем автоматически вводится знак "=".

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

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

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

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

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

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

При перемещении содержимого отдельной ячейки A3 в ячейку СЗ содержимое исходной ячейки, не изменяясь, перемешается в ячейку назначения, а исходная ячейка остается пустой. После перемещения содержимого трех ячеек Al, A2 и A3 (при этом ячейки взаимосвязаны) формула изменилась, чтобы отразить произошедшие в электронной таблице изменения. Диапазон исходных ячеек после выполнения операции перемещения опустел.

При перемещении содержимого ячейки A3 в ячейку СЗ, когда адрес переносимой ячейки входит в другую формулу. Это случай перемещения зависимых ячеек. При этом содержимое перемещаемой ячейки не изменяется, но изменяется содержимое зависимой ячейки В1 (хотя она не перемешается).

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

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

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

Прежде чем приступить к рассмотрению статистических функций, необходимо отметить, что при обработке больших объемов информации удобнее обращаться не к отдельным ячейкам, а к группам смежных ячеек, или диапазонам. Чтобы обратиться к диапазону ячеек, необходимо указать через двоеточие адреса начальной и конечной ячейки диапазона. Таким образом, обозначение A4:D4 адресует строку, состоящую из ячеек A4,B4,C4,D4, а обозначение В5:В8 указывает на столбец из ячеек В5,В6,В7,В8. Таким же образом можно обозначить и прямоугольную область ячеек, или блок ячеек. Например, ссылка C5:F9 указывает на блок ячеек.

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

ЕСЛИ (лог_выражение;значение_если_истина;значение_если_ложь), где

лог_выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ.

значение_если_истина — это значение, которое возвращается, если лог_выражение равно ИСТИНА. значение_если_истина может быть формулой.

значение_если_ложь — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. значение_если_ложь может быть формулой.

Когда значения аргументов значение_если_истина и значение_если_ложь вычислены, функция ЕСЛИ возвращает полученное значение.

Задание 4. Относительные и абсолютные адреса ячеек

Обозначение ячейки, составленное из номера столбца и номера строки (А5, В7 и т. д.), называется относительным адресом или просто адресом. При некоторых операциях копирования, удаления, вставки Excel автоматически изменяет этот адрес в формулах. Например, если в ячейку В2 помещена формула =А1 + В1, то после копирования этой формулы в ячейку СЗ произойдет автоматическая корректировка как номеров столбцов, так и номеров строк и в результате в ячейке СЗ появится формула =В2+С2. Автоматическое изменение адресов является удобным средством при работе с электронными таблицами, но иногда оно нежелательно, поскольку может привести к появлению ошибок. Чтобы отменить автоматическое изменение адреса данной ячейки, вы можете назначить ей абсолютный адрес. Для этого необходимо проставить перед номером столбца и (или) перед номером строки знак доллара «$». Например, в адресе $А5 не будет меняться номер столбца, в адресе В$7 - номер строки, а в адресе $D$12 - ни тот, ни другой номер.

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

Использование функций в формулах таблиц EXCEL.

o ввод исходной информации в ячейки (форматирование таблиц);

o редактирование и корректировка исходных данных;

o отображение на экране, запись на ВЗУ или вывод на принтер расчетной таблицы данных.

В Microsoft Excel есть два вида представления ссылок

* Стиль ссылок R1C1 (здесь R — row (строка), C — column (столбец)).

Что делать, если нет взаимности? А теперь спустимся с небес на землю. Приземлились? Продолжаем разговор...

Живите по правилу: МАЛО ЛИ ЧТО НА СВЕТЕ СУЩЕСТВУЕТ? Я неслучайно подчеркиваю, что место в голове ограничено, а информации вокруг много, и что ваше право...

Что делает отдел по эксплуатации и сопровождению ИС? Отвечает за сохранность данных (расписания копирования, копирование и пр.)...

Что вызывает тренды на фондовых и товарных рынках Объяснение теории грузового поезда Первые 17 лет моих рыночных исследований сводились к попыткам вычислить, когда этот...

Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте: