Автоматизация расчетов в экселе если

Обновлено: 06.07.2024

закрепить приобретенные навыки по заполнению, форматированию и печати таблиц.

Продолжить работу над воспитанием терпеливого и внимательного отношения к выполняемой работе;

Продолжить работу по развитию любознательности, интереса к предмету «информатика» и учению в целом;

Продолжить работу над формированием общеучебных умений: слушать объяснение учителя, самостоятельно выполнять задания учителя;

Развивать логическое мышление, способность к анализу;

База знаний и место урока

К этому уроку учащиеся знают:

Что такое «электронные таблицы», их виды, функции; назначение. Интерфейс табличного процессора Microsoft Excel;

Как использовать Мастер функций, изменять формат ячейки, решать задачи;

Арифметические действия в ЭТ, автосуммирование числовых данных, возможности использования встроенных функций;

Относительные и абсолютные ссылки;

Правила копирования формул;

Умеют составлять и заполнять таблицы;

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

Умеют изменять формат ячейки, размер, заливку, объединять ячейки;

Умеют копировать формулы;

Умеют использовать относительные и абсолютные ссылки;

Тип урока: Комбинированный урок;

Организационный этап – 2 мин;

Постановка темы и целей урока – 2 мин

Фронтальный опрос – 10 мин

Объяснение нового материала – 10 мин

Решение самостоятельной работы – 25 мин

Подведение итогов. Выставление оценок (индивидуально)

Материальная база, оборудование урока

Компьютер. Мультимедийный проектор. ОС Windows 7. MS Office 2010 (табличный процессор Excel), карточки с заданиями. Карточка «Штатное расписание хозрасчетной больницы»;

I. Организационный момент: приветствие.

Проверка готовности учащихся к уроку, отметка отсутствующих, объявление темы и цели урока.

При выполнении работы должны соблюдаться правила ТБ при работе за ПК и правила поведения в компьютерном классе.

II. Постановка темы и целей урока.

III. Фронтальный опрос. Вопросы:

Каково назначение программы Excel?

Что такое адрес электронной ячейки, из чего он состоит?

Как произвести ввод данных в ячейку?

Какие форматы данных вы знаете?

Как можно изменить высоту и ширину ячейки в электронной таблице?

Какие способы выравнивания текста в ячейке вы знаете?

Каким образом можно объединить ячейки электронной таблицы?

Как перенести текст в ячейке по словам?

Каким образом можно произвести обрамление таблицы в Excel?

Как очистить ячейки таблицы от данных?

Как произвести копирование содержимого ячейки?

IV. Объяснение нового материала.

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

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

Построим модель решения этой задачи.

Поясним, что является исходными данными. Казалось бы, ничего не дано, кроме общего фонда заработной платы. Однако заведующему больницей известно больше: он знает, что для нормальной работы больницы нужно 5—7 санитарок, 8—10 медсестер, 10—12 врачей, 1 заведующий аптекой, 3 заведующих отделениями, 1 главный врач, 1 заведующий хозяйством, 1 заведующий больницей. На некоторых должностях число людей может меняться. Например, зная, что найти санитарок трудно, руководитель может принять решение о сокращении числа санитарок, чтобы увеличить оклад каждой из них.

Итак, заведующий принимает следующую модель задачи. За основу берется оклад санитарки, а все остальные вычисляются исходя из него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада санитарки: А´С+В, где С — оклад санитарки; А и В—- коэффициенты, которые для каждой должности определяются решением совета трудового коллектива.

Допустим, совет решил, что

медсестра должна получать в 1,5 раза больше санитарки (А=1,5, В=0);

врач — в 3 раза больше санитарки (В=0, А=3);

заведующий отделением — на $30 больше, чем врач (А=3, В=30);

заведующий аптекой — в 2 раза больше санитарки (А=2, В=0);

заведующий хозяйством —на $40 больше медсестры (А=1.5, В=40);

главный врач — в 4 раза больше санитарки (А=4, В=0);

заведующий больницей — на $20 больше главного врача (А=4, В=20).

Задав количество человек на каждой должности, можно составить уравнение: Nl ´( Al ´ C + BI )+ N 2´( A 2´ C + B 2)+. + N 8´( A 8´ C + B 8)=10000,

где N 1 — количество санитарок; N 2 — количество медсестер и т. д.

В этом уравнении нам известны А1. А8 и В1. В8, а неизвестны С и NI . N 8.

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

Проделать такую работу вручную трудно. Но вам поможет электронная таблица.

Ход работы: Выполнение задания карточки (Приложение1)

1. Отведите для каждой должности одну строку и запишите названия должностей в столбец А .

2. В столбцах В и С укажите соответственно коэффициенты А и В.

3. В ячейку Н5 занесите заработную плату санитарки (в формате с фиксированной точкой и двумя знаками после нее).

4. В столбце D вычислите заработную плату для каждой должности по формуле А*С+В.

Обратите внимание! Этот столбец должен заполняться формулами с использованием абсолютной ссылки на ячейку Н5, в которой указана зарплата санитарки. Изменение содержимого этой ячейки должно приводить к изменению содержимого всего столбца D и пересчету всей таблицы.

5. В столбце Е укажите количество сотрудников на соответствующих должностях в соответствии со штатным расписанием.

6. В столбце F вычислите заработную плату всех рабочих данной должности. Тогда сумма элементов столбца F даст суммарный фонд заработной платы.

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

7. Если расчетный фонд заработной платы не равен заданному, то внесите изменения в зарплату санитарки или меняйте количество сотрудников в пределах штатного расписания, затем осуществляйте перерасчет - до тех пор, пока сумма не будет равна заданному фонду.

8. Сохраните таблицу в личном каталоге под именем work _3. xls .

9. После получения удовлетворительного результата отредактируйте таблицу.

9.1. Оставьте видимыми столбцы А, D, Е, F.

Столбцы В, С можно скрыть, воспользовавшись пунктом меню Формат. столбец, Скрыть.

9.2. Дайте заголовок таблице “Штатное расписание хозрасчетной больницы” и подзаголовок “зав. больницей Петров И. С.”.

9.3. Оформите таблицу, используя автоформатирование. Для этого:

выделите всю таблицу, включая заголовки;

выберите пункт меню Формат, Автоформат;

выберите удовлетворяющий вас формат.

10. Сохраните отредактированную таблицу предъявите учителю: файл work _3. xls на экране;

V Итоги: Выясняет степень усвоения нового материала (с каждым индивидуально).

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

Вам уже любопытно, что такое макрос, и как он работает? Тогда смелей вперёд – далее мы шаг за шагом проделаем весь процесс создания макроса вместе с Вами.

Что такое Макрос?

Макрос в Microsoft Office (да, этот функционал работает одинаково во многих приложениях пакета Microsoft Office) – это программный код на языке программирования Visual Basic for Applications (VBA), сохранённый внутри документа. Чтобы было понятнее, документ Microsoft Office можно сравнить со страницей HTML, тогда макрос – это аналог Javascript. То, что Javascript умеет делать с данными в формате HTML, находящимися на web-странице, очень похоже на то, что макрос может делать с данными в документе Microsoft Office.

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

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

Создание макроса – практический пример

Для примера возьмём самый обычный файл CSV. Это простая таблица 10х20, заполненная числами от 0 до 100 с заголовками для столбцов и строк. Наша задача превратить этот набор данных в презентабельно отформатированную таблицу и сформировать итоги в каждой строке.

Как уже было сказано, макрос – это код, написанный на языке программирования VBA. Но в Excel Вы можете создать программу, не написав и строчки кода, что мы и сделаем прямо сейчас.

Чтобы создать макрос, откройте View (Вид) > Macros (Макросы) > Record Macro (Запись макроса…)

Дайте своему макросу имя (без пробелов) и нажмите ОК.

Начиная с этого момента, ВСЕ Ваши действия с документом записываются: изменения ячеек, пролистывание таблицы, даже изменение размера окна.

Excel сигнализирует о том, что включен режим записи макроса в двух местах. Во-первых, в меню Macros (Макросы) – вместо строки Record Macro (Запись макроса…) появилась строка Stop Recording (Остановить запись).

Во-вторых, в нижнем левом углу окна Excel. Иконка Стоп (маленький квадратик) указывает на то, что включен режим записи макроса. Нажатие на неё остановит запись. И наоборот, когда режим записи не включен, в этом месте находится иконка для включения записи макроса. Нажатие на неё даст тот же результат, что и включение записи через меню.

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

Далее, введите в ячейки формулы в соответствии с названиями заголовков (даны варианты формул для англоязычной и русифицированной версии Excel, адреса ячеек – всегда латинские буквы и цифры):

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

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

Далее, мы подведем итоги для всей таблицы, для этого делаем ещё несколько математических действий:

Теперь, когда с вычислениями закончили, займёмся форматированием. Для начала для всех ячеек зададим одинаковый формат отображения данных. Выделите все ячейки на листе, для этого воспользуйтесь комбинацией клавиш Ctrl+A, либо щелкните по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов. Затем нажмите Comma Style (Формат с разделителями) на вкладке Home (Главная).

Далее, изменим внешний вид заголовков столбцов и строк:

Жирное начертание шрифта.
Выравнивание по центру.
Заливка цветом.

И, наконец, настроим формат итоговых значений.

Вот так это должно выглядеть в итоге:

Если Вас все устраивает, остановите запись макроса.

Поздравляем! Вы только что самостоятельно записали свой первый макрос в Excel.

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

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

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

Важный момент! Если Вы сохраните файл с расширением XLTX, то макрос в нём работать не будет. Кстати, можно сохранить книгу как шаблон Excel 97-2003, который имеет формат XLT, он тоже поддерживает макросы.

Когда шаблон сохранён, можно спокойно закрыть Excel.

Выполнение макроса в Excel

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

Макрос может нанести вред.
Ещё раз прочти предыдущий пункт.

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

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

Следующим шагом, мы импортируем последний обновлённый набор данных из файла CSV (на основе такого файла мы создавали наш макрос).

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

Когда импорт будет закончен, зайдите в меню Macros (Макросы) на вкладке View (Вид) и выберите команду View Macros (Макросы).

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

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

Заглянем под капот: Как работает макрос?

Чтобы увидеть этот программный код, нужно в меню Macros (Макросы) на вкладке View (Вид) кликнуть View Macros (Макросы) и в открывшемся диалоговом окне нажать Edit (Изменить).

Откроется окно Visual Basic for Applications, в котором мы увидим программный код записанного нами макроса. Да, Вы правильно поняли, здесь этот код можно изменить и даже создать новый макрос. Те действия, которые мы совершали с таблицей в этом уроке, вполне можно записать с помощью автоматической записи макроса в Excel. Но более сложные макросы, с тонко настроенной последовательностью и логикой действий требуют программирования вручную.

Добавим ещё один шаг к нашей задаче…

Представьте, что наш исходный файл с данными data.csv создаётся автоматически каким-то процессом и сохраняется на диске всегда в одном и том же месте. Например, C:\Data\data.csv – путь к файлу с обновляемыми данными. Процесс открытия этого файла и импорта данных из него тоже можно записать в макрос:

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

Если есть желание заняться программированием, Вы можете объединить действия этих двух макросов в один – просто скопировав код из LoadData в начало кода FormatData.

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

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

Автоматическое вычисление формул в Excel 2010

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

Обратите внимание, что есть другой способ изменить параметры расчета в Excel 2010, и этот параметр можно найти в окне параметров Excel. Если вы предпочитаете использовать этот метод, вы можете перейти к следующему разделу.

Шаг 1. Откройте файл в Microsoft Excel 2010.

Шаг 2: Нажмите «Формулы» вкладка в верхней части окна.

Шаг 3: Нажмите «Варианты расчета» кнопку справа от навигационной ленты, затем нажмите кнопку «автоматическая» вариант.

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

Изменить параметры расчета формулы Excel 2010 в меню параметров Excel

Шаг 1. Откройте Excel 2010.

Шаг 2: Нажмите «файл» вкладка в верхнем левом углу окна.

Шаг 3: Нажмите «Параметры» в столбце с левой стороны окна. Это собирается открыть новый «Параметры Excel» окно.

Шаг 4: Нажмите «Формулы» Вкладка в левой части «Параметры Excel» окно.

Шаг 5: Нажмите на круг слева от «автоматическая» под «Расчет рабочей книги»,

Шаг 6: Нажмите «Хорошо» Кнопка внизу окна.

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

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

Как сделать расчеты в Excel

Произвести расчеты в Excel легко. Вот как:

Вместо того, чтобы вводить цифры непосредственно в формулу расчета, вы можете поместить их в отдельные ячейки, а затем сослаться на эти ячейки в своей формуле, например, =A1+A2+A3

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

Суммирует числа в ячейках A1иA2

Вычитает число A2 из числа в A1.

Умножает числа из A1 и A2.

Делит число A1 на число A2.

Находит 10% от числа A1.

Возведение в степень

Возвращает число А2 в 3 степени.

Находит квадратный корень из числа A1.

^(1/n), где n – корень, который нужно найти

Находит кубический корень из числа А1.

Результаты приведенных выше формул расчета в Excel могут выглядеть примерно так:

Расчеты в Excel – Формулы расчетов в Excel

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

Символ пробела (" ") используется для разделения слов:

Расчеты в Excel – Объединение значений из нескольких ячеек

Вы также можете сравнивать ячейки с помощью логических операторов, таких как «больше» (>), «меньше» (<), «больше или равно» (> =) и «меньше или равно» (<= ). Результатом сравнения являются логические значения ИСТИНА и ЛОЖЬ:

Расчеты в Excel – Сравнение ячеек с использованием логических операторов

Порядок выполнения расчетов в Excel

Когда вы выполняете два или более расчета в одной формуле, Microsoft Excel вычисляет формулу слева направо в соответствии с порядком операций, показанных в этой таблице:

Отрицание, т.е. изменение знака числа, например, -5, или -A1

Возведение в степень (^)

Умножение (*) и деление (/), в зависимости от того, что наступит раньше

Сложение (+) и вычитание (-), в зависимости от того, что наступит раньше

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

Как изменить порядок расчета в Excel

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

Как вы можете видеть на изображении ниже, тот же расчет с круглыми скобками и без них дает разные результаты:

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