68461

РАБОТА С ФОРМУЛАМИ И ФУНКЦИЯМИ

Лекция

Информатика, кибернетика и программирование

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

Русский

2014-09-22

72 KB

2 чел.

инеок Александра Борисовна       Лекция № 5   “Информационные технологии в производстве текстильных изделий”                               стр. 17.

РАБОТА  С  ФОРМУЛАМИ  И  ФУНКЦИЯМИ

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

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

Формула состоит из операндов и символов операций

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

  •  Числа.
  •  Текст. В формуле текст пишется в двойных кавычках, например ”Неявка”.
  •  Логические значения. Например, ИСТИНА и ЛОЖЬ, условия типа: В5>10, А23=А5 и т.д.
  •  Ссылки или адреса ячеек. При этом в формулу для расчета подставляется значение из заданной ячейки. Могут использоваться имена (поименованные области).
  •  функции Excel.

Операции в формулах задается при помощи символов. Операции бывают разных типов.

  •  Арифметические операции:

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

  •  Операций отношения: 

 >= (меньше или равно), <, <= (больше или равно), =.

  •  Операции с текстом:  символ & - сцепление текста.

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

^  возведение в степень  приоритет 1 (высший)

*  умножение    приоритет 2

/  деление     приоритет 2

+   -  сложение и вычитание    приоритет 3

&  конкатенация (сцепление текста)   приоритет 4

= равно        приоритет 5 (низший)

>  < логическое сравнение больше и меньше   приоритет 5

>=   логическое сравнение больше или равно   приоритет 5

<= логическое сравнение меньше или равно   приоритет 5

Если значение в ячейке вычисляется по формуле, то сама формула высвечивается в строке формул, которая обычно расположена в нижней строке над самой таблицей после символа   fx .. Символ  fx . — это значок Мастера функций.

Абсолютные, относительные и смешанные ссылки

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

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

Абсолютные ссылки перед именем столбца и номером строки имеют символ $, например $А$2 или $С$23. При копировании абсолютные ссылки не изменяются

Смешанные ссылки при копировании изменяются частично. Символ $ ставится или перед именем столбца, или перед номером строки, например $А2, А$2, $С23 или С$23. Изменяется либо номер строки, либо номер столбца, причем та часть ссылки, перед которой нет символа $.

Примеры изменений различных типов ссылок при копировании:

F5 изменится имя столбца F, измениться номер строки  5.

$F5 сохранится имя столбца F,  номер строки  5 будет изменен.

F$5 будет меняться имя столбца F, номер строки  5 не меняется.

$F$F5  сохранится и имя столбца F  и  номер строки  5.

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

Изменить тип ссылки можно вручную - дописывая или удаляя знак доллара. Более простой способ – выделить ссылку в формуле или попасть на нее мышкой и несколько раз нажать клавишу F4. Каждое нажатие изменяет тип ссылки по кругу.

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

Если в формуле используются имена (поименованные ячейки), то они связывается с данными поименованного диапазона, а не с его местоположением. Можно перенести поименованный диапазон в другое место, и это не повлияет на его имя и на ссылки на это имя.

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

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

Как спрятать формулу.

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

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

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

Как использовать функции в формулах.

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

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

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

Мастер функций

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

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

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

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

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

Перед первым использованием очень полезно просмотреть справочные материалы по данной функции, вызвав их прямо из Мастера функций. Для этого в нижнем левом углу диалогового окна Мастера функции предусмотрена подсказка по просматриваемой функции – Справка по этой функции, выделенная синим цветом. Щелчок мышки по этой строке обеспечит вывод полной информации по этой функции с заметками, примечаниями и примерами использования. Кроме того, можно воспользоваться справочной системой самого EXCEL.

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

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

Редактирование формул.

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

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

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

Формулы → Зависимости формул → Влияющие ячейки.

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

Формулы → Зависимости формул → Зависимые ячейки.

После проверки убрать все стрелочки с экрана можно командой:

Формулы → Зависимости формул → Убрать все стрелки.

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

Можно проверить уже введенные формулы на всем листе на наличие ошибок. Для этого:

Формулы → Зависимости формул → Проверка наличия ошибок.

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

Формулы → Зависимости формул → Вычислить формулу.

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

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

Исправление ошибок в формулах.

Иногда при вводе формулы EXCEL выдает значение, которое начинается с символа решетки  #. Это значит, что при вычислении произошла ошибка, либо используемые значения не соответствуют нужному типу данных (например, текст вместо числа). Если ошибка найдена, то EXCEL часто предлагает свой вариант исправления ошибки, но, к сожалению, он не всегда соответствует тому, что нужно получить.

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

Ниже перечислены наиболее часто встречающиеся ошибки в формулах:

# ДЕЛ / 0 ! Деление на ноль или на содержимое пустой ячейки.

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

# Н / Д  Неопределенные данные, несоответствие значения типу данных.

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

# ССЫЛКА ! Недопустимая ссылка, например на уже удаленную ячейку.

# ЗНАЧ !  В формуле используется значение недопустимого типа.


 

А также другие работы, которые могут Вас заинтересовать

71634. Основные положения расчёта ж/б а/д мостов. Тротуары и эксплуатационные обустройства 35.78 KB
  Предельными называют такие состояния при которых конструкция сооружения или его основание перестаёт удовлетворять заданным эксплуатационным требованиям или требованиям безопасного производства работ.
71635. Постройка фундаментов мостов 140.77 KB
  Устройство плиты свайного ростверка. Для промеров расстояний через водоток вдоль оси перехода можно устроить лёгкий свайный мостик. При сооружении фундаментов в открытых котлованах размеры ограждений контуры фундаментов оси свай выносят на деревянную обноску из кольев и прибитых к ним горизонтальных досок.
71636. Методы научно-педагогического исследования в педагогике 31 KB
  Методология исследования – это учение о структуре, логической организации, методах и средствах принципов научного познания. Метод – способ достижения цели. С греческого «путь исследования», «теория», «учение». Принципы педагогического исследования (принцип, это основополагающее положение).
71637. История ранних славян VI-VIII вв 186.5 KB
  В бассейне левых притоков среднего Днепра найдены самые ранние памятники (III—IV века) киевской культуры, тесная связь которой с более поздними славянскими культурами практически не вызывает сомнений. Начало передвижения славянских народов связывают с продвижением гуннов на запад...
71639. История развития методов преподавания изобразительного искусства в древнем мире 87 KB
  Вопросы: Методы обучения рисунку в Древнем Египте. Методы обучения рисованию в Древней Греции. Методы обучения рисунку в Древнем Риме. Методы преподавания менялись в результате борьбы мнений смены художественных направлений изменения принципов обучения.
71640. История развития методов преподавания изобразительного искусства в Западной Европе 114.5 KB
  Методы обучения рисованию эпоху Возрождения. Методы обучения рисунку во второй половине XIX и в XX веке. Художники средневековья не использовали ни принципов построения изображения на плоскости которыми пользовались великие мастера Древней Греции ни достижений в области методики обучения...
71641. История развития методов преподавания изобразительного искусства в России и Белоруссии 90 KB
  Для приобретения четкого и уверенного почерка и навыков в рисунке рисовали главным образом пером, инструментом, требующим особенно твердой и уверенной руки. Однако метод обучения рисованию строился главным образом на копировании образцов.
71642. Методы психологии 128 KB
  Вторая самая многочисленная группа включает в себя эмпирические методы наблюдение и самонаблюдение эксперимент тесты; анкеты социометрия интервью и беседы приемы анализа процессов и продуктов деятельности хронометрия циклография профессиографическое описание оценка изделий...