Автоматизация расчётов в электронных таблицах Excel
Источник: Журнал «Бухгалтер и Компьютер» №4 2004г.
Как и любая другая электронная таблица, MS Excel предназначен прежде всего для автоматизации расчётов, которые обычно производят на листке бумаги или с помощью калькулятора. На практике в профессиональной деятельности встречаются довольно сложные расчёты. Именно поэтому мы подробнее поговорим о том, как Excel помогает нам автоматизировать их выполнение.
Для обозначения какого-либо действия, например сложения, вычитания и т. п., в формулах применяются операторы.
Все операторы делятся на несколько групп (см. таблицу).
Объединение последовательностей символов в одну последовательность символов
Ссылка на все ячейки между границами диапазона включительно
Арифметические операторы используются для обозначения основных математических операций над числами. Результатом выполнения арифметической операции всегда является число. Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом выполнения операции сравнения является логическое значение ИСТИНА или ЛОЖЬ.
Ссылки представляют собой включаемые в формулу адреса ячеек или диапазоны ячеек. Ссылки на ячейки задаются обычным образом, т. е. в виде A1, B1, C1. Например, для того, чтобы получить в ячейке A3 сумму ячеек A1 и A2, в неё достаточно ввести формулу =A1+A2 (рис. 1).
При вводе формулы ссылки на ячейки могут символ за символом непосредственно набираться с клавиатуры на латинице, но чаще намного проще указывать их с помощью мыши. Например, чтобы ввести формулу =A1+B2, нужно выполнить следующие действия:
Аналогично можно указывать ссылки на диапазоны ячеек, выделяя их при вводе формулы с помощью мыши или клавиатуры.
Диапазон ячеек представляет собой некоторую прямоугольную область рабочего листа и однозначно определяется адресами ячеек, расположенными в противоположных углах диапазона. Разделённые символом “:” (двоеточие), эти две координаты составляют адрес диапазона. Например, чтобы получить сумму значений ячеек диапазона C3:D7, используйте формулу =СУММ(C3:D7).
В частном случае, когда диапазон состоит целиком из нескольких столбцов, например от В до D, его адрес записывается в виде В:D. Аналогично если диапазон целиком состоит из строк с 6-й по 15-ю, то он имеет адрес 6:15. Кроме того, при записи формул можно использовать объединение нескольких диапазонов или ячеек, разделяя их символом “;” (точка с запятой), например C3:D7; E5;F3:G7.
Редактирование уже введённой формулы можно сделать несколькими способами:
• двойным щелчком левой кнопки мыши на ячейке, чтобы корректировать формулу непосредственно в этой ячейке;
• выбрать ячейку, переместив курсор в строку формул, щёлкнуть левой кнопки мыши.
В результате программа перейдёт в режим редактирования, в процессе которого можно вносить в формулу необходимые изменения.
Как и при вводе формул, ссылки на ячейки можно править или непосредственно с клавиатуры, или путём наведения мыши на нужный диапазон данных.
При заполнении таблицы принято задавать расчётные формулы только для “первой” (начальной) строки или “первого” (начального) столбца, а остальную часть таблицы заполнять формулами с помощью режимов копирования или заполнения. Прекрасный результат даёт применение автокопирования формул с помощью автозаполнителя.
Напомним, как правильно осуществить режим копирования. Здесь могут быть различные варианты (и проблемы тоже).
Необходимо иметь в виду, что при копировании происходит транспонирование адресов. При копировании формулы из одной ячейки в другую Excel по-разному реагирует на формулы с относительными и абсолютными ссылками. Для относительных Excel по умолчанию производит транспонирование адресов, в зависимости от позиции ячейки, в которую копируется формула.
1) выбираем ячейку С2, из которой нужно скопировать формулу;
2) нажимаем кнопку “Копировать” на панели инструментов, или клавиши Ctrl+C, или выбираем в меню “Правка ® Копировать”;
3) выбираем ячейку С3, в которую будем копировать формулу;
4) нажимаем кнопку “Вставить” на панели инструментов, или клавиши Ctrl+V, или через меню “Правка ® Вставить” с нажатием Enter.
Иногда возникает необходимость скопировать только числовое значение ячейки (диапазона ячеек). Для этого нужно проделать следующее:
1) выбрать ячейку (диапазон), из которой нужно скопировать данные;
2) нажать кнопку “Копировать” на панели инструментов или выбрать в меню “Правка ® Копировать”;
3) выбрать ячейку (левую верхнюю нового диапазона), в которую будут копироваться данные;
4) выбрать в меню “Правка ® Специальная вставка” и нажать Enter.
При копировании формул компьютер сразу производит расчёты по ним, выдавая, таким образом, быстрый и наглядный результат.
Функции в Excel в значительной степени облегчают проведение расчётов и взаимодействие с электронными таблицами. Наиболее часто применяется функция суммирования значений ячеек. Напомним, что она имеет название СУММ, а в качестве аргументов служат диапазоны суммируемых чисел.
В таблице часто требуется вычислить итоговую сумму по столбцу или строке. Для этого Excel предлагает функцию автоматической суммы, выполняемой нажатием кнопки (“Автосумма”) на панели инструментов.
Если мы введём ряд чисел, установим курсор под ними и выполним двойной щелчок мышью по значку автосуммирования, то произойдёт сложение чисел (рис. 3).
В последней версии программы справа от значка автосуммирования имеется кнопка списка, позволяющая произвести вместо суммирования ряд часто используемых операций (рис. 4).
Действительно, чтобы произвести в Excel операцию суммирования двух или более ячеек для получения временного результата, необходимо выполнить как минимум две лишние операции — найти место в текущей таблице, где будет расположена итоговая сумма, и активизировать операцию автосуммирования. И лишь после этого можно выбрать те ячейки, значения которых необходимо просуммировать.
Именно поэтому начиная с версии Excel 7.0 в электронную таблицу была встроена функция автовычисления. Теперь в электронных таблицах Excel имеется возможность быстрого выполнения некоторых математических операций в автоматическом режиме.
Для примера рассчитаем с помощью этой функции сумму значений для диапазона В3:В9. Выделите числа в диапазоне ячеек В3:В9. Обратите внимание, что в строке состояния, расположенной внизу рабочего окна, появилась надпись Сумма=X, где X — число, равное сумме выделенных чисел диапазона (рис. 5).
Как видим, результаты обычного расчёта по формуле в ячейке В10 и автовычисления совпадают.
Помимо функции суммирования Excel позволяет обрабатывать данные с помощью других функций. Любую из них можно ввести непосредственно в строке формул с помощью клавиатуры, однако для упрощения ввода и снижения количества ошибок в Excel имеется “Мастер функций” (рис. 6).
Вызвать окно диалога “Мастера” можно с помощью команды “Вставка ® Функция”, комбинацией клавиш Shift+F3 или кнопкой на стандартной панели инструментов.
Первый диалог “Мастера функций” организован по тематическому принципу. Выбрав категорию, в нижнем окне мы увидим список имён функций, содержащихся в данной группе. Например, функцию СУММ () вы сможете отыскать в группе “Математические”, а в группе “Дата и время” находятся функции ЧИСЛО(), МЕСЯЦ(), ГОД(), СЕГОДНЯ().
Кроме того, для ускорения выбора функций Excel “помнит” имена 10 недавно использованных функций в соответствующей группе. Обратите внимание, что в нижней части окна отображается краткая справка о назначении функции и её аргументах. Если вы нажмёте кнопку “Справка” в нижней части диалогового окна, то Excel откроет соответствующий раздел справочной системы.
Предположим, что необходимо произвести расчёт амортизации имущества. В этом случае следует в зоне поиска функции ввести слово “амортизация”. Программа подберёт все функции по амортизации (рис. 7).
После заполнения соответствующих полей функции будет произведён расчёт амортизации имущества.
Нередко нужно произвести сложение чисел, удовлетворяющих какому-либо условию. В этом случае следует использовать функцию СУММЕСЛИ. Рассмотрим конкретный пример. Допустим необходимо подсчитать сумму комиссионных, если стоимость имущества превышает 75 000 руб. Для этого используем данные таблицы зависимости комиссионных от стоимости имущества (рис. 8).
Наши действия в этом случае таковы. Устанавливаем курсор в ячейку В6, кнопкой запускаем “Мастера функций”, в категории “Математические” выбираем функцию СУММЕСЛИ, задаём параметры, как на рис. 9.
Обратите внимание, что в качестве диапазона для проверки условия мы выбираем интервал ячеек А2:А6 (стоимость имущества), а в качестве диапазона суммирования — В2:В6 (комиссионные), при этом условие имеет вид (>75000). Результат нашего расчёта составит 27 000 руб.
Для удобства работы в Excel имеется возможность присваивания имён отдельным ячейкам или диапазонам, которые затем можно использовать в формулах наравне с обычными адресами. Чтобы быстро присвоить имя ячейке, выделите её, установите указатель на поле имени в левой части строки формул, нажмите кнопку мыши и введите название.
Чтобы вставить имя в формулу, можно воспользоваться командой “Вставка ® Имя ® Вставить”, выбрав нужное имя в списке имён.
Полезно помнить, что имена в Excel используются как абсолютные ссылки, т. е. являются разновидностью абсолютной адресации, что удобно при копировании формул.
Имена в Excel можно определять не только для отдельных ячеек, но и для диапазонов (в том числе несмежных). Для присвоения имени достаточно выделить диапазон, а затем ввести название в поле имени. Кроме того, для задания имён диапазонов, содержащих заголовки, удобно использовать специальную команду “Создать” в меню “Вставка ® Имя”.
Чтобы удалить имя, выберите его в списке и нажмите кнопку “Удалить”.
При создании формулы, ссылающейся на данные из листа, можно использовать заголовки строк и столбцов для указания данных. Например, если присвоить значениям колонки имя названия колонки (рис. 10),
то для подсчёта общей суммы для столбца “Комиссионные” используется формула =СУММ(Комиссионные) (рис. 11).
:: Дополнительные возможности Excel — шаблоны
Для создания документа на основе шаблона выполните команду “Создать” из меню “Файл”, затем выберите необходимый шаблон на вкладке “Решения” (рис. 12).
Шаблоны копируются на диск при обычной установке Excel. Если шаблоны не отображаются в окне диалога “Создание документа”, запустите программу установки Excel и установите шаблоны. Чтобы получить подробные сведения об установке шаблонов, посмотрите раздел “Установка компонентов Microsoft Office” в справке Excel.
Например, для создания ряда финансовых документов выберите шаблон “Финансовые шаблоны” (рис. 13).
Эта группа шаблонов содержит формы следующих документов:
• командировочное удостоверение;
• авансовый отчёт;
• платёжное поручение;
• счёт-фактура;
• накладная;
• доверенность;
• приходный и расходный ордера;
• платёжки за телефон и электроэнергию.
Выберите нужный бланк для заполнения, а затем введите в него все необходимые реквизиты и распечатайте его. При желании документ можно сохранить как обычную Excel-таблицу.
Excel позволяет пользователю самому создавать шаблоны документов, а также редактировать уже имеющиеся.
При создании документа на основе шаблона программа Excel автоматически создаёт его рабочую копию с расширением *.xls, добавляя в конец имени документа порядковый номер. Шаблон-оригинал при этом остаётся нетронутым и может быть впоследствии использован повторно.
Для автоматического ввода даты можно воспользоваться следующим способом: в ячейку даты введите функцию СЕГОДНЯ, после этого в ней отобразится текущий день месяца, месяц и год соответственно.
Разумеется, все рассмотренные действия над шаблонами вы можете использовать и при работе с обычными книгами Excel.
Какой вид примет содержащая абсолютную и относительную ссылку формула, записанная в ячейке C1, после ее копирования в ячейку С2
Задачей оператора ПРИЗВЕД является умножение отдельных чисел или тех, которые расположены в ячейках листа. Аргументами этой функции являются ссылки на ячейки, в которых содержатся данные для перемножения. Всего может быть использовано до 255 таких ссылок. Результат умножения выводится в отдельную ячейку. Синтаксис данного оператора выглядит так:
Какой вид примет содержащая абсолютную и относительную ссылку формула, записанная в ячейке C1, после ее копирования в ячейку С2
3)15
4. Какой командой нужно воспользоваться, чтобы вставить в столбец числа от 1 до 10500?1)команда »Заполнить» в меню »Правка»
5. Какое форматирование применимо к ячейкам в Excel 4)все варианты верны
6. Какой оператор не входит в группу арифметических операторов?3)&
7. Что из перечисленного не является характеристикой ячейки?3)размер
8. Какое значение может принимать ячейка4)все перечисленные
9. Что может являться аргументом функции? 4)все варианты верны
10. Указание адреса ячейки в формуле называется1)ссылкой
11. Программа Excel используется для2)создания электронных таблиц
12. С какого символа начинается формула в Excel1)=
13. На основе чего строится любая диаграмма? 4)данных таблицы
14. В каком варианте правильно указана последовательность выполнения операторов в формуле?3)операторы ссылок затем операторы сравнения
15. Минимальной составляющей таблицы является1)ячейка
16. Для чего используется функция СУММ?2)для получения суммы указанных чисел
17. Сколько существует видов адресации ячеек в Excel2)два
18. Что делает Excel, если в составленной формуле содержится ошибка?2)выводит сообщение о типе ошибки как значение ячейки
19. Для чего используется окно команды » Форма. «1)для заполнения записей таблицы
21. Упорядочивание значений диапазона ячеек в определенной последовательности называют 4)Сортировка
22. Адресация ячеек в электронных таблицах, при которой сохраняется ссылка на конкретную ячейку или область, называется3)абсолютной
2)3
3)1
4)3
26. Выделен диапазон ячеек A1:D3 электронной таблицы MS EXCEL. Диапазон содержит 4)12 ячеек
27. Диапазон критериев используется в MS Excel при1)применении расширенного фильтра
2). 1, 2, 4
29. Для решения уравнения с одним неизвестным в MS Escel можно использовать опцию3)подбор параметра
??1. Если в диалоге «Параметрах страницы» установить масштаб страницы «не более чем на 1 стр. в ширину и 1 стр. в высоту» то при печати, если лист будет больше этого размера, .
3. Открыть Microsoft Word:3)Пуск — Программы — Microsoft Word
4. В текстовом редакторе основными параметрами при задании шрифта являются1)гарнитура, размер, начертание
5. В процессе форматирования текста изменяется2)параметры абзаца
6. В текстовом редакторе основными параметрами при задании параметров абзаца являются2)отступ, интервал
7. В текстовом редакторе необходимым условием выполнения операции Копирование является4)выделение фрагмента текста
8. В текстовом редакторе при задании параметров страницы устанавливаются3)поля, ориентация
9. В процессе редактирования текста изменяется3)последовательность символов, слов, абзацев
10. Минимальным объектом, используемым в текстовом редакторе, является 4)знакоместо (символ)
11. В текстовом редакторе выполнение операции Копирование становится возможным после4)выделения фрагмента текста
12. Для включения режима настройки меню в текстовом редакторе MS Word необходимо выполнить команду4)Сервис-Настройка
4)2, 5
2)2 — б
1)2, 4
4)2, 4, 5
17. В группу элементов управления Панель инструментов «Рецензирование» входят элементы для1)создания, просмотра и удаления примечаний
18. Циклическое переключение между режимами вставки и замены при вводе символов с клавиатуры осуществляется нажатием клавиши 4)Insert
21. Сохранить документ – это:2)записать документ из оперативной памяти на жесткий или гибкий магнитный диск
22. Документы обычно сохраняют:2)В папке » Мои документы »
23. Выберите режим просмотра документа, который служит именно для набора текста:1)обычный
24. Выберите правильный алгоритм печати документа:3)Сделать предварительный просмотр, Файл — Печать — Выбрать принтер — Указать количество копий — Ok
25. Какой список называется «маркированным»:2). каждая строка начинается с маркера — определенного символа
26. Какая панель инструментов предназначена для работы с таблицами:2)Таблицы и границы
4). 2, 5
studopedia.org — Студопедия.Орг — 2014-2022 год. Студопедия не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования (0.027 с) .
Использование функций в Excel. — КиберПедия
Ссылки представляют собой включаемые в формулу адреса ячеек или диапазоны ячеек. Ссылки на ячейки задаются обычным образом, т. е. в виде A1, B1, C1. Например, для того, чтобы получить в ячейке A3 сумму ячеек A1 и A2, в неё достаточно ввести формулу =A1+A2 (рис. 1).
Использование функций в Excel.
Цель работы: Научиться выполнять вычисления по формулам в таблице,использоватьМастер Функций для построения формул в Excel, познакомиться с основными функциями.
Задание 1.1. Укажите назначение кнопок команды Автосумма группы команд Редактирование.Заполните таблицу.
Задание 1.2. Укажите назначение кнопок команды Заполнить группы команд Редактирование.Заполните таблицу.
Задание 1.3. Перечислить способы вызова Мастера функций (Вставить функцию).
Задание 1.4. Заполните таблицу с информацией об основных функцияхExcelсогласнообразцу.
ДЛСТР Возвращает количество знаков ДЛСТР(текст) =ДЛСТР(45)
Задание 1.5. Заполните таблицу,если х записан в ячейку В1и равен3,аy–в ячейке В2и равенвашему номеру в журнале.
| Математическая формула | Формула в Excel | Результат |
Задание 1.6. Заполните таблицу,установив стрелкой соответствие между кодом ошибки идействием, вызвавшим эту ошибку.
используется недопустимый тип оператора или аргумента
в формуле задано пересечение областей, которые на самом
используемое в формуле имя некорректно и Excel не может
неопределенные данные. Чаще всего эта ошибка возникает
результатом выполнения формулы, использующей значения
даты и времени стало отрицательное число или результат
результатом вычисления является число, которое слишком
использовать в MS Excel. Диапазон отображаемых чисел
Задание 1.7. Укажите название и назначение кнопок группы команд Зависимости формул.Заполните таблицу.
| Вид кнопки | Название команды | Описание действия команды |
Практическое задание для самостоятельной работы
Создайте таблицу, содержащую информацию о планетах солнечной системы.
§ Подсчитать общее количество спутников всех планет солнечной системы:
— на вкладке Главная в группе команд Редактирование нажать кнопку
— мышкой выделить диапазон ячеек O6:O14 и нажать Enter.
§ Подсчитать минимальное расстояние от планеты до Солнца в км:
— на вкладке Главная в группе команд Редактирование открыть список кнопки Автосумма;
— мышкой выделить диапазон ячеек К6:К14 и нажать Enter.
§ Аналогично подсчитать максимальное расстояние от планеты до Солнца в км.
1. Какой оператор не входит в группу арифметических операторов?
3. Какой адрес будет иметь ячейка В12, если поменять вид адресации с А1 на R1C1?
8. Сколько существует видов адресации ячеек в Excel?
10. Какой вид примет формула при копировании еѐ на одну ячейку вниз?
11. Сначала скопировали значения выделенных ячеек в ячейку D1. Затем в ячейку A5.
В чем может быть причина различия результатов копирования? (выберите из предложенного)
§ В ячейках A6 и B6 содержались данные; выделенные ячейки скопировались в 5-ю и 7-ю строки
§ После копирования в ячейку A5 в таблицу чисел были вставлены дополнительные ячейки
12. Какой разделитель используется в датах в Excel 2010?
§ Разделитель определяется установками операционной системы
13. Какую функцию выполняет клавиша F4 при вводе формулы?
14. Какое из нижеследующих утверждений относительно функций является ложным?
§ Функции — это встроенные формулы, выполняющие вычисления по заданным величинам, называемым аргументами, в указанном порядке
§ В качестве аргумента функции нельзя использовать формулу
§ Существуют относительные, абсолютные и смешанные ссылки
§ В качестве аргументов функции используются числа, текст, логическое значение,массивы, значение ошибки, ссылки, другие функции(вложенные) или формулы
§ Использовать значок [^] (верхний регистр клавиши 6 на клавиатуре) при латинской раскладке
§ Скопировать значение степени в буфер обмена, после чего вставить его как степень
§ Использовать значок [~] слева от ряда чисел на клавиатуре при латинской раскладке
§ После введения числа нажать стрелку вверх и ввести значение степени
16. Какое из нижеследующих утверждений относительно формул не является истинным?
§ Существует четыре вида операторов формул — арифметические, текстовые, сравнения и ссылок
§ Формула есть выражение, по которому выполняется вычисление
§ В формулу могут входить функции, ссылки, операторы и константы
17. В перечне функций укажите функции, относящиеся к категории статистические:
Цель работы: Научиться выполнять вычисления по формулам в таблице,использоватьМастер Функций для построения формул в Excel, познакомиться с основными функциями.
Задание 1.1. Укажите назначение кнопок команды Автосумма группы команд Редактирование.Заполните таблицу.
Задание 1.2. Укажите назначение кнопок команды Заполнить группы команд Редактирование.Заполните таблицу.
Задание 1.3. Перечислить способы вызова Мастера функций (Вставить функцию).
Задание 1.4. Заполните таблицу с информацией об основных функцияхExcelсогласнообразцу.
ДЛСТР Возвращает количество знаков ДЛСТР(текст) =ДЛСТР(45)
Задание 1.5. Заполните таблицу,если х записан в ячейку В1и равен3,аy–в ячейке В2и равенвашему номеру в журнале.
| Математическая формула | Формула в Excel | Результат |
Задание 1.6. Заполните таблицу,установив стрелкой соответствие между кодом ошибки идействием, вызвавшим эту ошибку.
используется недопустимый тип оператора или аргумента
в формуле задано пересечение областей, которые на самом
используемое в формуле имя некорректно и Excel не может
неопределенные данные. Чаще всего эта ошибка возникает
результатом выполнения формулы, использующей значения
даты и времени стало отрицательное число или результат
результатом вычисления является число, которое слишком
использовать в MS Excel. Диапазон отображаемых чисел
Задание 1.7. Укажите название и назначение кнопок группы команд Зависимости формул.Заполните таблицу.
Поперечные профили набережных и береговой полосы: На городских территориях берегоукрепление проектируют с учетом технических и экономических требований, но особое значение придают эстетическим.
Общие условия выбора системы дренажа: Система дренажа выбирается в зависимости от характера защищаемого.
Папиллярные узоры пальцев рук — маркер спортивных способностей: дерматоглифические признаки формируются на 3-5 месяце беременности, не изменяются в течение жизни.
© cyberpedia.su 2017-2022 — Не является автором материалов. Исключительное право сохранено за автором текста.
Если вы не хотите, чтобы данный материал был у нас на сайте, перейдите по ссылке: Нарушение авторских прав. Мы поможем в написании вашей работы!
Статистические функции в Excel
Необходимо иметь в виду, что при копировании происходит транспонирование адресов. При копировании формулы из одной ячейки в другую Excel по-разному реагирует на формулы с относительными и абсолютными ссылками. Для относительных Excel по умолчанию производит транспонирование адресов, в зависимости от позиции ячейки, в которую копируется формула.
| Вид кнопки | Название команды | Описание действия команды |
