Mini-ats102.ru

ООО “Мультилайн”
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Арифметические операции в Excel

Арифметические операции в Excel

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

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

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

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

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

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

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

Арифметические операции в Excel

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

Арифметические операции в Excel

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

Арифметические операции в Excel

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

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

Арифметические операции в Excel

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

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

Читайте так же:
Использование индекс и поискпоз в excel

Арифметические операции в Excel

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

Арифметические операции в Excel

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

Арифметические операции в Excel

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

Арифметические операции в Excel

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

Автосумма

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

Арифметические операции в Excel

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

Арифметические операции в Excel

Если у вас остались вопросы, оставляйте их в комментариях ниже. А пока… пока.

Переменные и константы в VBA

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

Что такое переменные?

Переменная это некоторое выделенное пространство в памяти компьютера, которое может содержать данные разных типов – числовые, текстовые, логические и т.д. (о типах данных в VBA рассказано в этой статье), а так же результаты вычислений. Значение переменной присваивается с помощью знака равенства. Для удобства работы с переменными в языках программирования предусмотрена возможность задания человеко-понятных имен, к примеру, имена переменных rFIO, rBirthday. К именам переменных в VBA имеется ряд требований:

  • Переменная может содержать латинские символы, числа. Использование пробела и точки в имени переменной недопустимо (вместо пробела программисты используют знак «_»);
  • Имя переменной должно начинаться с текстового символа (числа в начале не допустимы);
  • Не допускается использование следующих символов: #, $, %, &, !. Эти символы в VBA зарезервированы за кратким указанием типа переменной без ее описания. К примеру: содержание символа $ (MyTxt$) в конце имени переменной указывает, что значение хранимое в этой переменной текстового типа;
  • Недопустимо использование зарезервированных слов. Если все ж для понимания назначения переменной необходимо использование в имени зарезервированного слова, то можно добавить некоторый символ или символы, например: Date зарезервировано (тип дата), для использования можно добавить «My», после чего получим допустимое — MyDate;
  • Имя переменной не должно быть длиннее 254 символов. Но я не думаю, что кому-то придет в голову для переменной задавать имя длиннее 15-20 символов, не говоря уже о 254;
  • Язык VB не чувствителен к регистру, поэтому переменные MyVar и myvar для VBA одинаковы.

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

Читайте так же:
Как в фотошопе вырезать волосы с фона

Описание переменных в VBA

Под описанием переменной подразумевается указание типа данных. В VBA переменные можно указывать явным и неявным образом. Не явным образом означает, что Вы можете в любом месте кода указать имя переменной и начать с ней работать, в таком случае тип этой переменной принимается как Variant. Такой способ удобен, но не рекомендуется т.к. может возникнуть путаница и как следствие, ошибки при вычислениях. Явное описание осуществляется после ключевого слова Dim [имя переменной] As [Тип], например: Dim MyInt As Integer. В случае такого описания переменная MyInt будет хранить в памяти только значения целого типа.

Обязательное объявление всех переменных

Как уже говорилось выше переменные в VBA можно объявить, а можно и не объявлять. Со своей стороны я рекомендую объявлять каждую переменную, используемую в программе. Это позволить сделать код более понятным, дисциплинирует, и в крупных разработках позволит сэкономить память и увеличить быстродействие при обработке данных. Для того чтоб в VBA включить обязательное объявление всех переменных необходимо в начале модуля добавить строку: Option Explicit. После, на каждой не объявленной переменной будет происходить остановка программы, и отображаться ошибка до тех пор, пока всем переменным не будет присвоен тип в разделе Dim.

Примечание: Для того чтоб в VBE (Visual Basic Editor) оператор Option Explicit вставлялся автоматически в каждый новый модуль, необходимо в настройках редактора VBE активировать данную опцию: Tools-Options. , на вкладке Editor поставить галочку «Require Variable Declaration»

Константы в VBA

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

Const MyIntConst as Integer = 8
Const MyTxtConst as String = «Константа»

или без указания типа:
Const MyConst = 4,55
в этом случае константа типа Variant.

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

Закрепим все вышесказанное примером небольшого макроса, который рассчитает функцию y = x+i*2, где x=i/7, 0<i<100 шаг 1.

Как видно из функции y, x, i это переменные. Так как при расчетах используется деление, то y, x будут содержать значения вещественного типа. Переменная i это счетчик с шагом 1. 2 постоянное значение, т.е. константа. Результат y будет выведен в диалоговом сообщении msgBox.

Создадим в редакторе VBE новый модуль и добавим туда следующий код:

Option Explicit

Sub VarExample()
Const MyConst As Byte = 2
Dim Y As Single
Dim X As Single
Dim i As Integer

For i = 1 To 100
X = i / 7
Y = X + i * MyConst
Next i
MsgBox «Результат равен » & Y
End Sub

Запускаем наш макрос. В результате выполнения данной процедуры мы получим Y равное 214,2857.

На этом все. Подписывайтесь на рассылки сайта и будьте в курсе появления новых статей и уроков. До встреч!

Читайте так же:
Как в ворде знаки подсчитать

Как сделать формулу в Excel более читаемой?

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

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

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

Например, мы прекрасно понимаем что запись вида «2+3» и «2 + 3» это одно и тоже, хоть они записаны немного по разному. Excel тоже так умеет, поэтому мы можем ставить пробелы и переносить часть формулы на следующие строки, в тех случаях когда не нарушается логика чтения синтаксиса формулы.

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

Приведение формулы в читаемый вид в Excel

Напомню, что перенос строки в Excel делается с помощью горячих клавиш — в режиме редактирования нужно воспользоваться сочетанием клавиш Alt + Enter.

Метод 2 — Использование точечной диаграммы для получения значения наклона

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

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

Ниже приведены шаги для этого:

  • Выберите точки данных X и Y (в нашем примере это будут столбцы высоты и дохода).
  • Щелкните вкладку «Вставка» на ленте.

  • Щелкните раскрывающееся меню «Вставить разброс» (в группе «Диаграммы»).
  • В появившемся раскрывающемся списке выберите параметр «Точечная диаграмма».

  • Это вставит точечную диаграмму в ваш рабочий лист, отображая ваши значения xy в виде точек разброса (как показано ниже).

  • Щелкните правой кнопкой мыши одну из точек разброса и выберите «Добавить линию тренда» в появившемся контекстном меню. Это вставит линию тренда, а также откроет панель «Форматировать линию тренда» справа.

  • На панели «Форматирование линии тренда» в разделе «Параметры линии тренда» установите флажок «Отображать уравнение на диаграмме».

  • Закройте панель «Форматировать линию тренда»

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

В нашем примере мы получаем следующее уравнение:

у = 138,56x + 65803

  • 138,56 — наклон линии регрессии
  • 65803 — точка пересечения линии регрессии

Вы можете сравнить это со значениями, которые мы получили от функций SLOPE и INTERCEPT (это то же значение).

Если значение наклона положительное, вы увидите, что линия тренда идет вверх, а если значение наклона отрицательное, вы увидите, что линия тренда идет вниз. Крутизна склона будет зависеть от его значения уклона.

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

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

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

Перебор элементов массива

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

Читайте так же:
Как в ворде добавить абзац

Наиболее удобный оператор цикла для перебора элементов массива — это безусловно For . Next .

так же есть способ не заботиться об определении нижней и верхней границ, если алгоритм не требует от нас знания текущего индекса массива:

Вы, конечно, можете перебирать массив и в других типах циклов Do . Loop , но, право, смысла и удобства в этом не много. По крайней мере я не сталкивался, кажется, с ситуациями, когда для перебора массива цикл For не подошёл.

Глобальные переменные VBA

Мы обычно объявляем любую переменную, используя слово DIM, и это лучший прямой способ сделать это. Но переменные, объявленные с использованием DIM, одна только в одном модуле. Если мы попытаемся использовать эту переменную, определенную с помощью DIM, она будет применима для модуля, в котором она объявлена. Что если мы хотим использовать переменную в любом модуле или проекте? Что не может быть возможным, если мы продолжаем использовать DIM для данной электрической сети?

В этом случае у нас есть вторичная опция, определяющая переменную по слову Global или Public в VBA. Глобальные или публичные переменные VBA — это те переменные, которые объявлены в начале подкатегории с Option Explicit. Что бы мы ни делали или не могли делать в подкатегориях, они выполняются до этого в Option Explicit.

Как объявить глобальные переменные в VBA?

Ниже приведены примеры объявлений глобальной сети Excel с использованием VBA.

Вы можете скачать этот шаблон Excel для глобальных чисел VBA здесь — Шаблон Excel для глобальных чисел VBA

Глобальные переменные VBA — Пример № 1

. В этом примере мы увидим, как различные варианты использования глобального или открытого слова для определения какой-либо альтернативы. Для этого нам понадобится модуль.

  • Перейдите в меню « Вставка» и нажмите « Модуль», как показано ниже.

  • Теперь введите подкатегорию под любым именем, как показано ниже.

Код:

  • Теперь використовуйте DIM для любой определение. Здесь мы выбрали алфавит A в качествестроки .

Код:

  • Теперь создайте другую подкатегорию в том же модуле, как показано ниже.

Код:

  • Теперь во второй подкатегории определите другую переменную B, используя DIM в качестве String.

Код:

Как показано выше, обе переменные A и B, источники в обеих подкатегориях, не одна из них в своей подкатегории.

  • Теперь, чтобы сделать это, напишите Option Explicit над первой подкатегорией, как показано ниже.

Код:

  • . Теперь мы можем определить нашу переменную, которая будет указывать в разных подкатегориях, которые у нас есть ниже, теперь рассмотрим запись любого типа, скажем, A как String, используя DIM.

Код:

Времена мы определили переменную, все подкатего будут в этом модуле, и это будет применимо тольк о ко всему коду этого модуля.

Глобальные переменные VBA — Пример № 2

, если мы попытаемся вызвать переменные, это даст нам ошибку. В этом примере мы увидим, как использовать глобальное или открытое слово для поиска альтернатив в Excel VBA. Для этого мы будем использовать код, который мы видели в примере-1.

Мы будем использовать нижнюю часть кода для глобальной публичной библиотеки

Код:

Как мы это делали в примере 1, где мы объявили общую переменную, которая будет сообщать обе подкатегориях, под Option Explicit. В этом случае мы можем объявить глобальную переменную явным явным кодом

, мы можем объявить переменные напрямую, используя Global . Теперь в приведенном ниже параметре Explicit напишите Global так же, как мы использовали для DIM, и выберите переменную по выбору. Здесь мы выбираем алфавит A, как показано ниже.

Читайте так же:
Как быстро выделить человека в фотошопе

Код:

Теперь мы выбираем переменную любого типа. Как мы уже использовали String в приведенном выше примере, мы бы тоже использовали это здесь.

Код:

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

Код:

Теперь давайте назначим некоторый текст категории других подкатегориях. Мы выбираем «Test1» и «Test2» для использования в различных подкатегориях, как показано ниже. А также мы выбрали MsgBox, чтобы показать значения, хранящиеся в переменных A.

Код:

Теперь запустите код, разрешите F5 или выключите воспроизведение, чтобы увидеть результат.

Мы получим сообщение как «Test1», как показано выше. Это потому, что мы сохранили наш курсор в первой подкатегории.

Теперь поместите курсор в любую вторую подкатегорию и снова запустите код. Теперь мы получим окно сообщениями с сообщением «Test2», как показано ниже.

Вот как мы определяем переменную один раз с помощью Global, и это можно использовать в любом модуле, любом классе и в любом проекте. Мы также можем использовать Public вместо Global, как показано ниже. И это даст тот же результат, который мы получили при использовании Global.

Код:

Плюсы VBA Global Variable

  • Это экономит время при объявлении нескольких чисел, когда мы знаем, что нам может потребоваться снова
  • Используя глобальные или общедоступные в модели выбора метода, наш код становится меньше, экономя время и пространство.
  • Это уменьшает сложность, когда мы пишем огромный код, и может запутаться в разных модулях или подкатегориях.

Минусы VBA Global Variable

  • Если мы внесем какие-либо изменения в глобальную переменную, то эти изменения будут реализованы во все на месте использования написанного кода.

То, что нужно запомнить

  • Global и Public взаимозаменяемо с одинаковыми функциями.
  • Если мы не хотим использовать глобальную переменную, мы можем следовать процедуре, как показано в примере 1, для определения альтернатив. Но это будет ограничено тем же модулем.
  • Глобальные переменные могут быть в любом модуле, любой подкатегории, любом классе или в любом проекте.
  • Глобальная переменная дает вывод кода, в котором мы сохранили курсор. Он не будет запускать полный код за один раз, передавая все выходные по одному.

Рекомендуемые статьи

Это руководство по глобальным переменным VBA. Здесь мы обсудим, как объявлять глобальные переменные в Excel, используя код VBA, а также некоторые практические примеры и загружаемый шаблон Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

На конкретном примере

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

Порядок работы

  • Запустите MicrosoftExcel и создайте новую электронную книгу.
  • Создайте таблицу ежемесячных выплат по займу и платежей по процентам по образцу.

=ПЛТ ($В$4/12;$В$3*12;$В$2). Ежемесячная выплата составит 10178,42 р.

  • Расчет платежей по процентам происходит с помощью функции ПРОЦПЛАТ (). В ячейку D6 введите формулу:

=ПРОЦПЛАТ ($B$4;$D$5;$D$3;$D$2). Платежи по процентам составят 1350 р.

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector