Разработка таблицы с расчетами

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

Постановка задачи:
Предположим, что торговая фирма закупает товар и продает его по наличному и безналичному расчету. Количество поступивших товаров и их покупные цены Вам известны. Известно также количество товаров, проданных по наличному и безналичному расчету. При продаже по наличному расчету установлена торговая наценка i %, а по безналичному расчету – j %.
Необходимо подсчитать, какой доход получен от продаж по наличному и безналичному расчету, суммарный доход и количество оставшихся на складе товаров.
Предусмотреть возможность изменять размеры торговой наценки для анализа дохода.
Условие задачи.

Любая таблица, в том числе и электронная, состоит из следующих элементов (рисунок 29):
названия таблицы;
заголовков столбцов ("шапки" таблицы);
информационной части, состоящей из исходных данных и выходных (рассчитываемых) данных.
Условие задачи

Решение.
Действия пользователя
Начните разработку таблицы с конструирования "шапки". Она может быть сделана разными способами.
Приведем один из возможных.
Введите в ячейку А1 название таблицы: АНАЛИЗ ДОХОДА и измените размер
шрифта названия (например, как показано на рисунке ниже).

Пусть шапка таблицы займет две строки. Для этого:
Введите в ячейку А2 текст - Наименование продукции, объедините ячейки
А2:А3 и введите Форматирование Переносить по словам.
Введите в ячейку В2 слово - Получено; объедините ячейки В2:С2 и
поместите их cодержимое в центре.
Введите в ячейки: В3 - ед.; С3 - Цена за ед. долл. Введите в С3
Форматирование Переносить по словам.
Введите в ячейку:D2 - Продано, объедините ячейки: D2: Е2 и поместите их
содержимое в центре.
Введите в ячейку F2 - Остаток на складе, Объедините ячейки F2:F3 и
введите форматирование Переносить по словам, скопируйте содержимое
ячеек D3:E3 в ячейки G3:H3.
Введите в ячейку I2 - Доход всего. Скопируйте формат ячейки F2 в ячейки
I2:I3. Скорректируйте, если это необходимо, ширину столбцов.
Решение.
Действия пользователя

Перейдем к этапу заполнения таблицы исходными данными.
Заполните столбец А наименованиями товаров. Вас, наверно, вначале будет смущать то обстоятельство, что при вводе наименования Видеокассеты после ввода первого символа появляется Видеомагнитофоны. Это сработал авто ввод. Если слово не подходит, продолжайте набирать с клавиатуры те символы, которые нужны. Лишние символы исчезнут.
После заполнения столбца выделите блок А4:А13 и дайте команду для авто подбора ширины столбца.
Заполните числами блок ячеек В4:Е12. При вводе чисел в блок С4:С12 (колонка « Цена за ед. долл.») вводите числа без знака доллара, так как в русифицированной версии программы Excel денежному числовому формату соответствуют рубли, а не доллары, поэтому, если ввести впереди знак доллара, содержимое этих ячеек программа Excel воспримет как текст, а не как число. Мы же в дальнейшем будем использовать эту информацию для вычислений, и нам в этом столбце нужны числа.
После ввода чисел в этот столбец для отображения знака доллара перед числом выделите блок С4:С12 и примените к нему формат числовой/денежный, выбрав, например, строку, австралийские доллары и указав два десятичных знака после запятой.
Решение.
Действия пользователя

Результат работы показан ниже. Таблица с исходными данными готова.

Приступим к конструированию формул.
В столбце «Остаток на складе» из количества полученного товара должно быть вычтено количество товара, проданного по наличному и безналичному расчету.
Выделите ячейку F4 и щелкните кнопкой мыши по знаку = в строке формул.
Для ввода формулы и отображения результата вычисления раскроется окно ввода. Подведите указатель мыши к ячейке В4 (в столбце «Получено») и щелкните кнопкой мыши. В строке появится адрес ячейки.
Введите с клавиатуры знак минус (-) , подведите указатель мыши к ячейке D4 («Продано по безналичному расчету») и щелкните кнопкой мыши. Снова введите с клавиатуры знак (-), подведите указатель мыши к ячейке Е4 и щелкните кнопкой мыши. Формула = В4-D4-E4 готова. Убедившись в правильности результата, нажмите Еnter или щелкните мышью по знаку ввода рядом со строкой формул.
В нижележащих ячейках должны быть выполнены аналогичные действия с
соответствующими ячейками тех же столбцов. Так как ссылки на ячейки содержат
относительные адреса, можно скопировать содержимое ячейки F4 в блок F5:F12.
Это можно сделать двумя способами:
давая соответствующие команды (выделить F4, копировать, выделить F5:F12, вставить),
путем перетаскивания при помощи мыши (выделить F4, подвести указатель мыши к правому нижнему углу ячейки. Когда появится черное перекрестие нажать на кнопку мыши и, не отпуская ее протащить до ячейки F12, затем кнопку мыши отпустить).

Блок F5:F12 заполнился вычисленными по формулам числами. Выделите какую-либо из вновь заполненных ячеек, чтобы убедиться в правильности скопированной формулы. Так, например, в ячейке F7 должна быть формула =В7-D7-E7 ,т.е. у аргументов формулы стоит тот же номер строки, что и у ячейки, в которой производятся вычисления.
В следующей колонке необходимо рассчитать доход от продаж по безналичному расчету, который будет рассчитываться как произведение цены на количество проданного по безналичному расчету и на надбавку при продаже по безналичному расчету. Прежде, чем вводить формулу для вычисления, введите в ячейку А15 текст «торговая наценка на безналичную продажу», а в ячейку Е15 соответствующее значение. Если вы хотите, чтобы торговая наценка отображалась в процентном формате к вводимому числу можно дописать знак % при вводе числа. Если вы первоначально ввели 0,15 , а потом решили отобразить число в виде процентов, воспользуйтесь командами или кнопкой на панели инструментов, чтобы отображались проценты.
Заполните ячейки А16 и Е16 текстом и значениями, касающимися надбавки по
продаже за наличные.
Введите формулу в ячейку G4, пользуясь описанными ранее приемами. После
указания ячеек сомножителей в строке формул должно быть = С4*D4*E15.
Приступим к конструированию формул.

В последующем мы будем копировать содержимое ячейки G4 в ячейки расположенные ниже. При этом адреса строк будут изменяться. Нам для вычислений потребуется сохранить неизменным адрес ячейки, в котором указана процентная надбавка, т.е. нам необходимо адрес ячейки Е15 ввести как абсолютный. Для этого надо поставить знак доллара перед номером строки и столбца (по крайней мере, перед номером строки), т.е. ввести с клавиатуры исправление в формулу, чтобы она имела вид = С4*D4*$E$15. Проверив формулу, введите ее в ячейку. Скопируйте формулу в остальные ячейки этого столбца и при желании, отформатируйте ячейки этого столбца в денежном формате.
Приступим к конструированию формул.
По аналогии с вводом формул для расчета дохода от продаж по безналичному расчету, введите формулы для расчета дохода от продаж по наличному расчету, используя соответствующие адреса ячеек.
Столбец "Доход всего" складывается из значений в столбцах G и H. Ввод формул в ячейки I4:I12 можно выполнить аналогично тому, как это делалось при вводе формул для расчета в столбце «Остаток на складе».
Попробуйте еще один способ - ввод формулы в блок ячеек. Выделите блок ячеек I4:I12. Щелкните по знаку « = » в строке формул, затем выделите блок G4:G12 (первые слагаемые), введите знак « + » и выделите блок H4:H12 (вторые слагаемые). В строке формул будет записана формула = G4:G12+ H4:H12. Это означает, что в каждой ячейке диапазона будет рассчитываться сумма содержимого соответствующих ячеек указанных блоков. Для ввода формулы необходимо одновременно нажать Ctrl+Enter.

Приступим к конструированию формул.
Теперь рассчитаем итоги. Это удобно делать с использованием кнопки Авто сумма. Для этого выделите ячейку, в которую нужно поместить итоги, щелкните кнопкой мыши по значку Авто сумма. Появится пунктирная рамка с выделенным диапазоном чисел, а в ячейке будет приготовленная для ввода функция СУММ ( ) , аргументом которой будет выделенный диапазон. Если диапазон указан, верно, нажмите Enter, если нет, укажите правильный диапазон для суммирования, и нажмите Enter.
Теперь можно приступить к окончательному оформлению таблицы. Откорректируйте ширину столбцов и высоту строк, введите или скопируйте форматы чисел и символов текста, обрисуйте таблицу рамками, выделите, если необходимо, итоги и суммы цветом. Вариант таблицы с расчетами показан далее.