Как Создать Программу Для Вычисления на Excel • Сводные таблицы

Создание приложений Excel на языке VBA

Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.

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

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

VBA — `объектно — ориентированный язык программирования высокого уровня, являющийся одним из диалектов языка программирования Visual Basic.

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

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

Внутренняя норма доходности (англ. internal rate of return, общепринятое сокращение — IRR (ВНД)) — это процентная ставка, при которой чистая приведённая стоимость (чистый дисконтированный доход — NPV) равна 0. NPV рассчитывается на основании потока платежей, дисконтированного к сегодняшнему дню.

Таким образом, для потока платежей CF, где CFt — платёж через лет () и начальной инвестиции в размере IC=? CFo внутренняя норма доходности рассчитывается из уравнения:

Если все CFt положительны, то уравнение имеет единственный корень, больший ?1. Если в уравнении все платежи и свободный член положительны, то уравнение имеет положительный корень, то есть внутренняя норма доходности положительна.

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

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

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

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

Для нахождения внутренней нормы доходности используются численные методы решения нелинейных уравнений. В программе используется метод половинного деления. Рассмотрим его содержание.

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

Будем считать, что корень t функции f(x) отделён на отрезке [a;b]. Задача заключается в том, чтобы найти и уточнить этот корень методом половинного деления. Другими словами, требуется найти приближённое значение корня с заданной точностью .

Пусть функция f непрерывна на отрезке [a;b], и — единственный корень уравнения

Поделим отрезок [a;b] пополам. Получим точку и два отрезка [a;c] и [c;b].

Если нет, то из двух полученных отрезков [a;c] и [c;b] надо выбрать один [a1;b1] такой, что , то есть

Новый отрезок [a1;b1] делим пополам. Получаем середину этого отрезка так далее.

Для того, чтобы найти приближённое значение корня с точностью до , необходимо остановить процесс половинного деления на таком шаге n, на котором и вычислить Тогда можно взять

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

Программа может работать в различных версиях Excel: Excel 97, Excel 2002 и Excel 2013 под управлением операционной системы Windows. Приведем листинг программы.

MsgBox » Не введено число платежей или не числовое данное», vbInformation, «Ошибка»

MsgBox «Не введен объем инвестиций или не числовое данное», vbInformation, «Ошибка»

MsgBox «Ошибка при вводе данных», vbInformation, «Ошибка»

MsgBox «Ошибка при вводе данных», vbInformation, «Ошибка»

MsgBox «Левая граница больше или равна правой. Проверьте данные», vbInformation, «Ошибка»

MsgBox «Ошибка при вводе данных», vbInformation, «Ошибка»

MsgBox «Ошибка при вводе данных. Точность вычислений не может быть отрицательной», vbInformation, «Ошибка»

IsNumeric(Worksheets(«Данные»).Cells(i, 1).Value) = False Then

MsgBox «Или пустая ячейка или не числовые данные», vbInformation, «Ошибка»

Нахождение внутренней нормы доходности методом половинного деления

MsgBox «Внутренняя норма доходности с точностью » + Trim(CStr(epsilon)) + » равна: » + CStr(c), vbInformation, «Решение»

Найдем внутреннюю норму доходности t с точностью 0,001. Уравнение для нахождения внутренней нормы доходности имеет следующий вид:

Предварительно было установлено, что корень уравнения находится на отрезке [0; 1].

В результате решения значение внутренней нормы доходности равно 0,155 или 15,5% с точностью 0,001. Значение левой части уравнения приближенно равно ?0,046.

1. Уокенбах, Д.. Профессиональное программирование на VBA в Excel 2002. М.: Издательский дом «Вильямс», 2003.

2. Гарнаев А. Ю. Excel, VBA, Internet в экономике и финансах. СПб: БХВ — Петербург, 2003.

3. Гарнаев А. Ю. Самоучитель VBA — СПб.: БХВ — Санкт — Петербург, 2000.

4. Румянцева Е. Л., Трояновский В. М. Microsoft Excel как инструмент разработки информационных систем // Мир ПК, май, 2004.

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Здесь вы можете выбрать, следует ли создавать ряды по столбцам или по строкам, насколько должно увеличиваться значение в последующих ячейках и каким должно быть окончательное значение. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Среди них приложение «Семейная безопасность», инновационные решения для Microsoft Word, Excel или PowerPoint, расширенные функции хранения файлов в облаке OneDrive, а также службы Microsoft Teams и Outlook.

Полезные функции Microsoft Excel – руководство для новичков

Большая часть работы в Excel основана на таблицах. Таблицы содержат данные, которые нам часто нужны или которые мы хотим полностью удалить из электронной таблицы. Но как быстро выбрать все необходимые данные из большой электронной таблицы? Один из способов – настроить фильтры в Excel.

Автосуммирование

Кнопка Автосумма (AutoSum) — ∑ может использоваться для автоматического создания формулы, которая суммирует область соседних ячеек, находящихся непосредственно слева в данной строке и непосредственно выше в данном столбце.

  1. Выберите ячейку, в которую надо поместить результат суммирования.
  2. Щелкните кнопку Автосумма — ∑ или нажмите комбинацию клавиш Alt+=. Excel примет решение, какую область включить в диапазон суммирования, и выделит ее пунктирной движущейся рамкой, называемой границей.
  3. Нажмите Enter для принятия области, которую выбрала программа Excel, или выберите с помощью мыши новую область и затем нажмите Enter.

Функция «Автосумма» автоматически трансформируется в случае добавления и удаления ячеек внутри области.

Таблица 23. Исходная таблица данных

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

  1. Выберите ячейку В8 и наберите знак (=).
  2. Щелкните мышью в ячейке В7, которая является первым операндом в формуле.
  3. Введите с клавиатуры знак (-) и щелкните мышью в ячейке ВЗ, которая является вторым операндом в формуле (будет введена формула).
  4. Нажмите Enter (в ячейке В8 будет вычислен результат).
  5. Повторите пункты 5-8 для вычислений по соответствующим формулам в ячейках С8 и 08.
  6. Сохраните файл с именем Образование_сотрудников.х1s.

Таблица 24. Результат расчета

А B С D Е F
1 Распределение сотрудников по образованию
2 Магнолия Лилия Фиалка Всего
3 Высшее 25 20 9
4 Среднее спец. 28 23 21
5 ПТУ 27 58 20
6 Другое 8 10 9
7 Всего 88 111 59
8 Без высшего 63 91 50
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Если я нажму на любую ячейку и внутри напечатаю 3 2, а затем нажму кнопку Enter на клавиатуре, то ничего не посчитается — в ячейке будет написано 3 2. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
А теперь попробуем посчитать. Допустим, нам нужно сложить число 122596 с числом 14830. Для этого щелкните левой кнопкой мышки по любой ячейке. Как я уже говорил, все вычисления в Excel начинаются со знака «=». Значит, в ячейке нужно напечатать =122596+14830

Умные Таблицы Excel – секреты эффективной работы.

  1. Выделите ячейки ВЗ:С5 (табл. 27).
  2. Выберите в меню Вставка, Имя (Insert, Name) команду Создать (Create), появится диалоговое окно Создать имена (рис. 88).
  3. Убедитесь, что переключатель в столбце слева помечен и нажмите ОК.
  4. Выделите ячейки ВЗ:СЗ и убедитесь, что в поле имени указано Иванов.

Имеется несколько различных видов имущества, которые можно передать по наследству. Используя данные налоговой шкалы на имущество, передаваемого по наследству (Таблица 1), определите налог на имущество.

А B С D Е F
1 Распределение сотрудников по образованию
2 Магнолия Лилия Фиалка Всего
3 Высшее 25 20 9
4 Среднее спец. 28 23 21
5 ПТУ 27 58 20
6 Другое 8 10 9
7 Всего 88 111 59
8 Без высшего 63 91 50

Что такое Excel и как он выглядит?

Как устроен Excel

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

Рабочее окно в приложении Excel

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

Панель закладок – самый важный элемент приложения. Наиболее важные вкладки включают:

Структура листа Excel

Самым важным элементом Excel, над которым выполняется работа, является лист.

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
В случае нескольких введенных данных ручное пополнение их в отдельных ячейках не является большой проблемой, однако, когда мы говорим об огромном листе, содержащем, например, данные нескольких десятков сотрудников, дело обстоит совсем иначе. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Возможность автоматического разделения текста на несколько ячеек – чрезвычайно полезная функция. В случае нескольких введенных данных ручное пополнение их в отдельных ячейках не является большой проблемой, однако, когда мы говорим об огромном листе, содержащем, например, данные нескольких десятков сотрудников, дело обстоит совсем иначе.

Практические работы по Excel: готовые и на заказ

  • Перейдите на вкладку Главная, нажмите «Найти и выделить» и после раскрытия меню нажмите ПерейтиСпециальные. Вы также можете сделать это с помощью сочетания клавиш Ctrl + G ;
  • Выберите параметр «Проверка данных», и будут выбраны все раскрывающиеся списки, доступные на листе.

Язык программирования как система обозначений, применяемая в описании алгоритмов для ЭВМ. Разработка программы на языке программирования Бейсик. Освоение приемов работы с электронными таблицами MS Excel. Создание базы данных с помощью СУБД MS Access.

Готовые лабораторные работы по Excel

В диапазоне ячеек A1:E3 создайте копию, приведенной ниже таблицы.

Введите в одну ячейку A1 листа 2 предложение и отформатируйте следующим образом:

На листе 4
a) Записать в ячейки A1-A12 названия всех месяцев года, начиная с января.
b) Записать в ячейки B1-G1 названия всех месяцев второго полугодия
c) Записать в ячейки A13-G13 названия дней недели

На листе 7 построить таблицу Пифагора (таблицу умножения). Скопировать полученную таблицу на свободное место листа, уменьшив значения в три раза.

Дополнительные задания для самостоятельной работы (1С, 2С, 3С, 4С) в файле.

Выполните вычисления по следующим формулам: $A=4+3x+2x^2+x^3, B=(x+y+z)/(xyz), C=\sqrt$ считая заданными величины x, y, z соответственно в ячейках A3, B3 и C3.

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

На листе запишите формулу для вычисления произведения сумм двух одномерных массивов A и B, т.е. где ai и bi соответствующие элементы массивов, а n – их размерность

На листе запишите формулы вычисления сумм Si каждой строки двумерного массива (матрицы) D, т.е. где m – количество строк матрицы, n – количество столбцов

На листе запишите формулы для вычисления значений элементов массива Yi = ai / max(bi) ,i=1, 2,…,n, где ai и bi элементы соответствующих массивов, а n – их размерность.

На листе задайте произвольный массив чисел. Вычислите сумму положительных чисел и количество отрицательных чисел в этом массиве.

На листе заполните произвольный диапазон любыми числами. Найдите сумму чисел больших заданного в ячейке A1 числа.

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

На листе создайте произвольный список имен, и присвойте ему имя ИМЕНА. Определите, сколько раз в списке ИМЕНА содержится Ваше имя, заданное в ячейке.

Написать формулы, заполнения диапазона А1:A100 равномерно распределенными случайными числами из отрезка [-3,55; 6,55], а диа-пазона B1:B100 случайными целыми числами из отрезка [-20;80]. Скопировать значения указанных диапазонов в диапазоны D1:D100 и E1:E100, увеличив вдвое значения второго диапазона.

Составьте электронную таблицу для решения уравнения вида $ax^2+bx+c=0$ с анализом дискриминанта и коэффициентов $a, b, c$. Для обозначения коэффициентов, дискриминанта и корней уравнения применить имена.

Дана таблица с итогами экзаменационной сессии. Составить электронную таблицу, определяющую стипендию по следующему правилу: По рассчитанному среднему баллу за экзаменационную сессию (s) вычисляется повышающий коэффициент (k), на который затем умножается минимальная стипендия (m).

По результатам сдачи сессии группой студентов (таблица Итоги экзаменационной сессии), определить — количество сдавших сессию на «отлично» (9 и 10 баллов);
— на «хорошо» и «отлично» (6-10 баллов);
— количество неуспевающих (имеющих 2 балла);
— самый «сложный» предмет;
— фамилию студента, с наивысшим средним баллом.

Пусть в ячейках A1,A2,A3 записаны три числа, задающих длины сторон треугольника. Написать формулу:
— определения типа треугольника (равносторонний, равнобедренный, разносторонний),
— определения типа треугольника (прямоугольный, остроугольный, тупоугольный),
— вычисления площади треугольника, если он существует. В противном случае в ячейку В6 вывести слово «нет».

1. Консолидация данных на листе.
2. Создание именованных диапазонов.
3. Поиск решения с помощью подбора параметров.
4. Выделение изменений, внесенных в книгу.
5. Вставка примечаний.
6. Ограничение доступа к документам Excel.

Имеется несколько различных видов имущества, которые можно передать по наследству. Используя данные налоговой шкалы на имущество, передаваемого по наследству (Таблица 1), определите налог на имущество.

При выполнении задания используйте только две ячейки: одну – для ввода суммы сбыта, другую – для формулы расчета комиссионных (Используйте функцию ЕСЛИ). Предусмотрите ситуацию, когда пользователь в ячейку для ввода суммы сбыта вводит недопустимое значение, например, отрицательное число (команда Данные/Проверка).

Выполните расчет выручки, всех издержек и прибыли с помощью Microsoft Excel. Постройте графики AVC, ATC и МС (диаграмма 1) и ТС и TR (диаграмма 2) .

(задача производства изделий, транспортная задача, игра с природой, сетевое планирование
Условия задач

Задача 2. На строительство четырех объектов (1,2,3,4) кирпич поступает с трех (I, II, III) заводов. Заводы имеют на складах соответственно 50, 100 и 50 тыс. шт. кирпича. Объекты требуют соответственно 50, 70, 40, 40 тыс. шт. кирпича. Тарифы (д.е./ тыс. шт) приведены в следующей таблице. Составьте план перевозок, минимизирующий суммарные транспортные расходы.

Задача 3. Дана платежная матрица Р игры с природой.
Известный вероятности наступления событий П природы и равны (0,2; 0,54; 0,26).
Найти оптимальное поведение игрока для максимизации среднеожидаемого выигрыша.

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
После чего осуществляется возврат итогового значения из ячейки, которая располагается на пересечении выбранной строчки и столбца. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Использование относительных ссылок аналогично указанию направления движения по улице — «идти три квартала на север, затем два квартала на запад». Следование этим инструкциям из различных начальных мест будет приводить в разные места назначения.
Как Создать Программу Для Вычисления на Excel • Сводные таблицы

Вычисление функций в экселе. Расчеты в Excel

  • Создание и редактирование электронных таблиц
  • Стилевое оформление и печать таблиц
  • Построение графиков, создание диаграмм различного типа
  • Продвинутые действия с таблицами: сортировка, фильтрация, сводные таблицы, консолидация данных
  • Использование встроенных функций Эксель
  • Реализация некоторых методов вычислений
  • Решение экономических задач с помощью подбора параметра
  • Решение оптимизационных задач (раздел Поиск решения)
  • Решение статистических задач: анализ и статистическая обработка выборки, проверка гипотез, корреляция
  • Использование макросов VBA
  • Связь с другими программами серии MS Office

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

Как правильно считать

Но, как правило, этот способ «счета» используется не так часто. Существует более продвинутый вариант.

Начну с первой позиции «Сыр». Щелкаю в ячейке D2 и печатаю знак равно.

Затем нажимаю на ячейку B2, так как нужно ее значение умножить на C2.

И, наконец, нажимаю кнопку Enter на клавиатуре. Все! В ячейке D2 получился нужный результат.

Щелкнув по этой ячейке (D2) и посмотрев в строку формул, можно увидеть, как получилось данное значение.

Объясню на примере этой же таблицы. Сейчас в ячейке B2 введено число 213. Удаляю его, печатаю другое число и нажимаю Enter.

Результат изменился. Это произошло из-за того, что поменялось значение в B2. Ведь формула у нас следующая: =B2*C2

Это означает, что программа Microsoft Excel умножает содержимое ячейки B2 на содержимое ячейки C2, каким бы оно не было. Выводы делайте сами:)

Попробуйте составить такую же таблицу и вычислить сумму в оставшихся ячейках (D3, D4, D5).

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Циклическая ссылка может вызывать искажения в вычислениях на рабочем листе и поэтому рассматривается как ошибка в большинстве приложений. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на нуль. Чаще всего это случается, когда в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение.

Создание приложений Excel на языке VBA

  • Введите символ равенства (=) в ячейку. Это говорит Excel, что вы вводите формулу, а не только цифры.
  • Введите уравнение, которое вы хотите посчитать. Например, чтобы посчитать сумму 5 и 7, введите =5+7
  • Нажмите клавишу Enter, чтобы завершить расчет. Готово!

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: