Для постійних користувачів Excel не секрет, що в цій програмі можна виробляти різні математичні, інженерні та фінансові розрахунки. Дана можливість реалізується шляхом застосування різних формул і функцій. Але, якщо Ексель постійно використовувати для проведення подібних розрахунків, то актуальним стає питання організації необхідних для цього інструментів прямо на аркуші, що значно підвищить швидкість обчислень і рівень зручності для користувача. Давайте з'ясуємо, як зробити подібний калькулятор в Ексель.

Процедура створення калькулятора

Особливо нагальною ця задача стає в разі потреби постійно проводити однотипні обчислення і розрахунки, пов'язані з певним видом діяльності. В цілому все калькулятори в Excel можна розділити на дві групи: універсальні (використовуються для загальних математичних обчислень) і вузькопрофільні. Остання група ділиться на безліч видів: інженерні, фінансові, кредитні інвестиційні тощо Саме від функціональних можливостей калькулятора, в першу чергу, залежить вибір алгоритму його створення.

Спосіб 1: використання макросів

Перш за все, розглянемо алгоритми створення користувацьких калькуляторів. Почнемо зі створення найпростішого універсального калькулятора. Даний інструмент буде виконувати елементарні арифметичні дії: додавання, множення віднімання, ділення і т. Д. Він реалізований за допомогою макросу. Тому перш, ніж приступити до процедури створення, потрібно впевнитися, що у вас включені макроси і панель розробника. Якщо це не так, то обов'язково слід активувати роботу макросів .

  1. Після того, як зазначені вище попередні налаштування виконані, переміщаємося у вкладку «Розробник». Тиснемо на іконку «Visual Basic», яка розміщена на стрічці в блоці інструментів «Код».
  2. Перехід в редактор макросів в Microsoft Excel

  3. Запускається вікно редактора VBA. Якщо центральна область у вас відобразилася сірим кольором, а не білим, то це означає, що поле введення коду відсутня. Для включення його відображення переходимо до пункту меню «View» і тиснемо по напису «Code» в списку. Можна замість цих маніпуляцій натиснути функціональну клавішу F7. У будь-якому випадку поле для введення коду з'явиться.
  4. Включення поля для введення коду в редакторі макросів в Microsoft Excel

  5. Тут в центральній області нам потрібно записати сам код макросу. Він має такий вигляд:

    Sub Calculator()
    Dim strExpr As String
    ' Введение данных для расчета
    strExpr = InputBox("Введите данные")
    ' Вычисление результата
    MsgBox strExpr & " = " & Application.Evaluate(strExpr)
    End Sub

    Замість словосполучення «Введіть дані» ви можете записати будь-яке інше прийнятне для вас. Саме воно буде розташовуватися над полем введення виразу.

    Після того, як код введено, файл потрібно перезаписати. При цьому його слід зберегти у форматі з підтримкою макросів. Тиснемо на іконку у вигляді дискети на панелі інструментів редактора VBA.

  6. Введення коду в редакторі макросів в Microsoft Excel

  7. Запускається вікно збереження документа. Переходимо в ту директорію на жорсткому диску або знімному носії, де хочемо його зберегти. В поле «Ім'я файлу» присвоюємо документу будь-яке бажане найменування або залишаємо те, яке присвоєно йому за умовчанням. В обов'язковому порядку в полі «Тип файлу» з усіх доступних форматів вибираємо найменування «Книга Excel з підтримкою макросів (* .xlsm)». Після цього кроку клацають по кнопці «Зберегти» в нижній частині вікна.
  8. Збереження файлу в форматі xlsm в Microsoft Excel

  9. Після цього можна закривати вікно редактора макросів, просто натиснувши на стандартний значок закриття у вигляді червоного квадрата з білим хрестиком в його правому верхньому куті.
  10. Закриття вікна редактора макросів в Microsoft Excel

  11. Щоб запустити обчислювальний інструмент за допомогою макросу, перебуваючи у вкладці «Розробник», клацають по значку «Макроси» на стрічці в блоці інструментів «Код».
  12. Перехід у вікно макросів в Microsoft Excel

  13. Після цього запускається вікно макросів. Вибираємо найменування того макросу, який ми тільки що створювали, виділяємо його і тиснемо на кнопку «Виконати».
  14. Вікно макросів в Microsoft Excel

  15. Після виконання даної дії запускається калькулятор, створений на основі макросу.
  16. Калькулятор на основі макросу запущений в Microsoft Excel

  17. Для того, щоб зробити в ньому обчислення, записуємо в поле необхідну дію. Зручніше за все використовувати для цих цілей числовий блок клавіатури, який розташований праворуч. Після того, як вираз введено, тиснемо на кнопку «OK».
  18. Перехід до обчислення в калькуляторі на основі макросу запущений в Microsoft Excel

  19. Потім на екрані з'являється невелике віконце, яке містить в собі відповідь рішення заданого виразу. Для його закриття тиснемо на кнопку «OK».
  20. Результат обчислення в калькуляторі на основі макросу запущений в Microsoft Excel

  21. Але погодьтеся, що досить незручно кожен раз, коли потрібно провести обчислювальні дії, переходити в вікно макросів. Давайте спростимо реалізацію запуску вікна обчислень. Для цього, знаходячись у вкладці «Розробник», клацаємо по вже знайомій нам іконці «Макрос».
  22. Перехід у вікно макросів в програмі Microsoft Excel

  23. Потім у вікні макросів вибираємо найменування потрібного об'єкту. Клацаємо по кнопці «Параметри ...».
  24. Перехід в параметри макросу в Microsoft Excel

  25. Після цього запускається віконце ще менше попереднього. У ньому ми можемо поставити поєднання гарячих клавіш, при натисканні на які буде запускатися калькулятор. Важливо, щоб дане поєднання не використовувалося для виклику інших процесів. Тому перші символи алфавіту використовувати не рекомендується. Першу клавішу поєднання задає сама програма Ексель. Це клавіша Ctrl. Наступну клавішу задає користувач. Нехай це буде клавіша V (хоча ви можете вибрати і іншу). Якщо дана клавіша вже використовується програмою, то буде автоматично додана ще одна клавіша в комбінацію - S hift. Вписуємо обраний символ в полі «Швидкий доступ» і тиснемо на кнопку «OK».
  26. Вікно параметрів макросу в Microsoft Excel

  27. Потім закриваємо вікно макросів, натиснувши на стандартний значок його закриття у верхньому правому куті.

Закриття вікна макросів в Microsoft Excel

Тепер при наборі обраної комбінації гарячих клавіш (в нашому випадку Ctrl + Shift + V) буде запускатися вікно калькулятора. Погодьтеся, це набагато швидше і простіше, ніж кожен раз викликати його через вікно макросів.

урок: Як створити макрос в Ексель

Спосіб 2: застосування функцій

Тепер давайте розглянемо варіант створення вузькопрофільного калькулятора. Він буде призначений для виконання конкретних, специфічних завдань і розміщений безпосередньо на аркуші Excel. Для створення цього інструменту будуть застосовуватися вбудовані функції Ексель.

Для прикладу створимо інструмент конвертації величин маси. В процесі його створення нами буде використана функція граф. Даний оператор відноситься до інженерного блоку вбудованих функцій Ексель. Його завданням є перетворення величин однієї заходи вимірювання в іншу. Синтаксис цієї функції наступний:

=ПРЕОБР(число;исх_ед_изм;кон_ед_изм)

«Число» - це аргумент, який має вигляд числового значення тієї величини, яку треба конвертувати в інший запобіжний вимірювання.

«Вихідна одиниця виміру» - аргумент, який визначає одиницю виміру величини, що підлягає конвертації. Він задається спеціальним кодом, який відповідає певній одиниці виміру.

«Кінцева одиниця виміру» - аргумент, який визначає одиницю виміру тієї величини, в яку перетворюється вихідне число. Він також задається за допомогою спеціальних кодів.

Нам слід докладніше зупинитися на цих кодах, так як вони нам знадобляться в подальшому при створенні калькулятора. Саме нам знадобляться коди одиниць вимірювання маси. Ось їх перелік:

  • g - грам;
  • kg - кілограм;
  • mg - міліграм;
  • lbm - англійський фунт;
  • ozm - унція;
  • sg - Слег;
  • u - атомна одиниця.

Потрібно також сказати, що всі аргументи цієї функції можна задавати, як значеннями, так і посиланнями на комірки, де вони розміщені.

  1. Перш за все, робимо заготовку. У нашого обчислювального інструменту буде чотири поля:
    • Конвертована величина;
    • Вихідна одиниця виміру;
    • Результат конвертації;
    • Кінцева одиниця виміру.

    Встановлюємо заголовки, під якими будуть розміщуватися дані поля, і виділяємо їх форматуванням (заливанням і кордонами) для більш наочної візуалізації.

    У поля «Конвертована величина», «Початкова межа вимірювання» і «Кінцева межа вимірювання» нами будуть вводитися дані, а в полі «Результат конвертації» - виводитися кінцевий результат.

  2. Заготівля калькулятора конвертації маси в Microsoft Excel

  3. Зробимо так, щоб в поле «Конвертована величина» користувач міг вводити тільки допустимі значення, а саме числа більше нуля. Виділяємо осередок, в яку буде вноситися перетворюються величина. Переходимо у вкладку «Дані» і в блоці інструментів «Робота з даними» натискаємо по значку «Перевірка даних».
  4. Перехід до перевірки даних в Microsoft Excel

  5. Запускається віконце інструменту «Перевірка даних». Перш за все, виконаємо налаштування у вкладці «Параметри». В поле «Тип даних» зі списку вибираємо параметр «Справжнє». В поле «Значення» також зі списку зупиняємо вибір на параметрі «Більше». В поле «Мінімум» встановлюємо значення «0». Таким чином, в дану комірку можна буде вводити тільки дійсні числа (включно з дробові), які більше нуля.
  6. Вікно перевірки введених значень в Microsoft Excel

  7. Після цього переміщаємося у вкладку того ж вікна «Повідомлення для введення». Тут можна дати пояснення, що саме потрібно вводити користувачеві. Він його побачить при виділенні комірки введення величини. В поле «Повідомлення» напишемо наступне: «Введіть величину маси, яку слід перетворити».
  8. Повідомлення для введення в вікні перевірки введених значень в Microsoft Excel

  9. Потім переміщаємося у вкладку «Повідомлення про помилку». В поле «Повідомлення» нам слід написати ту рекомендацію, яку побачить користувач, якщо введе некоректні дані. Напишемо наступне: «Введене значення має бути позитивним числом». Після цього, щоб завершити роботу в вікні перевірки введених значень і зберегти введені нами настройки, тиснемо на кнопку «OK».
  10. Повідомлення про помилку в вікні перевірки введених значень в Microsoft Excel

  11. Як бачимо, при виділенні комірки з'являється підказка для введення.
  12. Підказка для введення при виділенні ячеку в Microsoft Excel

  13. Спробуємо ввести туди некоректне значення, наприклад, текст або негативне число. Як бачимо, з'являється повідомлення про помилку і введення блокується. Тиснемо на кнопку «Скасування».
  14. Повідомлення про помилку в Microsoft Excel

  15. А ось коректна величина вводиться без проблем.
  16. Коректна величина введена в Microsoft Excel

  17. Тепер переходимо до поля «Вихідна одиниця виміру». Тут ми зробимо так, що користувач буде вибирати значення зі списку, що складається з тих семи величин маси, перелік яких був наведений вище при описі аргументів функції граф. Ввести інші значення не вийде.

    Виділяємо осередок, яка знаходиться під найменуванням «Вихідна одиниця виміру». Знову клацають по іконці «Перевірка даних».

  18. Перехід до перевірки даних в програмі Microsoft Excel

  19. У вікні перевірки даних переходимо у вкладку «Параметри». В поле «Тип даних» встановлюємо параметр «Список». В поле «Джерело» через крапку з комою (;) перераховуємо коди найменувань величин маси для функції преобра, про які йшла мова вище. Далі тиснемо на кнопку «OK».
  20. Вікно перевірки введених значень в програмі Microsoft Excel

  21. Як бачимо, тепер, якщо виділити поле «Вихідна одиниця виміру», то праворуч від нього виникає піктограма у вигляді трикутника. При кліці по ній відкривається список з найменуваннями одиниць вимірювання маси.
  22. Список з найменуванням одиниць виміру маси в Microsoft Excel

  23. Абсолютно аналогічну процедуру у вікні «Перевірка даних» проводимо і з осередком з найменуванням «Кінцева одиниця виміру». У ній теж виходить точно такий же список одиниць вимірювання.
  24. Другий список едніц вимірювання в Microsoft Excel

  25. Після цього переходимо до осередку «Результат конвертації». Саме в ній міститиметься функція преобра і виводити результат обчислення. Виділяємо даний елемент листа і тиснемо на піктограму «Вставити функцію».
  26. Перехід в Майстер функцій в в Microsoft Excel

  27. Запускається Майстер функцій. Переходимо в ньому в категорію «Інженерні» і виділяємо там найменування «У граф». Потім клацають по кнопці «OK».
  28. Перехід у вікно аргументів функції преобра в Microsoft Excel

  29. Відбувається відкриття вікна аргументів оператора граф. В поле «Кількість» слід ввести координати осередку під найменуванням «Конвертована величина». Для цього ставимо в курсор в поле і натискаємо лівою кнопкою миші по цьому осередку. Її адреса тут же відображається в поле. Таким же чином вводимо координати в поля «Вихідна одиниця виміру» і «Кінцева одиниця виміру». Тільки на цей раз натискаємо по осередках з такими ж назвами, як у цих полів.

    Після того, як всі дані введені, тиснемо на кнопку «OK».

  30. вікно аргуметом функції преобра в Microsoft Excel

  31. Як тільки ми виконали останню дію, в віконці осередку «Результат конвертації» тут же відобразився результат перетворення величини, згідно з раніше введених даних.
  32. Результат обчислення функції преобра в Microsoft Excel

  33. Давайте змінимо дані в осередках «Конвертована величина», «Початкова одиниця вимірювання» і «Кінцева одиниця виміру». Як бачимо, функція при зміні параметрів автоматично перераховує результат. Це говорить про те, що наш калькулятор повністю функціонує.
  34. Повторний обчислення функції преобра в Microsoft Excel

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

    Проблема полягає в тому, що блокування встановлюється на лист в цілому. Але якщо ми заблокуємо лист, то не зможемо вводити дані в поля введення. Тому нам потрібно буде в властивості формату осередків зняти можливість блокування з усіх елементів листа, потім повернути цю можливість тільки осередку для виведення результату і вже після цього заблокувати лист.

    Кількома лівою кнопкою миші по елементу на перетині горизонтальної та вертикальної панелі координат. При цьому виділяється весь лист. Потім натискаємо правою кнопкою миші по виділенню. Відкривається контекстне меню, в якому вибираємо позицію «Формат ячеек ...».

  36. Перехід в формат осередків в Microsoft Excel

  37. Запускається вікно форматування. Переходимо в ньому у вкладку «Захист» та знімаємо галочку з параметра «Що Захищається ланка». Потім клацають по кнопці «OK».
  38. Зняття захисту з осередків в Microsoft Excel

  39. Після цього виділяємо тільки осередок для виведення результату і натискаємо по ній правою кнопкою миші. У контекстному меню клацають по пункту «Формат ячеек».
  40. Перехід в формат осередків в програмі Microsoft Excel

  41. Знову у вікні форматування переходимо у вкладку «Захист», але на цей раз, навпаки, встановлюємо галочку біля параметра «Що Захищається ланка». Потім клацаємо по кнопці «OK».
  42. Установка захисту осередки в Microsoft Excel

  43. Після цього переміщаємося у вкладку «Рецензування» і тиснемо на іконку «Захистити лист», яка розташована в блоці інструментів «Зміни».
  44. Установка захисту листа в Microsoft Excel

  45. Відкривається вікно установки захисту листа. В поле «Пароль для відключення захисту аркуша» вводимо пароль, за допомогою якого при необхідності в майбутньому можна буде зняти захист. Інші налаштування можна залишити без змін. Тиснемо на кнопку «OK».
  46. Вікно захисту листа в Microsoft Excel

  47. Потім відкривається ще одне невелике віконце, в якому слід повторити введення пароля. Робимо це і тиснемо на кнопку «OK».
  48. Повторне введення пароля в Microsoft Excel

  49. Після цього при спробі внесення будь-яких змін в осередок виведення результату дії будуть блокуватися, про що повідомляється в постає діалоговому вікні.

Повідомлення про неможливість вносити зміни в клітинку в Microsoft Excel

Таким чином, ми створили повноцінний калькулятор для конвертації величини маси в різні одиниці виміру.

Крім того, в окремій статті розповідається про створення ще одного виду вузькопрофільного калькулятора в Ексель для розрахунку платежів по кредитах.

урок: Розрахунок аннуитетного платежу в Ексель

Спосіб 3: включення вбудованого калькулятора Excel

Крім того, в Ексель є власний вбудований універсальний калькулятор. Правда, за замовчуванням кнопка його запуску відсутня на стрічці або на панелі швидкого доступу. Розглянемо, як активувати її.

  1. Після запуску програми Excel переміщаємося у вкладку «Файл».
  2. Перехід у вкладку Файл у Microsoft Excel

  3. Далі у вікні, переходимо в розділ «Параметри».
  4. Переміщення в вікно параметрів в Microsoft Excel

  5. Після запуску віконця параметрів Excel переміщаємося в підрозділ «Панель швидкого доступу».
  6. Перехід до підрозділу Панель швидкого доступу вікна параметраметров в Microsoft Excel

  7. Перед нами відкривається вікно, права частина якого розділена на дві області. У правій її частині розташовані інструменти, які вже додані на панель швидкого доступу. У лівій представлений весь набір інструментів, який доступний в Excel, включаючи відсутні на стрічці.

    Над лівою областю в поле «Вибрати команди» з переліку вибираємо пункт «Команди не на стрічці». После этого в списке инструментов левой области ищем наименование «Калькулятор» . Найти будет просто, так как все названия расположены в алфавитном порядке. Затем производим выделения данного наименования.

    Над правой областью находится поле «Настройка панели быстрого доступа» . Оно имеет два параметра:

    • Для всех документов;
    • Для данной книги.

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

    После того, как все настройки совершены и наименование «Калькулятор» выделено, жмем на кнопку «Добавить» , которая расположена между правой и левой областью.

  8. Добавление калькулятора на панель быстрого доступа в Microsoft Excel

  9. После того, как наименование «Калькулятор» отобразилось в правой области окна, жмем на кнопку «OK» внизу.
  10. Закртие окна параметров в Microsoft Excel

  11. После этого окно параметров Excel будет закрыто. Чтобы запустить калькулятор, нужно кликнуть на одноименный значок, который теперь располагается на панели быстрого доступа.
  12. Запуск калькулятора в Microsoft Excel

  13. После этого инструмент «Калькулятор» будет запущен. Функционирует он, как обычный физический аналог, только на кнопки нужно нажимать курсором мышки, её левой кнопкой.

Калькулятор запущен в Microsoft Excel

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