В чем состоит основной принцип проведения вычислений в таблицах excel

Обновлено: 01.07.2024

Основные принципы оптимизации работы в электронных таблицах

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

Интерфейс

Копирование ячеек

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

Написание копируемых формул позволяет в разы сократить время на обработку информации. Для полноценного применения мощного механизма копирования требуется освоить несколько базовых понятий электронных таблиц:

Абсолютная и относительная адресация ячеек и диапазонов.
R1C1-адресация и именованные диапазоны.
Раздельное копирование формул, форматов и значений.
Выделение связанных, несвязанных и трехмерных диапазонов.
Перетаскивание мышью как вариант копирования-вставки.

Одновременное редактирование и форматирование диапазонов ячеек

Этот принцип подразумевает возможность ввода и изменения данных и форматов сразу в нескольких диапазонах без применения копирования. К нему можно отнести такие интерфейсные возможности как:

Одновременный ввод формул в выделенные ячейки (Ctrl+Enter).
Форматирование выделенных диапазонов.
Поиск и замена данных.

Клавиатурный ввод

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

Сочетание клавиш	Контекст	Действие
F4	таблица	повотор последней операции
F4	редактор формул	изменение типа адресации вводимой ссылки с относительной на абсолютную и наоборот
F9, Shift+F9	таблица	полный пересчет, пересчет ячеек активного листа (при ручном расчете)
F9	редактор формул	расчет выделенного текста формулы с последующей вставкой результата вместо выделения
Ctrl+Enter	редактор формул	одновременный ввод формулы в выделенные ячейки
Ctrl+c, Ctrl+x, Ctrl+v	везде	копирование, вырезание, вставка
Ctrl+z, Ctrl+y	везде	отменить последнее действие, вернуть отмененное действие
Ctrl+1	таблица	вызов диалога форматирования для выделенных ячеек
Ctrl+'	таблица	вставка формулы из ячейки сверху
Ctrl+PgUp, Ctrl+PgDown	таблица	переход к предыдущему листу, переход к следующему листу
Shift+пробел, Ctrl+пробел	таблица	выделение текущей строки целиком, выделение текущего столбца целиком
Ctrl+a	таблица	выделение всех ячеек листа
Ctrl++, Ctrl+-	таблица	добавление ячеек, удаление ячеек

Вычисления

Применение сложных формул

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

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

Использование вычисляемых диапазонов

Вычисляемые диапазоны подразумевают использование функций раздела «Ссылки и массивы». К ним, в частности, относятся:

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

Для лучшего понимания концепции вычисляемых диапазонов рекомендуется также освоить работу с использованием «R1C1» адресации.

Использование массивов

При описании работы в Excel'е понятие массив (иногда употребляется термин таблица) имеет два способа реализации, которые применяются в разных случаях. Часто это приводит к некоторой путанице понятий.

Массив используется в формулах для обработки и группировки диапазонов ячеек. Такие формулы с обработкой массивов являются мощным инструментом создания сложных формул. А иногда и единственно возможным вариантом оптимизации работы. Классический пример формулы с обработкой массивов – это сумма по нескольким условиям (в версии Excel до 2007).
Массивом также называют прямоугольный неразрывно связанный диапазон ячеек, в котором используется одна формула. Такие массивы вычисляются отдельно от других веток вычислений листа Excel. Нельзя вставить или удалить строки/столбцы внутри диапазонов, использующих массив. Этот вариант использования массивов для решения экономических задач на практике применяется довольно редко.

Общим для этих двух понятий массивов, является то, что формула в ячейку вводится с применением сочетания клавиш Ctrl+Shift+Enter.

Использование контрольных значений

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

Использование VBA

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

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

Написание вычислительных алгоритмов на VBA требует изучения программирования как отдельного предмета. Навыки работы в электронных таблицах Excel практически не имеют к этому отношения. Следует также отметить, что экономические модели, реализуемые в электронных таблицах, обычно не требуют применения программирования.

Занятие №12. Организация вычислений в таблицах MS Excel .

Специальность

31.02.01 Лечебное дело

Курс – второй

Одобрена на заседании цикловой
методической комиссии _________________

Разработчик: О.А. Потемкина

преподаватель первой квалификационной категории

Выписка из рабочей программы дисциплины
«Информатика»

для специальности 31.02.01 Лечебное дело

Наименование разделов и тем

Содержание учебного материала, самостоятельная работа обучающихся

Объем часов

Уровень освоения

Пакет прикладных программ « Microsoft Office »

Формулы и функции в MS Excel

Содержание учебного материала

Внедрение современных прикладных программных средств. Ячейка: абсолютная и относительная адресация. Ввод и редактирование данных. Оформление таблиц.

Лабораторные работы

Практические занятия

12) Современные прикладные программные средства. Организация в ычислений в таблицах.

Контрольные работы

Самостоятельная работа обучающихся

Заполнение словаря терминов, подготовка ответа на контрольные вопросы, выполнение упражнений (метод разработка) .

Повторение материала по конспектам теоретического занятия, [1, стр. 175-177], выполнение практического задания [2, стр.114, задание 19.3.]

Мотивация

Программа Microsoft Excel самая удоб ная и сама мощная электронная таблица, разработанная для использования в среде Windows . Microsoft Excel –одна из самых загадочных и интересных программ в пакете MS Office . Интересна она многочис ленными средствами автоматизации работы, оформления документов и вычислительной мощью. Загадочность же ее состоит в том, что большинство пользователей применяют в своей работе лишь малую толику того, что может дать им Excel . Это тем более удивительно, что спектр возможностей Excel почти безграничен: от создания хорошо оформленных бланков до программирования сложных вычислений и моделирования различных процессов с визуализацией параметров в виде диаграмм и графиков.

Методический лист

Вид занятия – практическое занятие

Продолжительность – 90 мин.

Требования к результатам освоения темы в соответствии с ФГОС по специальности среднего профессионального образования 31.02.01 Лечебное дело:

В результате изучения темы обучающийся должен уметь:

использовать персональный компьютер в профессиональной и повседневной деятельности;

внедрять современные прикладные программные средства;

В результате изучения темы обучающийся должен знать:

методы и средства обработки информации;

базовые программные продукты и пакеты прикладных программ.

1. Учебные цели:

сформировать знания по базовым программным продуктам и пакетам прикладных программ (возможности табличного процессора MS Excel по оформлению документов);

сформировать умение использовать и внедрять современные прикладные программные средства (табличный процессор MS Excel ) в профессиональной деятельности .

2. Развивающие цели:

способствовать

развитию познавательной активности студентов через изучение работы в среде табличного процессора MS Excel .

3. Воспитательные цели :

способствовать

развитию информационной культуры;

формированию готовности к использованию информационно-коммуникационных технологий в профессиональной деятельности.

Методы обучения : объяснительно-иллюстративный, репродуктивный, мультимедийных технологий.

Место проведения занятия : аудитория колледжа.

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

Список используемых источников :

Жукова, Е. Л. Информатика: Учебное пособие. [Текст] / Жукова, Е. Л., Бурда Е. Г. – М.: Издательско-торговая корпорация «Дашков и К»; Ростов н/Д: Наука-Пресс, 2007. – 272 с.

Калабухова, Г.В. , Титов, В.М. Компьютерный практикум по информатике. Офисные технологии: учеб. пособие [Текст] – М.: ИД «ФОРУМ»: ИНФРА-М, 2008. – 336 с.: ил. – (Высшее образование).

Михеева, Е. В. Практикум по информатике: учебное пособие для студ. сред. проф. образования [Текст] / Е.В. Михеева. – 5-е изд., стер. – М.: Издательский центр «Академия», 2007. – 192 с.

Омельченко, В.П. Математика: компьютерные технологии в медицине: учебник [Текст] / В.П. Омельченко, А.А. Демидова. – Изд. 2-е, испр. – Ростов н/Д: Феникс, 2010. – 588 с.: ил. – (Среднее профессиональное образование)

Оснащение занятия :

Домашнее задание:

Повторение материала по конспектам теоретического занятия, [1, стр. 171-175] .

Форматирование в Excelприменяется для облегчения восприятия данных, что играет немаловажную роль в производительности труда.

Назначение формата

1. Выделите ячейку (диапазон ячеек).

2. Выберите команду "Формат"-"Ячейки" (Ctrl+1).

3. В появившемся окне диалога (подробно окно будет рассмотрено позже) введите нужные параметры форматирования.

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

Удаление формата

1. Выделите ячейку (диапазон ячеек).

3. Для удаления значений в ячейках надо выбрать команду "Все" подменю "Очистить".

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

Форматирование с помощью панелей инструментов

Наиболее часто используемые команды форматирования вынесены на панель инструментов "Форматирование". Чтобы применить формат с помощью кнопки панели инструментов, выделите ячейку или диапазон ячеек и затем нажмите кнопку мышью. Для удаления формата надо нажать кнопку повторно.

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

Форматирование отдельных символов

Форматирование можно применять к отдельным символам текстового значения в ячейке так же, как и ко всей ячейке. Для этого выделите нужные символы и затем в меню "Формат" выберите команду "Ячейки". Установите нужные атрибуты и нажмите кнопку "Ок". Нажмите клавишу Enter, чтобы увидеть результаты своего труда.

Применение автоформата

Автоматические форматы Excel - это заранее определенные сочетания числового формата, шрифта, выравнивания, границ, узора, ширины столбца и высоты строки.

Чтобы использовать автоформат, надо выполнить следующие действия:

1. Введите нужные данные в таблицу.

2. Выделите диапазон ячеек, которые необходимо форматировать.

3. В меню "Формат" выберите команду "Автоформат". При этом откроется окно диалога.

4. В окне диалога "Автоформат" нажмите кнопку "Параметры", чтобы отобразить область "Изменить".

5. Выберите подходящий автоформат и нажмите кнопку "Ок".

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

Организация вычислений в Excel

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

По умолчанию Excel выполняет пересчет всегда, когда изменения воздействуют на значения ячеек. Если пересчитывается достаточно много ячеек, в левой части строки состояния появляются слова «Расчет ячеек» и некоторое число. Число показывает процент выполненного перерасчета ячеек. Процесс пересчета можно прервать. При вводе какой-либо команды и значения в ячейку во время выполнения пересчета Excel приостановит обновление вычислений и продолжит его, когда пользователь закончит операцию.

При внесении изменений в большую книгу с множеством формул бывает удобно отказаться от автоматического пересчета и выполнять его вручную. В этом случае Excel будет выполнять пересчет только тогда, когда пользователь даст явно соответствующую команду. Чтобы установить ручное обновление вычислений, следует включить режим Вручную на вкладке Вычисления команды Параметры меню Сервис. Чтобы обновить значения формул после изменений, следует нажать кнопку F9. Excel вычислит значения всех ячеек во всех листах, на которые воздействуют изменения, сделанные после последнего пересчета. Чтобы пересчитать только активный лист, следует нажать комбинацию клавиш Ctrl и F9. Даже если установлено ручное обновление вычислений, Excel обычно пересчитывает всю книгу при ее сохранении на жестком диске. Чтобы отменить такой режим, следует в диалоговом окне рассмотренной команды снять флажок Пересчет перед сохранением.

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

Существуют следующие правила ввода функций:

1. Имя функции всегда вводится после знака «=».

2. Аргументы заключаются в круглые скобки, указывающие на начало и конец списка аргументов.

3. Между именем функции и знаком « ( » пробел не ставится.

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

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

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

1. Арифметические и тригонометрические.

2. Инженерные, предназначенные для выполнения инженерного анализа (функции для работы с комплексными переменными; преобразования чисел из одной системы счисления в другую; преобразование величин из одной системы мер в другую).

3. Информационные, предназначенные для определения типа данных, хранимых в ячейках.

4. Логические, предназначенные для проверки выполнения условия или нескольких условий (ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ).

5. Статистические, предназначенные для выполнения статистического анализа данных.

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

7. Функции баз данных, предназначенные для анализа данных из списков или баз данных.

8. Текстовые функции, предназначенные для обработки текста (преобразование, сравнение, сцепление строк текста и т. д.).

9. Функции работы с датой и временем. Они позволяют анализировать и работать со значениями даты и времени в формулах.

10. Нестандартные функции. Это функции, созданные пользователем для собственных нужд. Создание функций осуществляется с помощью языка Visual Basic.

Командная кнопка Автосумма б на панели инструментов предназначена для автосуммирования, т. е. для получения итоговых данных для любых указанных диапазонов данных с помощью функции СУММ. Технология работы с командой автосуммирования следующая:

1. выделить ячейку, в которой должен располагаться итог;

2. щелкнуть по кнопке Автосумма;

3. будет предложен диапазон для суммирования (он окружен подвижной рамкой). Если диапазон неверен, следует выделить нужный диапазон (ячейка, смежные ячейки, несмежные ячейки — в любой комбинации);

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

Работать с простейшими арифметическими операциями в Excel может любой пользователь компьютера, для этого не требуется профильное образование. Это как работать с калькулятором, только более удобным и более гибким. Таблицы же в Excel помогут нам в любых жизненных ситуациях, которые нам буквально нужно просчитать. Это банально могут быть списки и подсчёты покупки еды и каких-то повседневных вещей, списки и подсчёты сданных денег на школьный ремонт или какое-то прочее общественное дело. А могут быть сметы на ремонт или строительство жилья, фиксация движения вверенных подотчётных денег и подобного рода значимые ситуации. Все эти списки не нужно хранить где-то в блокнотах или заметках и потом скрупулёзно высчитывать на калькуляторе все их пункты. Эти списки можно вести в программе Excel, в ней же и проводить все необходимые подсчёты.

Базовые арифметические операции в Excel – это:

Сложение (знак «+» на клавиатуре),
Вычитание (знак «-» на клавиатуре),
Умножение (знак «*» на клавиатуре),
Деление (знак «/» на клавиатуре),
Возведение в степень (знак «^» на клавиатуре).

Все операции производятся в ячейках таблицы после установки знака равенства «=». После ввода операций жмем Enter для получения результата. Комбинируя базовые операции, можно проводить более сложные арифметические операции. И при необходимости использовать их формулы уже для оперирования другими данными. Давайте посмотрим, как всё это работает.

Excel как калькулятор

Таблицу Excel можно использовать элементарно как обычный калькулятор. Если возникнет необходимость отдельно от таблицы с данными произвести какие-то промежуточные вычисления, нам теперь не нужно отвлекаться и использовать отдельное приложение калькулятора, всё делается у нас в любой пустой ячейке Microsoft Excel. Ставим знак «=». Вводим нужное арифметическое вычисление – сложение, вычитание, умножение, деление, возведение в степень. Жмём Enter и получаем результат.

Можем произвести более сложное вычисление.

Если вычисление нужно скорректировать или доработать, тогда кликаем ячейку с результатом. Делаем клик в строке формул Excel, изменяем или довычисляем что нужно. Жмём Enter.

Арифметические операции с данными таблицы

Рассмотрев принцип выполнения арифметических операций непосредственно с числами можем поступить и с табличными значениями. Возьмём какой-нибудь элементарный расчёт, вот например, годовой расчёт выплаты кредита. Каждый месяц надо уплатить и тело кредита и проценты. Вносим эти данные в таблицу. Последний столбец у нас будет для вычислений – за каждый месяц и в общем. Чтобы узнать общую сумму наших отчислений в месяц, сложим тело кредита и проценты. А в последнем столбце в ячейке первого месяца мы ставим знак «=». Кликаем ячейку тела кредита, ставим знак «+», затем кликаем на ячейку суммы процентов. Жмём Enter.

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

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

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

И, наконец, нам нужно узнать общую сумму за все 12 месяцев. Отдельно по телу нашего кредита, отдельно по процентам, а также вместе тело и проценты. Здесь можно применить метод обычных вычислений: поставить в итоговой ячейке знак «=», сложить каждые 12 ячеек, нажать Enter. И так нам нужно будет сделать со всеми тремя столбцами – тела, процентов и общей их суммы.

И мы получим итоговые суммы.

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

Автосумма

Чтобы получить итоговую сумму значений подряд идущих ячеек (вертикально, горизонтально – не важно), мы выделяем блоком эти ячейки. Во вкладке панели инструментов Excel «Формула» кликаем пункт «Автосумма». И в ячейке ниже или правее получаем результат.

Значок автосуммы также вынесен на главную вкладку панели инструментов. Но непосредственно автосумма – это только основная из всей подборки операций. В выпадающем списке автосуммы мы можем выбрать и другие операции, которые можно провести с идущими подряд ячейками – вычисление среднего значения, определение минимального или максимального значения, подсчёт выделенных ячеек.

Читайте также: