Конспект лекций по Excell. 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12. Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно
 Скачать 3.95 Mb.
|
|
Правка → Заполнить → Прогрессия. 37 С его помощью можно наиболее гибким образом заполнить выходной диапазон. В нем можно указать заполнение по строкам или столбцам, простое копирование или построение арифметических или геометрических аппрокси- мирующих последовательностей, и их параметры, а также шаг изменения по- следовательности и ее предельное значение. При этом границы заполняемого диапазона могут быть установлены либо его предварительным выделением, либо указанием предельного значения прогрессии, либо их комбинацией. В последнем случае граница заполняемого диапазона будут установлена в зави- симости от того, какое из условий выполнится первым. С помощью подменю команды Заполнить из основного меню Правка можно не только вызывать окно диалога Прогрессия, но и выполнять запол- нение выделенного диапазона значением единственной ячейки, расположен- ного вверх, вниз, вправо или влево относительно последней. Сочетание кла- виш Ctrl + R и Ctrl + D также вызывает заполнение выделенного диапазона значением единственной ячейки, если он расположен справа или вниз от нее, соответственно. Заполнение списков. При заполнении списков, состоящих из нескольких столбцов, может быть полезным следующий прием. После за- полнения очередной ячейки в строке, перемещение курсора к следующей справа ячейке выполняется по нажатию клавиши Tab. Нажатие клавиши Enter после заполнения последней ячейки в строке вызывает активизацию первой слева ячейки в следующей строке. Заполнение выделенного диапазона. Если до начала заполнения пря- моугольного диапазона выделить его, то можно ограничить перемещение ак- тивной ячейки его пределами. Нажатие клавиши Enter (или сочетания клавиш Shift + Enter) в конце ввода данных в очередную ячейку вызывает заполнение выделенного диапазона по столбцам, а Tab (или Shift + Tab) – по строкам. Применение фиксированного десятичного формата. При вводе боль- шого количества чисел, с одинаковым количеством знаков после десятичной точки или завершающих целые числа нулей, можно избежать многих ошибок, если использовать режим фиксированного десятичного формата при вводе. Если он установлен, то в любом числе, не содержащем десятичной точки, она будет вставляться автоматически, отделяя заданное количество знаков, или это число будет дополнено заданным количеством нулей. Если во вводимом числе десятичная точка присутствует, то никакого изменения числа не проис- ходит. Установка (или сброс) режима фиксированного десятичного формата 38 выполняется путем установки (или сброса) флажка Фиксированный деся- тичный формат при вводе в группе Параметры на вкладке Правка диало- гового окна Параметры. Там же, с помощью счетчика десятичных разрядов, устанавливается количество десятичных разрядов, автоматически отделяемых при вводе или количество добавляемых нулей – если это число отрицательное. Числа, введенные до установки (или сброса) режима фиксированного деся- тичного формата, остаются без изменения. После того, как будет закончен ввод чисел с десятичными разрядами или конечными нулями, флажок Фикси- рованный десятичный формат при вводе рекомендуется сбросить. Проверка данных при вводе. В Microsoft Excel имеется средство кон- троля ввода данных, которое можно «закрепить» за определенными ячейками таблицы. Настройка параметров контроля данных при вводе производится с помощью диалогового окна Проверка вводимых значений, которое можно активизировать по команде Данные → Проверка. На вкладке Параметры устанавливается тип данных, который будет вводиться в активную ячейку (или выделенный диапазон ячеек), а также до- пустимый диапазон изменения их значений. На вкладке Сообщение для ввода указывается подсказывающее сооб- щение, которое будет высвечиваться в тоненькой рамке всякий раз, когда ука- затель мыши будет попадать на одну из проверяемых при вводе ячеек. Вклад- ка Сообщение об ошибке служит для указания сообщения, которое будет вы- водиться в случае ввода в проверяемую ячейку недопустимого значения, а также реакции Excel на эту ошибку. 39 ЛЕКЦИЯ № 3. ВЫЧИСЛЕНИЯ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ 3.1. Формулы Вся ценность электронных таблиц заключается в возможности хранить в них не только исходные данные, но и формулы, согласно которым эти данные обрабатываются. Все формулы в Microsoft Excel начинаются со знака равен- ства (=), за которым следуют вычисляемые элементы (операнды), разделен- ные операторами. MS Excel выполняет действия слева направо с учетом приоритета опера- торов. Порядком вычисления можно управлять с помощью скобок, группируя действия, которые должны выполняться в первую очередь. Всякое составляющее формулу выражение состоит из различных комби- наций следующих компонент : 1) адресов ячеек, 2) констант, 3) функций, 4) знаков операций (операторов), 5) круглых скобок. На рис. 3.1. представлена формула, которая содержит все, перечислен- ные выше, компоненты. 1. Знак равенства – начало (признак) формулы 2. Выражение 3. Адрес ячейки 4. Числовая константа 5. Имя функции 6. Параметр функции 7. Операторы (знаки опера- ций) 8. Круглые скобки изменяют приоритет операций Рис. 3.1 – Формула Однако не всякая формула содержит все компоненты. Простейшая формула, например, может состоять только из константы (=2.5), или адреса ячейки (=A1). =(A1+25)/ СУММ (B2:C3) 1 3 4 5 6 7 2 8 40 В выражениях Microsoft Excel используются те же типы констант, что и в ячейках. Однако форма записи некоторых из них в выражениях отличается от формы записи в ячейках. Так текстовые константы в выражениях, в отличие от их записи в ячейках, заключаются в двойные кавычки ("), например "На- числено". Это сделано для того, чтобы отличить в выражении текстовую кон- станту от любого другого операнда, например, имени функции. При построении выражений в Microsoft Excel допустимы следующие че- тыре группы операторов (знаков операций): 1. Арифметические: + – сложение, - – вычитание и унарный минус, * – умножение, / – деление, % – процент, ^ – возведение в степень, 2. Текстовые: & – конкатенация (объединение строк), 3. Сравнения: = – равно, <> – не равно, > – больше, < – меньше, >= – больше или равно, <= – меньше или равно, 4. Адресные: : – диапазон, ,(;) – объединение (символ-разделитель элементов списка), – пересечение (пробел). Некоторые из этих операторов просты и очевидны, некоторые же, на- верное, требуют дополнительных пояснений. Так, оператор % уменьшает значение в 100 раз. Например, если в ячейку было введено 5%, то фактически ее содержанием будет число 0.05 – т.е. при вводе процентов, в ячейки записы- ваются коэффициенты. Ввод процентов в формулу выполняется с целью при- дания ей наглядности. Например, необходимо получить 5% от числа 200. То- гда формулу можно записать так =5%*200, или так =200*5%. Результат в обо- их случаях будет одинаков – 10. 41 Операцию процента можно применять и к ячейкам, например, A1% – сотая часть содержимого ячейки A1. Текстовый оператор конкатенации (&) используется для объединения двух строк в одну. Так, например, результатом применения оператора конка- тенации в формуле ="Иванов "&"Иван" будет фраза "Иванов Иван". Операторы отношения могут использоваться как с числовыми, так и с текстовыми данными. Их результатом может быть либо ИСТИНА, либо ЛОЖЬ. Например, =2<5 и ="B"<"A". В первом случае результат очевиден – ИСТИНА. Во втором – ЛОЖЬ, поскольку буква A по алфавиту идет раньше буквы B (или, код буквы B больше кода буквы A). В Excel можно сравнивать между собой буквы и числа. При этом буква всегда будет «больше» числа, потому что цифры в большинстве кодовых таблиц располагаются раньше букв. Так, например, значение ячейки, содержащей пробел, будет больше любого числа. Это может стать причиной визуально трудно обнаруживаемой ошибки, поскольку ячейка, содержащая пробел, выглядит так же, как и пустая ячейка, значение которой считается нулевым. Адресный оператор (:) используется в качестве разделителя при указа- нии прямоугольного диапазона ячеек. Оператор объединения (,) (или ;) слу- жит для объединения нескольких диапазонов ячеек в один, например, A1:B3,D4. Реже применяемый оператор (пробел) служит для обозначения операции пересечения диапазонов. Например, результатом операции пересечения диапазонов A2:C3 и B1:B4 будет диапазон B2:B3. Если в выражении содержится несколько операторов, то порядок его вычисления определя- ется приоритетом операторов: в начале выполня- ются операции с более высоким приоритетом, за- тем – в порядке его убывания По умолчанию в Microsoft Excel для операторов определен следующий порядок приоритетов (в порядке убывания): : – диапазон ячеек, – пересечение диапазонов ячеек, ,(;) – объединение диапазонов ячеек, - – унарный минус, 42 % – процент, ^ – возведение в степень, *, / – умножение и деление, +, - – сложение и вычитание, & – объединение двух текстовых строк в одну (конкатенация), =, <, <=, >, >=, <> – сравнения. Как уже говорилось ранее, если выражение содержит операторы с оди- наковым приоритетом, например, операторы умножения и деления, то опера- ции выполняются слева направо. Для изменения установленного по умолча- нию порядка выполнения операций используются парные круглые скобки: вначале вычисляются те части выражения, которые заключены в скобки, а за- тем – все остальные. 3.2. Ввод формул Формулы в ячейки можно вводить, как и любые другие данные: 1) либо непосредственно в ячейку, 2) либо через строку формул. Каким бы способом ни вводилась формула в ячейку, ее первым символом обязательно должен быть знак равенства (=). Знак равенства можно ввести двумя способами: 1. С клавиатуры. 2. Нажав кнопку Вставка функции в строке формул – если формула начи- нается с функции. При этом запустится Мастер функций. После ввода знака равенства с клавиатуры список Имя в левой части строки формул превращается в раскрывающийся список Функции, который состоит из 10 наиболее часто используемых функций. Далее, в формуле вводится ее содержательная часть – выражение. Суще- ствует ряд способов для ускорения процесса ввода содержательной части формулы и уменьшения вероятности совершения при этом ошибок. Самый простой (но не самый лучший) способ ввода содержательной части формулы – непосредственный ввод с клавиатуры. Например, для ввода формулы =A1+A2 43 необходимо последовательно нажать следующие клавиши: «=», «A», «1», «+», «A», «2» и Enter. Одна из наиболее распространенных ошибок при вводе формулы – это пропуск знак равенства в ее начале, например, A1+A2. В этом случае вве- денная формула обычно воспринимается как текст, и Excel не выдает ника- ких сообщений об ошибке. Ввод адресов ячеек и ключевых (зарезервированных) слов, используе- мых в формулах, можно осуществлять как заглавными, так и прописными бу- квами. Если выражение не содержит ошибок, то после завершения ввода все прописные буквы автоматически будут преобразованы в заглавные. Поэтому, такую информацию лучше вводить строчными буквами, что позволит затем быстро выявить допущенные синтаксические ошибки. Непреобразование строчных букв в заглавные будет означать, что соответствующий элемент записан неверно. Так, типичная ошибка – ввод адресов ячеек русскими бу- квами вместо латинских. Например, символы А, В и С имеются в обоих язы- ках. Если ввести адрес a4, используя латинский алфавит, он позже превратит- ся в A4, если же русский – так и останется а4. Формулы могут содержать также ссылки на ячейки других рабочих лис- тов активной или других рабочих книг – как открытых в данный момент, так и нет, как находящихся на данном компьютере, так и расположенных на других компьютерах, доступ к которым, в свою очередь, может осуществляться как средствами локальной сети, так и через Internet. Ввод таких «длинных» и сложных ссылок непосредственно с клавиатуры, естественно, связан с воз- можностью появления в них различного рода ошибок. Поэтому, другой, в большинстве случаев более надежный, способ ввода ссылки на ячейку (вместо непосредственного набора ее адреса на клавиатуре) состоит в однократном щелчке левой кнопкой мыши по ячейке, адрес которой необходимо ввести в формулу. Например, для ввода приведенной выше формулы =A1+A2 необходимо: 1. Ввести знак «=». 2. Щелкнуть по ячейке A1. 3. Ввести с клавиатуры знак «+». 4. Щелкнуть по ячейке A2. 5. Завершить ввод формулы (например, щелкнув по кнопке Ввод в строке формул). 44 При этом если нужная ячейка находится на другом рабочем листе ак- тивной или любой другой открытой в данный момент рабочей книги, то вставляемый в формулу адрес будет содержать также имя рабочего листа, а при необходимости – также и имя рабочей книги, где находится требуемая ячейка. Этот способ ввода ссылок обеспечивает более надежный и точный их ввод, поскольку пользователь непосредственно видит и выбирает именно те данные, которые он хочет включить в выражение. После однократного щелчка левой кнопкой мыши по ячейке, Excel переходит в режим Укажите, а выде- ленная ячейка обрамляется бегущей пунктирной линией, которую можно «растягивать», выделяя также и другие ячейки. При этом ссылки на ячейки в выражении отображаются тем же цветом, что и соответствующие им ячейки на рабочем листе. По умолчанию, при построении ссылок на ячейки в режиме непосредст- венного указания с помощью мыши, Excel в формулы вставляет их относи- тельные адреса. Это сделано с целью обеспечения возможности их правильной настрой- ки в случае копирования или перемещения формулы, содержащей эти адреса. Для преобразования относительного адреса в абсолютный (а затем, в смешанный, и далее, снова, в относительный) необходимо установить курсор на требуемую ссылку и несколько раз подряд нажать клавишу F4. Для синхрон- ного изменения нескольких адресов их необходимо предварительно выделить. По умолчанию после завершения ввода формулы, каким бы способом он ни был выполнен, Excel будет показывать результат вычисления по этой формуле. Сама же формула будет отображаться в строке формул – при вы- делении ячейки, в которую она была записана. Смысл введенной в предыдущем примере формулы состоит в складывании содержимого ячеек A1 и A2 и записи результата в ячейку A3. В случае изменения содержимого любой из влияющих ячеек A1 и A2, или обеих сразу, содержимое зависимой ячейки A3 будет пересчитано автоматически. Например, если в ячейку A1 вместо старого значения 1 ввести новое значение 2.5, то после завершения ввода в ячейке A3 будет отображаться результат 4.5. 45 3.3. Функции Одним из наиболее распространенных элементов в выражениях Micro- soft Excel является функция. Функции в Microsoft Excel предназначены для выполнения стандартных вычислений в рабочих книгах. Смысл понятия функция здесь тот же, что и в математике – на основе переданных ей аргу- ментов (фактических параметров) функция выполняет требуемые вычисле- ния, а результат этих вычислений возвращает в качестве своего значения. Например, функция СУММ() возвращает сумму переданных ей пара- метров, в качестве которых могут быть как просто числовые константы, так и адреса отдельных ячеек, а также ссылки на целые диапазоны ячеек, а функция ОКРУГЛ() округляет значение своего первого параметра до количества разря- дов после десятичной точки, которое указывается вторым ее параметром. Осо- бенностью функций Microsoft Excel (как впрочем, и любой другой компью- терной программы) состоит в том, что одни и тех же параметры выступают в двух ипостасях – формальной и фактической. Формальные параметры указы- ваются в справочниках при описании функции, фактические же параметры (аргументы) – это те значения, которые непосредственно передаются ей при обращении. То есть при обращении к функции осуществляется присваивание фактических параметров формальным. Вызов функции (или обращение к функции) в выражении состоит в указании имени функции, за которым в пар- ных круглых скобках задается список ее фактических параметров. Такая по- следовательность символов называется синтаксисом функции. Синтаксис обращения к функции предполагает обязательное наличие парных круглых скобок вслед за именем функции, даже если она параметров не имеет. Фактические параметры функций – это выражения, от самых простых, таких как константы, адреса ячеек или обращение к другим функциям, до самых сложных. При их указании имеется два существенных момента: 1. Если некоторая функция в качестве одного из операндов фактического па- раметра использует другую функцию, то такие функции называются вло- женными. При этом максимально допустимая глубина вложенности встроенных функций Microsoft Excel |