Урок #7. Расчеты в Excel
Как посчитать в программе Microsoft Excel
Вычисление множественных результатов с помощью таблицы данных
Основное назначение Excel — выполнение расчетов с данными. Обработка данных происходит в ячейках содержащих формулы. Правила ввода простейших формул вы уже изучили в начале раздела. В данном подразделе мы рассмотрим общие принципы создания формул любой сложности и изучим примеры типичных расчетов в Excel. В формуле могут находиться:. В Excel имеется большое количество функций, с помощью которых можно проводить вычисления и другие действия, относящиеся к самым разнообразным областям знаний. Аргументами функции могут быть числа, ссылки на ячейки или диапазоны ячеек, а также другие встроенные функции они называются вложенными. А5 — суммирование значений из диапазона ячеек А1: Это пример использования встроенной функции. Здесь СУММ — имя функции, А1: C7 — вычисление произведения матриц B1: Как видно, данная функция имеет два аргумента, которые являются массивами данных из выделенных диапазонов. Если функция имеет несколько аргументов, они отделяются друг от друга точкой с запятой. В качестве аргументов функций вы можете использовать ссылки на ячейки и диапазоны на текущем листе и на других листах. Разумеется, синтаксис всех встроенных функций Excel запомнить невозможно, да и делать это не нужно, поскольку в повседневной практике для решения наиболее часто встречающихся задач вы будете использовать всего несколько встроенных функций. Опции вставки в документ встроенных функций находятся на вкладке Формулы в группе Библиотека функций. Функции распределены по категориям в зависимости от того, для решения каких типов задач они предназначены. Назначение той или иной функции можно прочесть на всплывающей подсказке, которая появляется при наведении указателя мыши на имя функции с меню рис. Если вы хотите просмотреть полный список встроенных функций Excel, нажмите кнопку Вставить функцию, которая находится в строке формул. В открывшемся окне Мастера функций выберите в раскрывающемся списке Категория пункт Полный алфавитный перечень и щелчком выделите имя функции, чтобы ниже прочитать о выполняемых ею действиях. Название встроенной функции можно ввести с клавиатуры что крайне нежелательно ввиду высокой вероятности ошибки , вставить из соответствующего меню кнопок, расположенных в группе Библиотека функций на вкладке Формулы или же из окна Мастера функций. О двух последних вариантах будет упомянуто ниже. Часто применяемые на практике функции вынесены в меню кнопки , которая находится в группе Редактирование на вкладке Главная. Рассмотрим задачи, связанные с их использованием. Простейшие расчеты Функция суммирования данных является самой востребованной, именно поэтому задействовать ее в Excel проще всего. Если данные расположены в одном столбце или строке, выделите их и нажмите кнопку. Результат сложения тут же отобразится внизу в случае столбца или справа в случае строки ряда. Если необходимо просуммировать значения из нескольких строк или столбцов не важно, являются они смежными или нет , выделите ряды и вновь воспользуйтесь данной кнопкой. Суммы тут же отобразятся в следующей за рядом ячейке. Для суммирования ячеек, находящихся в прямоугольной области или в разных частях документа, выполните следующие действия. Выделите щелчком ячейку, в которой нужно отобразить результат суммирования. При этом в ячейку будет помещена функция суммирования, а в ее скобках будет мигать курсор, указывающий на то, что нужно ввести аргументы. Выделите нужный диапазон ячеек при необходимости — несколько диапазонов, удерживая нажатой клавишу. При этом он будет охвачен бегущей рамкой, а скобках формулы появятся ссылки на диапазоны ячеек рис. Нажмите для получения результата. Даже после того, как ввод формулы будет завершен, вы всегда сможете отредактировать ее, изменив или добавив аргументы или функции. Так, чтобы изменить диапазон ячеек в только что рассмотренном примере, действуйте следующим образом. Выполните двойной щелчок на ячейке, содержащей формулу. При этом диапазон, принимающий участие в расчетах, будет охвачен синей рамкой с маркерами в углах. Подведите указатель мыши к нужному углу и, когда он примет вид двунаправленной стрелки, протащите границы рамки так, чтобы захватить новые ячейки или, наоборот, исключить старые. При этом адрес диапазона в скобках формулы будет изменяться автоматически. Нажмите для подсчета нового результата. Щелкнув на стрелке кнопки , вы раскроете список команд, вызывающих функции, которые можно задействовать так же быстро, как и функцию суммирования. Схема действий при их использовании не отличается от последовательности шагов для функции суммирования. Ниже приводится краткое описание функций, вызываемых командами кнопки. Результат работы некоторых из перечисленных функций можно видеть, не обращаясь непосредственно к самим функциям. Выделите интересующий вас диапазон и посмотрите вниз на строку состояния Excel. Слева от регулятора масштаба появятся значения суммы, количества ячеек в диапазоне и среднего арифметического рис. Комплексные расчеты Вы изучили примеры простейших расчетов в Excel. Теперь попробуйте разобраться с комплексной задачей, требующей комбинации действий, рассмотренных нами ранее. Выбрать оптимальный тарифный план при подключении к сети сотовой связи, если в месяц планируется 2,5 часа разговоров внутри сети и 0,5 часа разговоров с абонентами городской сети и других сотовых операторов. Цены на услуги представлены в таблице на рис. В первую очередь необходимо создать таблицу и внести в нее расценки на услуги сотовых операторов так, как это сделано на рис. Для подсчета конечной суммы с учетом НДС в конце таблицы следует обозначить отдельный столбец. Выделите ячейку А1 и наберите в ней название работы. В ячейках следующей строки создайте заголовки таблицы. Воспользуйтесь кнопкой Перенос текста из группы Выравнивание, чтобы отрегулировать высоту строки. Ширину столбцов подберите вручную перетаскиванием границ их заголовков. Далее заполните сетку таблицы значениями, представленными на рис. Поскольку тарифы представлены в расчете за минуту связи, планируемое время разговоров также необходимо ввести в минутах в столбцах Звонки внутри сети и Звонки в другие сети. Заполнять оба столбца вручную не нужно. Достаточно ввести значение в первой ячейке, а затем скопировать его на остальные с помощью автозаполнения. Внизу столбца создайте заголовок Оптимум, ниже которого поместите функцию расчета минимального значения ряда, чтобы определить наименьшую стоимость. Суммарные затраты будут складываться из абонентской платы, стоимости разговоров внутри сети и с абонентами других сетей, а также из налога на добавленную стоимость. Это нужно представить в виде формулы. Начнем расчеты с первого тарифного плана Минутка. К содержимому ячейки В3 абонплата нужно прибавить произведение ячеек С3 и Е3 общая стоимость разговоров внутри сети и произведение ячеек D3 и F3 общая стоимость разговоров с абонентами других сетей. Так будет подсчитана сумма затрат без учета НДС. Это результат и должен отобразиться в ячейках последнего столбца. При этом будет вставлена функция суммирования, автоматически определен диапазон ячеек, а его адрес будет выделен в скобках функции. Поскольку данный диапазон нам не подходит, нажмите , чтобы удалить ссылку из скобок. Отделите новый аргумент точкой с запятой. Так мы создали часть формулы, которая отвечает за подсчет суммарных затрат без учета налогов. Она должна выглядеть следующим образом: Если вы где-то допустили ошибку, установите курсор возле нее щелчком мыши или с помощью клавиш движения и внесите исправления. Далее нужно прибавить налоговую часть формулы. Щелчком установите курсор в конец формулы, выполните правый щелчок и обратитесь к команде Вставить. Нажмите , чтобы увидеть итоговую сумму. Набирать вручную формулу в остальных ячейках не нужно. Воспользуйтесь автозаполнением — выделите ячейку с формулой и протяните рамку за маркер вниз. В результате формула будет скопирована в нижние ячейки, а ссылки в ней автоматически заменятся на адреса ячеек из той же строки. Обратите внимание, автоматически посчиталась и минимальная сумма затрат. В нашем случае она соответствует тарифному плану Будь на связи. Попробуйте изменить начальные условия задачи и задать другое количество планируемых минут разговоров внутри сети и с абонентами других операторов. Пересчет суммарных затрат будет выполнен мгновенно, и вполне вероятно, что для новых значений оптимальным окажется другой тарифный план. В завершении работы отформатируйте таблицу, чтобы она имела презентабельный вид. В первую очередь стоит перевести ячейки с тарифами в денежный формат. Выделенные ячейки будут переведены в денежный формат, но поскольку в Excel по умолчанию отображается два десятичных знака, в конце каждого числа появится незначащий ноль. Чтобы скрыть его, выделите диапазоны и щелкните на кнопке Уменьшить разрядность. Для некоторых ячеек может понадобиться скрыть и второй ноль после запятой. Сделать это можно аналогичным образом. Теперь примените к таблице один из встроенных стилей. Для этого выделите сам прямоугольник таблицы, не включая название задания и ячейки подсчета оптимума, щелкните на кнопке Форматировать как таблицу в группе Стили и выберите понравившийся эскиз. В появившемся окошке нужно установить флажок Таблица с заголовками и нажать ОК. При необходимости уменьшите ширину столбцов перетаскиванием границ заголовков. В заключение отформатируйте название задания и ячейку с конечным результатом. Выделите ячейки, на которые по длине распространяется название, и щелкните на стрелке кнопки и задействуйте команду Объединить ячейки. Затем задайте для ячейки с названием определенные параметры шрифта и цвет фона с помощью инструментов группы Шрифт или примените к ней один из стилей, воспользовавшись кнопкой Стили ячеек в группе Стили. После выполнения всех операций форматирования таблица с расчетом должна принять вид, подобный тому, что представлен на рис. Относительная и абсолютная адресация Адреса ячеек и диапазонов в Excel могут быть относительными и абсолютными. До сих пор мы говорили об относительных ссылках на ячейки и диапазоны, которые состоят только из номера строки и буквы столбца, например В2 или D4: Преимущество относительной адресации состоит в том, что при копировании ячеек и использовании автозаполнения ссылки в скопированных формулах меняются автоматически относятся к ячейкам текущей, а не исходной строки , поэтому нет необходимости набирать вручную каждую формулу. Однако на практике встречаются ситуации, когда адрес ячейки или диапазона необходимо зафиксировать, чтобы он не изменялся при копировании или автозаполнении ячеек. Это называется смешанной адресацией. Чтобы быстро изменить адресацию в готовой формуле, выполните по ней двойной щелчок, установите курсор на нужную ссылку и последовательно нажимайте клавишу для изменения типа адреса. Рассмотрим использование абсолютной адресации на конкретном примере. Рассчитать конечную стоимость товара для оптового покупателя в зависимости от оговоренного размера скидки. Вы являетесь оптовым поставщиком и для некоторых групп товаров, залежавшихся на складке, готовы предоставить скидку. Размер скидки варьируется в зависимости от того, насколько вы удачно договоритесь с покупателем. После переговоров вы должны предоставить ему прайс-лист, в котором цены будут скорректированы с учетом скидки, и по ним можно будет проводить дальнейшие расчеты. В первую очередь вам необходимо сформировать сам прайс-лист со стандартными ценами, в одной из ячеек набрать размер скидки в процентах его впоследствии можно будет варьировать , а также обозначить столбец, в ячейках которого будут находиться формулы, вычисляющие стоимость товара с учетом скидки. Пример такого прайс-листа приведен на рис. Описание подробностей оформления опустим, поскольку вы уже приобрели в этом вопросе определенные навыки. Перейдем к сути проблемы. Чтобы рассчитать цену с учетом скидки, необходимо от текущей цены отнять процент скидки. В нашем примере он равняется десяти. На первый взгляд решение задачи очень похоже на предыдущее, где прибавлялась процентная ставка НДС. Останется только распространить формулу на остальные ячейки столбца с помощью автозаполнения. Попробуйте выполнить описанные действия, и в конечном итоге вы обнаружите, что в вычислениях произошла ошибка. А заключается она в следующем. При использовании автозаполнения ссылки в ячейках автоматически изменяются по мере копирования формул. Чтобы этого не происходило, ее адрес необходимо зафиксировать — сделать абсолютным. Для этого выполните на первой ячейке столбца скидки двойной щелчок С4 , поместите курсор на адрес ячейки, где находится величина скидки в нашем случае это ячейка С1 и нажмите клавишу. В результате конечная формула будет выглядеть так: Теперь можно повторить процедуру автозаполнения, чтобы получить корректный результат. Щелкните на любой ячейке столбца скидок, чтобы убедиться в неизменности абсолютной ссылки. При изменении величины скидки весь ряд будет автоматически пересчитан. Щелкните на ячейке С1 где указан размер скидки , слева в строке формул наберите Скидка. Осталось распространить формулу на все ячейки столбца с помощью автозаполнения. В описанных выше примерах мы не упоминали об использовании кнопок группы Библиотека функций на вкладке Формулы и о Мастере функций для вставки встроенных функций в формулу. Ошибки в формулах При работе с формулами в Excel нередко возникают ошибки, связанные не только с правильностью написания формулы, но и с корректным определением адресов ячеек и диапазонов с данными. Проведем краткий обзор способов выявления и устранения ошибок. Текст сообщения будет различным в зависимости от того, удалось Excel определить источник ошибки или нет. Когда система не может определить источник ошибки, появляется сообщение, представленное на рис. В этом случае вернитесь к ячейке с формулой, перепроверьте ее и исправьте ошибку. Иногда системе удается определить, какие изменения следует внести в формулу, чтобы она стала синтаксически правильной. При этом появляется сообщение, подобное тому, что представлено на рис. Проверьте, удовлетворяет ли предлагаемое системой исправление условиям расчета, и в зависимости от сделанного вывода, согласитесь с автоматическим исправлением, нажав Да, или же исправьте ошибку вручную в другом участке формулы. Иногда возникает ситуация, когда после введения формулы в ячейке вместо результата появляется текстовое сообщение об ошибке. Это связано с тем, что при проведении вычислений система столкнулась с каким-либо противоречием. Список наиболее часто встречающихся сообщений в ячейках приведен ниже. Самая опасная ошибка — правильность ввода адресов ячеек и диапазонов в формулу. Система определяет только математические и синтаксические ошибки, но предугадать, данные каких именно ячеек должны присутствовать в формуле, не может. За эти должны внимательно следить вы. Проще всего проверить правильность указания адресов ячеек и диапазонов в формуле можно следующим образом. Выполните двойной щелчок на ячейке содержащей формулу. При этом ячейки и диапазоны, входящие в нее, будут выделены в таблице рамками с маркерами, цвет которых соответствует цвету ссылки в формуле рис. Если был указан неправильный адрес, перетащите рамку на нужную ячейку или измените ее размеры протаскиванием маркера при увеличении или уменьшении диапазона. У Вас есть материал пишите нам.
Попробуйте Microsoft Edge быстрый и безопасный браузер, разработанный для Windows 10 Нет Начало работы. Данная статья переведена с помощью машинного перевода, см. Используйте английский вариант этой статьи, который находится здесь , в качестве справочного материала. Таблица данных представляет собой диапазон ячеек, значения которых можно изменять для получения различных решений проблемы. Например, можно использовать функцию ПЛТ с разными суммами кредитов и процентными ставками для расчета возможного кредита на жилье или автомобиль. Эксперименты с изменяемыми значениями с целью получения разных результатов — часть дисциплины, известной как анализ данных. Например, таблицу данных можно использовать для варьирования процентной ставки и длительности срока, которые используются в условиях займов, для определения возможных сумм ежемесячного платежа. Сценарии и таблицы данных принимают наборы входных значений и определения возможных результатов. Подбор параметров отличается от сценарии и таблицы данных в том, что он принимает результата и определяет возможные входные значения, которые, результат будет выглядеть. Как и сценарии, таблицы данных позволяют изучить набор возможных результатов. В отличие от сценариев, в таблицах данных все результаты отображаются в одной таблице на одном листе. Применение таблиц данных облегчает и ускоряет изучение возможных вариантов. Поскольку внимание сосредоточено только на одной или двух переменных, результаты легко воспринимаются; ими также просто обмениваться в табличной форме. Таблица данных не может принимать более двух переменных. Если необходимо проанализировать больше переменных, вместо таблиц данных лучше использовать сценарии. Сценарий поддерживает только 32 различных значения, но количество сценариев может быть любым. Дополнительные сведения о анализ "что если": Общие сведения для анализа "что если". В зависимости от числа переменных и формул, которые нужно протестировать, можно создать таблицу данных с одной или двумя переменными. Например, можно использовать таблицу данных с одной переменной для просмотра различных процентной ставки влияют на ежемесячный платеж по ипотеке с помощью функции ПЛТ. Ввод значений переменных в один столбец или строку, а результаты, отображаются в смежных столбцах или строках. Например, таблицу данных с двумя переменными можно использовать для оценки влияния различных сочетаний процентной ставки и срока кредитования на размер ежемесячной выплаты по закладной. Ускорение вычислений для листов, содержащих таблицы данных, вы можете изменить параметры расчета для автоматического пересчета листа, но не таблицы данных. В разделе ускорение вычислений, содержащих таблицы данных на листе. Входные значения для таблицы данных с одной переменной перечисляются в столбце таблица данных с ориентацией по столбцам или в строке таблица данных с ориентацией по строкам. Формулы, которые используются в таблице данных с одной переменной, должны ссылаться только на одну ячейка ввода. Введите список значений, которые нужно подставлять в ячейку ввода, в один столбец или в одну строку. Оставьте несколько пустых строк и столбцов по обе стороны от значений. Если таблица данных ориентирована по столбцам переменной значения расположены в столбце , введите формулу в ячейке на одну строку выше и на одну ячейку вправо от столбца значений. Таблица данных с одной переменной, рисунке в разделе Обзор ориентирована по столбцам, а содержащейся в ячейке D2 формулу. Если требуется проверить влияние различных значений в других формулах введите другие формулы в ячейках справа от первой формулы. Если таблица данных ориентирована по строкам значения переменной содержатся в строке , введите формулу в ячейку на один столбец левее первого значения и на одну ячейку ниже строки значений. Если необходимо изучить влияние различных значений на другие формулы, введите дополнительные формулы в ячейки под первой формулой. Выделите диапазон ячеек, содержащих формулы и значения, которые нужно заменить. Если таблица данных ориентирована по столбцу, введите в поле Подставлять значения по строкам ссылка на ячейку на ячейку ввода. В примере показано на рисунке первую ячейку ввода — B3. Если таблица данных ориентирована по строкам, введите ссылку на ячейку ввода в поле Подставлять значения по столбцам в. После создания таблицы данных формат ячеек результатов можно изменить. На рисунке ячейки результатов имеют денежный формат. Формулы, которые используются в таблице данных с одной переменной, должны ссылаться только на одну ячейку ввода. Если таблица данных ориентирована по столбцам значения переменной содержатся в столбце , введите новую формулу в пустую ячейку справа от существующей формулы в верхней строке таблицы данных. Если таблица данных ориентирована по строкам значения переменной содержатся в строке , введите новую формулу в пустую ячейку под существующей формулой в первом столбце таблицы данных. Если таблица данных ориентирована по столбцам, введите ссылку на ячейку ввода в поле Подставлять значения по строкам в. В таблице данных с двумя переменными используется формула, содержащая два списка входных значений. Формула должна ссылаться на две разные ячейки ввода. Выделите диапазон ячеек с формулой C2 строку и столбец значений C3: E2 , а также ячейки, в которые нужно поместить вычисленные значения D3: В поле Подставлять значения по столбцам введите ссылку на ячейку ввода для входных значений в строке. Ячейку B4 введите в поле Подставлять значения по столбцам. В поле Подставлять значения по строкам введите ссылку на ячейку ввода для входных значений в столбце. В поле Подставлять значения по строкам , введите B3. В Excel нажмите Кнопку Microsoft Office , выберите пункт Параметры Excel и выберите категорию формулы. В разделе Параметры вычислений в группе Вычисление выберите вариант Автоматически, кроме таблиц данных. Можно также на вкладке Формулы в группе Вычисление щелкнуть стрелку рядом с кнопкой Параметры вычислений и выбрать пункт Автоматически, кроме таблиц данных. Если выбран этот параметр вычисления, таблицы данных не пересчитываются при пересчете остальной части книги. Чтобы пересчитать таблицу данных вручную, выделите ее формулы и нажмите клавишу F9. Для определенных целей и в случае больших наборов переменных данных можно использовать несколько других средств Excel для выполнения анализа "что если". Если известно, что требуется получить его результат вы хотите получить при вычислении формулы, но не уверены, что входные значения, формулы, используйте средство подбора параметров. Читайте в статье Использование подбора для поиска путем изменения входного значения требуемого результата. Надстройка "Поиск решения" Excel можно использовать для поиска оптимального значения на основе числа переменных. Подбираются значения в ячейках переменной решения для удовлетворения ограничения на ограничение ячеек и получения нужного результата для целевой ячейки. Ознакомьтесь со статьей определить и решение задачи с помощью поиска решения. Отказ от ответственности относительно машинного перевода. Данная статья была переведена с помощью компьютерной системы без участия человека. Microsoft предлагает эти машинные переводы, чтобы помочь пользователям, которые не знают английского языка, ознакомиться с материалами о продуктах, услугах и технологиях Microsoft. Поскольку статья была переведена с использованием машинного перевода, она может содержать лексические,синтаксические и грамматические ошибки. Поиск в справке Office Нет результатов. Вычисление множественных результатов с помощью таблицы данных Применяется к: Первоочередный доступ к новым возможностям. Были ли сведения полезными? Что мы могли бы улучшить? Спасибо за ваш отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office. Обращение в службу поддержки. Обучение Windows Office Skype Outlook OneDrive MSN. Устройства Microsoft Surface Xbox Устройства на Windows Microsoft Lumia. Магазин Microsoft Адресная книга Заказы Возврат товаров Продажа и поддержка. Загрузки Центр загрузок Microsoft Загрузки для Windows Windows 10 Apps Office Apps Microsoft Lumia Apps Internet Explorer. Ценности Правила обеспечения конфиденциальности корпорации Майкрософт Образование. Компания Карьера в Microsoft Microsoft Новости и публикации Продукты Политика по персональным данным.
Умные таблицы Excel 2007-2013
Fighter 35 инструкция
Лыжный спорт описание
Гороскоп по зодиакам характеристика
Литера в основной надписи чертежа
Тк увольнение в порядке перевода
Пример приказао дисциплинарном взыскании