Как в программе excel определять объем

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

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

Как рассчитать объем и площадь в Excel

В ячейке A1 запишем формулу вычисления объема параллелепипеда: a = 6 см; b = 8 см; c = 12 см.

В ячейке A2 запишем формулу вычисления площади круга: r = 25 см.

В ячейке A3 формула содержит безаргументную функцию ПИ(), которая содержит в себе полное число ПИ (а не 3.14). Поэтому значения ячеек A2 и A3 немного отличаются.

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

Допустим нам нужно в Excel вычислить формулу:

Чтобы получить результат вычисления ее нужно просто записать в одну строку: =(125*(3+4,5)^2)/(12-3,7).

Данная формула содержит 5 арифметических действий: суммирование, вычитание, умножение, деление и поднесение к степени (^). Если мы записываем все в одно строку, то нужно соблюдать правила арифметической последовательности. Для этого нужно использовать скобки.



Формула для вычисления объема сферы в Excel

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

Формула вычисления объема сферы выгладит так:

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

1. В ячейку B2 запишем формулу вычисления объема сферы в одну строку: =(4/3)*ПИ()*A2^3 (A2 – это ссылка на ячейку).
2. В ячейке A2 будем вводить разные радиусы и после каждого ввода в ячейке B2 будем получать результат вычисления объема сфер соответствующих своим радиусам.

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

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

Читайте также: Как посчитать количество строк в Excel
Как посчитать количество заполненных ячеек в Экселе

Процедура подсчета значений в столбце

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

Способ 1: индикатор в строке состояния

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

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

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

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

Способ 2: оператор СЧЁТЗ

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

Главной задачей функции СЧЁТЗ, которая относится к статистической категории операторов, как раз является подсчет количества непустых ячеек. Поэтому мы её с легкостью сможем приспособить для наших нужд, а именно для подсчета элементов столбца, заполненных данными. Синтаксис этой функции следующий:

=СЧЁТЗ(значение1;значение2;…)

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

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





2. Тем самым мы вызвали Мастер функций. Переходим в категорию «Статистические» и выделяем наименование «СЧЁТЗ». После этого производим щелчок по кнопке «OK» внизу данного окошка.



3. Мы переходим к окну аргументов функции СЧЁТЗ. В нём располагаются поля ввода аргументов. Как и количество аргументов, они могут достигать численности 255 единиц. Но для решения поставленной перед нами задачи хватит и одного поля «Значение1». Устанавливаем в него курсор и после этого с зажатой левой кнопкой мыши выделяем на листе тот столбец, значения в котором нужно подсчитать. После того, как координаты столбца отобразились в поле, жмем на кнопку «OK» в нижней части окна аргументов.



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

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

Урок: Мастер функций в Excel

Способ 3: оператор СЧЁТ

С помощью оператора СЧЁТ можно произвести подсчет только числовых значений в выбранной колонке. Он игнорирует текстовые значения и не включает их в общий итог. Данная функция также относится к категории статистических операторов, как и предыдущая. Её задачей является подсчет ячеек в выделенном диапазоне, а в нашем случае в столбце, который содержит числовые значения. Синтаксис этой функции практически идентичен предыдущему оператору:

=СЧЁТ(значение1;значение2;…)

Как видим, аргументы у СЧЁТ и СЧЁТЗ абсолютно одинаковые и представляют собой ссылки на ячейки или диапазоны. Различие в синтаксисе заключается лишь в наименовании самого оператора.

1. Выделяем элемент на листе, куда будет выводиться результат. Нажимаем уже знакомую нам иконку «Вставить функцию».



2. После запуска Мастера функций опять перемещаемся в категорию «Статистические». Затем выделяем наименование «СЧЁТ» и щелкаем по кнопке «OK».



3. После того, как было запущено окно аргументов оператора СЧЁТ, следует в его поле внести запись. В этом окне, как и в окне предыдущей функции, тоже может быть представлено до 255 полей, но, как и в прошлый раз, нам понадобится всего одно из них под названием «Значение1». Вводим в это поле координаты столбца, над которым нам нужно выполнить операцию. Делаем это все тем же образом, каким выполняли данную процедуру для функции СЧЁТЗ: устанавливаем курсор в поле и выделяем колонку таблицы. После того, как адрес столбца был занесен в поле, жмем на кнопку «OK».



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

Урок: Функция СЧЁТ в Excel

Способ 4: оператор СЧЁТЕСЛИ

В отличие от предыдущих способов, использование оператора СЧЁТЕСЛИ позволяет задавать условия, отвечающие значения, которые будут принимать участие в подсчете. Все остальные ячейки будут игнорироваться.

Оператор СЧЁТЕСЛИ тоже причислен к статистической группе функций Excel. Его единственной задачей является подсчет непустых элементов в диапазоне, а в нашем случае в столбце, которые отвечают заданному условию. Синтаксис у данного оператора заметно отличается от предыдущих двух функций:

=СЧЁТЕСЛИ(диапазон;критерий)

Аргумент «Диапазон» представляется в виде ссылки на конкретный массив ячеек, а в нашем случае на колонку.

Аргумент «Критерий» содержит заданное условие. Это может быть как точное числовое или текстовое значение, так и значение, заданное знаками «больше» (>), «меньше» (<), «не равно» (<>) и т.д.

Посчитаем, сколько ячеек с наименованием «Мясо» располагаются в первой колонке таблицы.

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



2. В Мастере функций совершаем переход в категорию «Статистические», выделяем название СЧЁТЕСЛИ и щелкаем по кнопке «OK».



3. Производится активация окошка аргументов функции СЧЁТЕСЛИ. Как видим, окно имеет два поля, которые соответствуют аргументам функции.

   В поле «Диапазон» тем же способом, который мы уже не раз описывали выше, вводим координаты первого столбца таблицы.

   В поле «Критерий» нам нужно задать условие подсчета. Вписываем туда слово «Мясо».

   После того, как вышеуказанные настройки выполнены, жмем на кнопку «OK».



4. Оператор производит вычисления и выдает результат на экран. Как видим, в выделенной колонке в 63 ячейках содержится слово «Мясо».

Давайте немного изменим задачу. Теперь посчитаем количество ячеек в этой же колонке, которые не содержат слово «Мясо».

1. Выделяем ячейку, куда будем выводить результат, и уже описанным ранее способом вызываем окно аргументов оператора СЧЁТЕСЛИ.

   В поле «Диапазон» вводим координаты все того же первого столбца таблицы, который обрабатывали ранее.

   В поле «Критерий» вводим следующее выражение:

   <>Мясо

   То есть, данный критерий задает условие, что мы подсчитываем все заполненные данными элементы, которые не содержат слово «Мясо». Знак «<>» означает в Экселе «не равно».

   После введения этих настроек в окне аргументов жмем на кнопку «OK».



2. В предварительно заданной ячейке сразу же отображается результат. Он сообщает о том, что в выделенном столбце находятся 190 элементов с данными, которые не содержат слово «Мясо».

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

1. Выделяем ячейку для вывода результата и производим переход в окно аргументов функции СЧЁТЕСЛИ.

   В поле «Диапазон» вводим координаты третьего столбца нашей таблицы.

   В поле «Критерий» записываем следующее условие:

   >150

   Это означает, что программа будет подсчитывать только те элементы столбца, которые содержат числа, превышающие 150.

   Далее, как всегда, жмем на кнопку «OK».



2. После проведения подсчета Excel выводит в заранее обозначенную ячейку результат. Как видим, выбранный столбец содержит 82 значения, которые превышают число 150.

Таким образом, мы видим, что в Excel существует целый ряд способов подсчитать количество значений в столбце. Выбор определенного варианта зависит от конкретных целей пользователя. Так, индикатор на строке состояния позволяет только посмотреть количество всех значений в столбце без фиксации результата; функция СЧЁТЗ предоставляет возможность их число зафиксировать в отдельной ячейке; оператор СЧЁТ производит подсчет только элементов, содержащих числовые данные; а с помощью функции СЧЁТЕСЛИ можно задать более сложные условия подсчета элементов.

Excel 2013 makes several categories of math problems easier, among them calculating volumes in solid geometry. While keying numbers into a calculator can get you the right answer, Excel allows you to enter multiple dimensions for the solid you’re working from, change them, and then see the differences in volume. Entering in the classical volume formulas is merely a matter of knowing how to enter them in an Excel-friendly format.

Volume of an Ellipsoid

Enter the labels «Radius 1,» «Radius 2» and «Radius 3» in cells A1, B1 and C1, respectively.

Enter the following formula in cell D2:

=(4/3)_PI()_A2_B2_C2

Enter the three different radii for the ellipsoid whose volume you want to enter in cells A2, B2 and C2. Entering the same number for all three values gives you the volume of a sphere.

Volume of a Rectangular Solid

Enter the labels «Height,» «Width» and «Length» in cells A4, B4 and C4, respectively.

Enter the following formula in cell D5:

=A5_B5_C5

Enter the three different side dimensions for the rectangular solid whose volume you’re deriving in cells A5, B5 and C5. If you enter the same dimensions for all three values, you’re calculating the volume of a cube.

Volume of a Cylindrical Solid

Enter the labels «Radius» and «Height» in cells A7 and B7, respectively.

Enter the following formula in cell D8:

=PI()_A8^2_B8

Enter the radius and height of the cylinder in cells A8 and B8.

Volume of a Cone

Enter the labels «Radius» and «Height» in cells A10 and B10, respectively.

Enter the following formula in cell D11:

=PI()_A11^2_B11*(1/3)

Enter the radius and height of the cone in cells A11 and B11.

Volume of a Torus

Enter the labels «Outer Radius» and «Inner Radius in cells A13 and B13, respectively.

Enter the following formula in cell D14:

=(1/4)_PI()^2_A14+B14*(A14-B14)^2

Enter the inner and outer radius of the torus in cells A14 and B14.

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

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

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

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

Математическая постановка задачи о коробке максимального объема

Для математической постановки данной задачи необходимо ввести в рассмотрение некоторые параметры, характеризующие геометрические размеры коробки. С этой целью дополним содержательную постановку задачи соответствующими параметрами. С этой целью будем рассматривать квадратную заготовку из некоторого гибкого материала, которая имеет длину стороны L (Рис. 2). Из этой заготовки следует вырезать четыре равных квадрата со стороной r по ее углам, а полученную фигуру согнуть, так чтобы получилась коробка без верхней крышки. Задача состоит в таком выборе размера вырезаемых квадратов, чтобы в результате получилась коробка максимального объема.

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

Целевой функцией является объем полученной коробки. Поскольку длина стороны основания коробки равна: L — 2r, а высота коробки равна r, то ее объем находится по формуле: V(r) = (L — 2r)² * r. Исходя из физических соображений, значения переменной r не могут быть отрицательными и превышать величину половины размера исходной заготовки Z, т. е. 0,5 * L.

С целью унификации, обозначим переменную через х = r, что не оказывает влияния на характер решаемой задачи оптимизации. Тогда математическая постановка задачи о коробке максимального объема может быть записана в следующем виде: f(x) = x * (L - 2x)² → max, где Δβ = {X ∈ R¹ | 0 ≤ x ≤ 0,5 * L}.

Целевая функция данной задачи является нелинейной, поэтому задача о коробке максимального размера относится к классу задач нелинейного программирования или нелинейной оптимизации.

Решение задачи о коробке максимального объема с помощью программы MS Excel

Не уменьшая общности математической постановки задачи, предположим: L = 1. Для решения данной задачи с помощью программы MS Excel создадим новую книгу с именем Нелинейная Оптимизация и изменим имя ее первого листа на Задача о коробке. Сделаем необходимые надписи в ячейках А1:В2. После этого введем в ячейку С2 формулу: =C1*(1-2*C1)^2, которая представляет целевую функцию. Внешний вид рабочего листа MS Office Excel с исходными данными для решения задачи о коробке максимального объема имеет следующий вид (Рис. 3).

При изображении исходных данных для решения задачи о коробке максимального объема (Рис. 3.) выбран режим отображения формул в ячейках рабочего листа, что оказывается весьма удобным для визуального контроля правильности задания выражений для соответствующих формул. Этот режим может быть установлен с помощью выполнения операции главного меню: Формулы → Показать формулы. С помощью удаления этой отметки можно вернуться к обычному режиму изображения ячеек рабочего листа.

Для дальнейшего решения задачи следует воспользоваться инструментом поиска решения программы MS Office Excel. С этой целью необходимо выполнить операцию меню: Данные → Поиск решения.

После вызова инструмента поиска решения появится диалоговое окно мастера задания параметров для нахождения решения (Рис. 4). Ввиду важности этого инструмента следует более подробно остановиться па элементах его диалоговых окон.

Первое диалоговое окно Поиск решения (Рис. 4) имеет следующие элементы:

• поле Оптимизировать целевую функцию — используется для задания ссылки на ячейку, в которой задана формула с выражением целевой функции решаемой задачи оптимизации;
• переключатели в группе До: — определяют характер решаемой задачи оптимизации. Для нахождения решения с максимальным значением целевой функции этот переключатель ставится в положение максимальному значению, для нахождения решения с минимальным значением — в положение минимальному значению. Наконец, для нахождения решения, при котором целевая функция принимает некоторое фиксированное значение, переключатель ставится в положение Значению, справа от которого можно ввести требуемое фиксированное значение;
• поле Изменяя ячейки переменных — служит для указания ячеек, которые должны изменяться в процессе поиска решения задачи. Именно в этих ячейках должны находиться переменные решаемой задачи оптимизации;
• в многострочном поле В соответствии с ограничениями отображаются ограничения решаемой задачи оптимизации;
• кнопка Добавить — служит для вызова дополнительного окна Добавление ограничения (Рис. 5);
• кнопка Изменить — служит для вызова дополнительного окна Добавление ограничения, в котором будет отображено ограничение, выбранное в многострочном поле Ограничения;
• кнопка Удалить — служит для удаления ограничения, выбранного в многострочном поле Ограничения;
• кнопка Выполнить — служит для запуска процесса нахождения оптимального решения после спецификации всех параметров поиска решения;
• кнопка Сбросить — служит для сброса всех параметров поиска решения;
• кнопка Загрузить / сохранить — служит для загрузки сохраненных условий ограничений или сохранения условий ограничения;
• кнопка Выполнить — служит для запуска процесса нахождения оптимального решения после спецификации всех параметров поиска решения;
• кнопка Параметры — служит для вызова дополнительного окна Параметры поиска решения (Рис. 3.5) для спецификации дополнительных параметров поиска решения, часть которых уже задана по умолчанию.

Диалоговое окно Добавление ограничения (Рис. 5) предназначено для задания одного ограничения и имеет следующие элементы:

• поле Ссылки на ячейки — служит для указания ячейки или диапазона ячеек, в которых содержится левая часть задаваемого ограничения;
• выпадающий список в центре окна — содержит перечень знаков неравенств ограничений, а также возможность спецификации требования целочисленности или двоичных значений переменных. Мастер поиска решений допускает спецификацию ограничений в виде равенств и неравенств;
• поле Ограничение — служит для указания ячейки, диапазона ячеек или конкретного числа, которое специфицирует правую часть задаваемого ограничения;
• кнопка ОК — служит для добавления ограничения к параметрам поиска решения и закрытия окна Добавление ограничения;
• кнопка Отмена — для закрытия окна Добавление ограничения без добавления ограничения к параметрам поиска решения;
• кнопка Добавить — для добавления ограничения к параметрам поиска решения без закрытия окна Добавление ограничения;
• кнопка Справка — служит для получения справочной информации об элементах диалогового окна Добавление ограничения.

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

• Поле Максимальное время служит для ограничения времени, отпускаемого на поиск решения задачи. В это поле можно ввести время (в секундах), не превышающее 32 767. Установленное по умолчанию значение 100 подходит для решения рассматриваемых типовых задач.
• Поле Число итераций служит для ограничения времени решения задачи посредством задания некоторого предельного числа промежуточных вычислений. Установленное по умолчанию значение 100 подходит для решения рассматриваемых типовых задач.
• Флажок Показывать результаты итераций служит для приостановки поиска решения для просмотра результатов отдельных итераций.
• Флажок Автоматическое масштабирование служит для включения автоматической нормализации входных и выходных значений, качественно различающихся по величине, — например, максимизация прибыли в процентах по отношению к вложениям, исчисляемым в миллионах рублей.

Продолжим решение задачи о коробке максимального объема. В первом окне мастера поиска решения следует в поле с именем Установить целевую ячейку указать ячейку $С$2, в которой содержится формула для расчета целевой функции задачи, а в поле е именем Изменяя ячейки ввести абсолютный адрес ячейки $C$1, в которую будет записано искомое решение задачи.

После этого следует добавить два ограничения на допустимые значения переменной. Для этого следует нажать кнопку Добавить, в результате чего появится дополнительное окно задания ограничений (Рис. 7). Для ввода адресов ячеек в левую часть ограничения предназначено поле е именем Ссылка на ячейку. Форма неравенства ограничения выбирается из вложенного списка в средней части окна.

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

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

Для редактирования некоторого ограничения его следует выделить в многострочном поле с именем Ограничения и нажать кнопку с именем Изменить. В этом случае появится дополнительное окно, аналогичное изображенным на Рис. 7, в котором можно выполнить необходимые действия по редактированию выбранного ограничения.

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

Интерпретируя полученные количественные значения, можно прийти к следующему заключению. Результатом решения является оптимальное значение стороны вырезаемого квадрата: ropt = 0,166666693977061, при котором изготовленная коробка будет иметь максимальный объем: Vmax = 0,0740740740740726. Напомним, что это решение соответствует длине стороны исходной заготовки, равной 1.

Полученное решение имеет общий характер, поскольку в любом конкретном случае для получения решения рассматриваемой задачи оптимизации значение L следует умножить на найденное значение r_opt. Например, если L = 2, то для получения коробки максимального объема следует вырезать по углам исходной заготовки квадраты со стороной 0,166666693977061 * 2 = = 0,333333387954122 м.

Таким образом, задача о коробке максимального объема в каждом конкретном случае может быть решена простой подстановкой в расчетную формулу r_opt = 0,166666693977061 * L конкретного значения величины L. Допустимая точность решения задается в каждом конкретном случае, исходя из специфики задачи и соответствующей проблемной области.

Для анализа найденного решения можно построить график целевой функции и визуально оценить его корректность. С этой целью на отдельном рабочем листе е помощью операции автозаполнения ячеек зададим последовательный ряд чисел исходной переменной r в диапазоне от 0 до 1 с интервалом 0.01, которые запишем в ячейки с адресами А2:А52. Рядом в ячейки В2:В52 запишем соответствующие значения целевой функции. Для этого можно записать формулу: =A2*(1-2*A2)^2 в ячейку В2 и с помощью операции автозаполнения «протащить» содержание этой ячейки на диапазон В3:В52.

После этого для построения графика целевой функции для задачи о коробке следует воспользоваться мастером диаграмм. Построенный график целевой функции будет иметь следующий вид как на Рис. 9. Визуальный анализ этого графика показывает, что максимум целевой функции находится между значениями х = 0,16 и х = 0,17. Этот факт можно также проверить, сравнив значения в ячейках В18 и В19. Тем самым, можно сделать вывод о корректности полученного результата решения данной задачи оптимизации.

Аналитическое решение задачи о коробке максимального объема

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

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

Первая производная функции: f(x) = x*(1-2x)² равна df(x)/dx = 12x²-8x+1. Соответствующее уравнение: 12x²-8x+1=0 является квадратным, которое имеет два корня: Х = 1/6 и х2 = 1/2. Анализ графика целевой функции (см. Рис. 3.5) показывает, что максимальному значению этой функции соответствует первый корень. Данное значение является допустимым и служит точным аналитическим решением задачи оптимизации о коробке максимального размера.

Таким образом, ropt = 1/6. Поскольку число 1/6 является иррациональным и представляет собой периодическую дробь 0.16(6), при желании можно получить его любое приближение произвольной точности. Сравнивая найденное ранее значение 0,166666693977061 с соответствующим округлением точного значения, можно получить относительную погрешность программы MS Excel. Это значение равно: 0.000016382137877% и свидетельствует о том, что решение задач оптимизации программой MS Excel с гладкой целевой функцией выполняется весьма эффективно.

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

Методы переноса данных в Excel могут быть различны:

• Копирование-вставка результатов запросов
• Использование стандартных процедур импорта (например, Microsoft Query) для формирования данных на рабочих листах
• Использование программных средств для доступа к базам данных с последующим переносом информации в диапазоны ячеек
• Непосредственный доступ к данным без копирования информации на рабочие листы
• Подключение к OLAP-кубам

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

Обработка этих данных в Excel может вестись различными методами. Выделим основные способы работы:

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

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

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

Описание примеров

Примеры к статье построены на основе демонстрационной базы данных, которую можно скачать с сайта Microsoft

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=19704

Выгруженный из этой базы данных набор записей сформирован при помощи Microsoft Query.

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

Файл nwdata_sums.xls используется для версий Excel 2000-2003

Файл nwdata_sums.xlsx имеет некоторые отличия и используется для версий Excel 2007-2010.

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

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

Применение метода

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

Проблема при консолидации данных при помощи сводных таблиц появляются, если предполагается дальнейшая работа с этими агрегированными данными. Например, сравнить или дополнить данные из двух разных сводных таблиц (как вариант: объемы продаж и прайс листы). В таком случае обычно прибегают к методу копирования значений из сводных таблиц в промежуточные диапазоны с дальнейшим применением формул поиска (VLOOKUP/HLOOKUP). Очевидно, что проблема возникает при обновлении исходных данных (например, при добавлении новых строк) – требуется заново копировать результаты консолидации из сводной таблицы. Другим, с нашей точки зрения, не совсем корректным методом решения является применение функций поиска непосредственно к диапазонам, которые занимают сводные таблицы. Это может привести к неверному поиску при обновлении не только данных, но и внешнего вида сводной таблицы.

Еще один классический пример непригодности применения сводной таблицы – это требование формирования отчета в заранее предопределенном виде («начальство требует в такой форме и никак иначе»). Возможностей настройки сводной таблицы зачастую недостаточно для предоставления произвольной формы. В данном случае пользователи также обычно используют копирование результатов агрегирования в качестве значений.

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

Суммирование по одному ключевому полю

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

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

SUM!B5

=SUMIF(data!$H:$H;A5;data!$M:$M) 

SUM!B11

=SUMIF(data!$Z:$Z;A11;data!$M:$M) 

Нижние таблицы показывают возможности другой редко используемой функции DSUM

SUM!B19

=DSUM(data!$A$1:$AJ$2156;"Quantity";D18:D19) 

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

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

SUM!B28

=DSUM(data!$A$1:$AJ$2156;"Quantity";D27:D28)

SUM!D28

Здесь data!Z2 означает ссылку на текущую строку данных, а не на конкретную ячейку, так как используется относительная ссылка. К сожалению, нельзя указать в третьем параметры ссылку на одну ячейку – строка заголовка полей все равно требуется, хотя и может быть пустой.

В принципе, функции типа DSUM являются устаревшим методом работы с данными, в подавляющем большинстве случаев лучше использовать SUMIF, SUMPRODUCT или формулы обработки массивов. Но иногда их применение может дать хороший результат, например, при совместном использовании с интерфейсной возможностью «расширенный фильтр» – в обоих случаях используется одинаковое описание условий через дополнительные диапазоны.

Суммирование по нескольким критериям

Таблицы с формулами на листе SUM2 показывают вариант суммирования по нескольким критериям.

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

SUM!D5

=SUMIF(A:A;B5 & ";" & C5;data!M:M) 

Операция «&» используется для соединения строк. Можно также вместо этого оператора использовать функцию CONCATENATE. Промежуточный символ «;» (или любой другой служебный символ) необходим для обеспечения уникальности сцепленных строковых значений.

Пример: Есть, если два поля с перечнем слов. Пары слов «СТОЛ»-«ОСЬ» и «СТО»-«ЛОСЬ» дают одинаковый ключ «СТОЛОСЬ». Что соответственно даст неверный результат при консолидации данных. При использовании служебного символа комбинации ключей будут уникальны «СТОЛ;ОСЬ» и «СТО;ЛОСЬ», что обеспечит корректность вычислений.

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

Второй пример – это популярный вариант использования функции SUMPRODUCT с проверкой условий в виде логического выражения:

SUM!D13

=SUMPRODUCT((data!$H$2:$H$3000=B13)*(data!$Z$2:$Z$3000=C13)*data!$M$2:$M$3000) 

Обрабатываются все ячейки диапазона (data!$M$2:$M$3000), но для тех ячеек, где условия не выполняются, в суммирование попадает нулевое значение (логическая константа FALSE приводится к числу «0»). Такое использование этой функции близко по смыслу к формулам обработки массива, но не требует ввода через Ctrl+Shift+Enter.

Третий пример аналогичен, описанному использованию функций DSUM для листа SUM, но в нем для диапазона условий использовано несколько полей.

SUM!D21

=DSUM(data!$A$1:$AJ$2156;"Quantity";F20:G21) 

Четвертый пример – это использование функций обработки массивов.

SUM!D32

{=SUM(IF(data!$H$2:$H$3000=B32;IF(data!$Z$2:$Z$3000=C32;data!$M$2:$M$3000)))} 

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

Пятый пример содержится только в файле формата Excel 2007 (xlsx). Он показывает возможности новой стандартной функции

SUMIFS

SUM!D40

=SUMIFS(data!$M$2:$M$3000;data!$H$2:$H$3000;B40;data!$Z$2:$Z$3000;C40) 

Поиск по одному критерию

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

Первый вариант – это использование популярной функции VLOOKUP.

SEARCH!B5

=VLOOKUP(A5;data!$H$1:$M$3000;6;0) 

Во втором вариант использовать VLOOKUP нельзя, так как результирующее поле находится слева от искомого. В данном случае используется сочетание функций MATCH+OFFSET.

SEARCH!C13

=MATCH(A13;data!$Z$1:$Z$3000;0) 

SEARCH!B13

=OFFSET(data!$M$1;C13-1;0) 

Первая функция ищет нужную строку, вторая возвращает нужное значение через вычисляемую адресацию.

Поиск по нескольким критериям

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

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

SEARCH2!Е5

=VLOOKUP(C5 & ";" & D5;$A$1:$B$3000;2;0) 

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

SEARCH2!Е 12

{=OFFSET(data!$M$1;MATCH(C13 & ";" & D13; data!$H$1:$H$3000 & ";" & data!$Z$1:$Z$3000;0)-1;0)} 

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

Выборка по одному критерию

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

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

SELECT!С4

=COUNTIF(data!$H:$H;$A$5) 

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

SELECT!С5

=MATCH($A$5;data!$H$1:$H$3000;0) 

Вторая и последующие строки ищутся в вычисляемом диапазоне с отступом от предыдущей найденной строки:

SELECT!С6

=MATCH($A$5;OFFSET(data!$H$1;C5;0; ROWS(data!$H$1:$H$3000)-C5;1);0)+C5

Результат выдается через функцию вычисляемой адресации:

SELECT!B6

=IF(ISNA(C6);"";OFFSET(data!$M$1;C6-1;0)) 

Вместо функции проверки наличия ошибки ISNA можно сравнивать текущую строку с максимальным количеством, так как это сделано в столбце A.

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

Выборка вариантов

Самый сложный вариант выборки по ключевому полю представлен на листе SELECT2. Формулы сами определяют все доступные ключевые значения второго критерия.

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

SELECT2!B2

=IF(LEFT(A2;LEN($D$5)) & ";" = $D$5 & ";"; data!Z2;"") 

Третий служебный столбец проверяет значение второго ключа на уникальность:

SELECT2!C2

=IF(B2="";0;IF(ISNA(MATCH(B2;B$1:B1;0));COUNTIF(C$1:C1;">0")+1;0)) 

Результирующий столбец второго ключа ProductName ищет уникальные значения в служебном столбце C:

SELECT2!E5

=IF(ISNA(MATCH(ROWS($5:5);$C$1:$C$3000;0));"";OFFSET($B$1;MATCH(ROWS($5:5);$C$1:$C$3000;0)-1;0)) 

Столбец Quantity просто суммирует данные по двум критериям, используя технику, описанную на листе SUM2.

SELECT2!F5

=IF(E5="";"";SUMPRODUCT((data!$H$2:$H$3000=D5)*(data!$Z$2:$Z$3000=E5)*data!$M$2:$M$3000)) 

Заключение

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