Вычисления в MS Excel при решении социально-экономических задач.
Выполнение всех математических расчетов осуществляется с помощью формул. Написав некоторую формулу, Вы определяете порядок выполнения действий. Порядок выполнения операций записывается с помощью операторов. Однако для получения результата необходимо подставить в формулу конкретные значения входящих в нее величин. Значения — это числа, даты, время, текст. Операторы — это условные обозначения, определяющие действия со значениями. Умножение, деление, сложение и вычитание — операторы, которые мы используем в виде символов или условных обозначений.
Клавиша Опреатор\+ сложение\- вычитание\* умножение\ /деление\^ возведение в степень\% процент.
Знак равенства в формулах Excel.
Вне зависимости от того, создаете ли Вы в Excel самую простую или очень сложную формулу, один элемент всегда постоянен: формулы всегда начинаются со знака равенства "=".
Поиск непарной скобки в формуле.
Если в формуле имеется множество пар скобок, существует опасность пропустить одну из них. Чтобы этого не произошло, в Excel предусмотрена возможность определения пропущенной скобки: по мере перемещения текстового курсора по формуле отдельные пары скобок кратковременно выделяются жирным шрифтом.
Непарная скобка жирным шрифтом не выделяется. Если Вы все-таки попытаетесь зафиксировать эту формулу, нажав клавишу Enter, появится диалоговое окно, сообщающее об ошибке и предлагающее исправить ее. Вы не сможете работать дальше до тех пор, пока не справитесь с этой проблемой.
|
|
|
Вложенные скобки в формуле выделяются разными цветами (каждая пара скобок имеет свой цвет).
Операторы сравнения:Клавиша, Опреатор, Выражение, Результат\=,равно,=5=3,ЛОЖЬ\>,больше,=6>4,ИСТИНА\<,меньше,=8<4,ЛОЖЬ\>=,не меньше,=9>=9,ИСТИНА\<=,не больше,=4<=2,ЛОЖЬ\<>,не равно,=6<>0,ИСТИНА. Результат выполнения этих операторов в формуле может принимать значение TRUE (ИСТИНА) или FALSE (ЛОЖЬ). Это так называемые логические значения.
Текстовый оператор.
Амперсант (&) служит для объединения строковых значений. Так, результатом выполнения выражения "Информационные " & "системы" будет строковое значение "Информационные системы"
Операторы ссылок (адресные операторы).
Операторы ссылок используют в формулах для определения диапазонов, участвующих в вычислениях.
Двоеточие (:), используемое для определения диапазона, называется оператором диапазона. Например, запись AI:D4 определяет диапазон, включающий все ячейки от А1 до D4. Если Вы хотите включить все ячейки в строке или столбце в свою формулу, то укажите, например, Е:Е для всего столбца Е или 3:8 для всех ячеек в строках с 3 по 8.
|
|
|
Точка с запятой (;) — оператор объединения. Он объединяет не менее двух ссылок на несмежные ячейки или диапазоны. AI;D4 означает "ячейка А1 и ячейка D4". AI:D4;FI:H4 означает "диапазон AI:D4 и диапазон FI:H4".
Пробел - оператор пересечения, который ссылается на общие ячейки диапазонов. Например, результатом выражения B5:B15 A7:D7 будет ссылка на ячейку B7, поскольку она является общей для этих двух диапазонов.
Ссылки на ячейки и диапазоны.
В формулах Excel требуется точно определять ссылки на ячейки или диапазоны. Использование в формуле ссылки на ячейки позволяет складывать любые значения, помещенные в определенные ячейки. Единственное, в чем следует быть уверенным, — это правильность адресации.
Простой способ формирования ссылок.
Самый надежный способ получения правильной адресации ячейки или диапазона в формуле — это выделение последних с помощью мыши:
Выделите ячейку для размещения в ней формулы.
Введите знак "=" для определения формулы.
Выделите ячейку, которая должна быть представлена в формуле. Если должен быть выделен диапазон, выделите начальную ячейку, а затем протащив указатель мыши до конечной ячейки, — весь диапазон. Вокруг ячейки или диапазона появится подвижная рамка.
|
|
|
Введите оператор, например, сравнения или арифметический.
Выделите другую ячейку или диапазон. Если это последняя ссылка в формуле на ячейку или диапазон, то нажмите Enter или кнопку ввода в строке формул.
Вы увидите адреса ячеек или диапазонов в строке формул.
Если при выделении диапазона для включения в формулу Вы используете мышь, Excel автоматически вставляет оператор диапазона (:). При выделении несмежных ячеек или диапазонов также автоматически вставляется оператор объединения (;).
Именованные диапазоны.
Использование в формулах адресных операторов не всегда удобно, т.к. формула становится труднозапоминаемой и сложной для понимания. Чтобы сделать формулу более прозрачной, имеет смысл использовать имена диапазонов. Например, если столбцы или строки Вашей таблицы имеют заголовки, то для ссылки на значения таких столбцов или строк можно использовать их имена. Кроме того, существует возможность создать имя, которое представляет одинаковые ячейки или группу ячеек на нескольких листах. В этом случае, чтобы обратиться к этой группе ячеек в формуле, достаточно указать имя, присвоенное диапазону.
Рассмотрим пример: в первой колонке рабочего листа расположены наименования товаров, во второй колонке - цены. Причем, диапазону, в котором находятся цены товаров, присвоено имя цена. Теперь, чтобы узнать цену определенного товара, например стула, достаточно указать ссылку стул цена.
|
|
|
Чтобы присвоить имя диапазону, следует выполнить следующее:
Выделите диапазон ячеек, которому Вы хотите присвоить имя.
Выберите последовательно команды Name (Имя), Define(Присвоить) меню Insert (Вставка).
В строке ввода Names in workbook (Имя) укажите имя диапазона.
Кликните на кнопке OK.
Имя выделенного диапазона появляется в поле имен, как показано на картинке. Чтобы быстро выделить поименованный диапазон, достаточно выбрать его в раскрывающемся списке поля имен.
Абсолютные и относительные ссылки.
Как Вы уже знаете, каждая ячейка имеет свой адрес, который определяется соответствующими столбцом и строкой. Например, на пересечении столбца A со строкой 3 располагается ячейка A3. Такая запись называется - относительная ссылка. Если Вы переместите ячейку, формула, содержащая относительную ссылку на эту ячейку будет изменена так, чтобы обращаться уже к новой ячейке. Например, Вы ввели формулу =A3, после чего переместили ячейку A3 на одну позицию вниз. Теперь формула будет выглядеть так: =A4. Причем Excel сделает это автоматически, Вам не надо заботиться о корректировке формул после перемещения ячеек. Это удобно и в том случае, если Вы заполняете ячейки с помощью автозаполнения. Вам достаточно ввести формулу в одну ячейку, а затем протянуть за маркер автозаполнения, после чего во всех ячейках появятся скорректированные формулы.
Однако может возникнуть ситуация, когда ссылка на ячейку меняться не должна (например, несколько формул используют цену, которая постоянна для определенного вида товара). В этом случае необходимо использовать абсолютную ссылку, зафиксировав столбец и/или строку знаком $. Например, если ссылка выглядит так: =$B$1, то при автозаполнении все ячейки будут содержать формулу =$B$1.
Рассмотрим подробнее как изменяются ссылки при перемещении и копировании ячеек. Предположим, ячейка A3 содержит формулу =A1+$A$2. Здесь A1 - относительная ссылка, а $A$2 -абсолютная.
При перемещении ячеек, на которые ссылается формула (перенесем A1 в B1 и A2 в B2) относительные и абсолютные ссылки в формуле изменяются (ячейка A3 содержит теперь формулу =B1+$B$2).
Если Вы копируете ячейки, к которым обращается формула (скопируем A1 в B1 и A2 в B2, относительные и абсолютные ссылки в формуле останутся прежними (=A1+$A$2).
При перемещении ячейки, в которой находится формула (перенесем A3 в B3), относительные и абсолютные ссылки в формуле останутся прежними (=A1+$A$2).
Если Вы копируете ячейку, содержащую формулу (скопируем A3 в B3), относительные ссылки меняются, а абсолютные остаются прежними (=B1+$A$2).
При протягивании ячейки, в которой находится формула, за маркер атозаполнения (протянем A3 до C3), относительные ссылки меняются, а абсолютные остаются прежними (=C1+$A$2).
То есть реальная разница между относительными и абсолютными ссылками существует только в двух операциях: копировании ячейки с формулой и автозаполнении ячейкой, содержащей формулу.
Циклические ссылки.
Если Вы интенсивно пользуетесь ссылками в своих формулах, может возникнуть ситуация, когда формула ссылается (через другие ссылки) сама на себя. Такая последовательность ссылок называется циклической ссылкой.
При попытке ввести формулу, содержащую циклическую ссылку, появится диалоговое окно, предупреждающее о возможной ошибке. Если Вы кликните на клавише OK, на экране отобразится панель инструментов Circular Reference (Циклические ссылки), которая позволит последовательно просмотреть каждую ячейку в циклической ссылке и внести необходимые изменения в формулу. При этом на рабочем листе появятся стрелки слежения, показывающие взаимосвязь между активной и связанными ячейками. Для перехода к следующей ячейке в циклической ссылке следует дважды щелкнуть на соответствующей стрелке слежения.
Однако циклическая ссылка не всегда является ошибкой. Циклические ссылки довольно-таки часто используются в научных и инженерных расчетах. При таких вычислениях значение каждой ячейки рассчитывается на основе результатов предыдущих итераций. Чтобы разрешить такие вычисления, следует включить флажок Iteration (итерации) на вкладке Calculation (Вычисления) диалогового окна Options (Параметры) меню Tools (Сервис). По умолчанию вычисления прекращаются после выполнения 100 итераций или после того, как изменение значения не будет превышать 0,001 за один шаг. Эти параметры тоже можно изменить, воспользовавшись вкладкой Calculation (Вычисления).
Ошибки в формулах Microsoft Excel.
Если формула обработана неправильно, Microsoft Excel отображает ошибку. Причины возникновения ошибок могут быть самыми разными:
##### - результат обработки формулы не умещается в ячейке или результатом выполнения формулы, оперирующей датами и временем, является отрицательное число.
#ЗНАЧ! - используется недопустимый тип аргумента или операнда.
#ДЕЛ/0! - в формуле предпринимается попытка деления на ноль.
#ИМЯ? - Excel не может распознать имя, используемое в формуле.
#Н/Д - неопределенные данные (чаще всего встречается, если некорректно определены аргументы функции).
#ССЫЛКА! - используется недопустимая ссылка на ячейку (например, ячейки, на которые ссылается формула, были удалены).
#ЧИСЛО! - возвращаемое числовое значение слишком велико или слишком мало, чтобы его можно было представить в Microsoft Excel (диапазон отображаемых чисел от -10307 до 10307).
#ПУСТО! - задано пересечение двух областей, которые в действительности не имеют общих ячеек.
Ошибки могут возникать не только из-за неправильной обработки формулы, ошибка может содержаться в ячейке, на которую ссылается формула.
Дата добавления: 2018-05-13; просмотров: 228; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!