Электронная таблица MS Excel

Лабораторная работа № 1

Электронная таблица MS Excel

Элементарные расчеты на персональном компьютере в электронной таблице MS Excel

Проработав материал этого блока в электронных таблицах на компьютере, вы научитесь:

· вводить исходные данные в таблицу;

· редактировать и форматировать табличные данные;

· вводить расчетные формулы и копировать их;

· применять абсолютные ссылки в формулах;

· использовать встроенные функции;

· подгонять данные под ответ;

· по таблице числовых значений строить диаграммы.

При запуске программы Excel пользователю обычно предлагается для заполнения данными новый пустой документ в оперативной памяти компьютера со стандартным именем Книга1(Book1), состоящий из 16 рабочих листов, разграфленных в виде таблицы на 16 384 строки и 256 столбцов, стандартная ширина каждой клетки равна 9 символам. Экран компьютера в начале стандартного сеанса работы программы выглядит как на рис. 1.

Рис. 1. Вид окна программы обработки электронных таблиц MS Excel при запуске Исходное положение указателя текущей клетки внутри видимой на экране части таблицы — пересечение первой строки и первого столбца, это клетка с адресом A1 в одноименном стиле ссылок.

Альтернативный стиль ссылок на ячейки рабочего листа, когда столбцы также нумеруются, а номер строки указывается в первую очередь, можно активизировать командой Сервис Параметры, выбрав в ее диалоговом окне на вкладке Общие в группе Стиль ссылок позицию переключателя? R1C1. Тогда первая клетка (ячейка) рабочего листа так и будет именоваться R1C1, от английского Row1Column1 (ряд первый, колонка первая).

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

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

Непрерывное перемещение указателя в любом направлении инициируется мышью (выбор позиции фиксируется щелчком), а дискретное — стандартными управляющими клавишами, назначение большинства из которых дано в табл. 1.

Таблица 1. Основные клавиши управления положением табличного указателя текущей клетки

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

Задание Сколько клеток помещается в таблице на одном рабочем листе стандартной книги Excel?

1) Переведите указатель до конца рабочего листа вниз и направо;

2) Для определения номера последнего столбца активизируйте стиль ссылок R1C1;

3) Перемножьте (расчетную формулу можно разместить в любой свободной клетке) номер последней строки на номер последнего столбца.

Переход на другие листы табличной книги достигается щелчком мыши по ярлычкам, либо парными комбинациями служебных клавиш: Ctrl+PageUp и/или Ctrl+PageDown. Переименование рабочего листа инициируется двойным щелчком левой кнопки мыши по ярлычку, после чего можно вводить новое имя.

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

Для записи нового документа в виде файла электронной таблицы на диск используется команда Файл Сохранить как…, в диалоге с которой пользователь выбирает нужную папку на диске, а также имя и тип создаваемого файла. Для таблиц Microsoft Excel по умолчанию предусмотрено расширение *.XLS

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

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

Например, было куплено 850 г апельсинов по цене 12 руб. 56 коп. за 1 кг… Запишем в первую строку таблицы рабочего листа эту информацию. В столбец A будем вносить названия, в столбец B — вес покупки, а в столбец C — цену…

Рис. 2. Заполнение первой клетки таблицы данными примера 1 в режиме ввода с клавиатуры.

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

Для ввода данных необходимо:

· сделать заполняемую клетку текущей (перевести туда рамку указателя);

· набрать последовательность символов на клавиатуре, при этом вводимая строка в Excel отображается и в заполняемой клетке, и над полем таблицы в строке формул;

· закончить ввод нажатием клавиши ввода? Enter, либо щелчком мыши по заменяющей ее экранной кнопке с изображением зеленой галочки (символ ?), расположенной в режиме ввода над полем рабочего листа в левой части строки ввода.

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

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

· повторить ввод данных в ту же клетку;

· отредактировать текущую клетку, дважды щелкнув по ней мышью, или нажав клавишу F2.

Отменить незаконченный ввод можно клавишей Esc, или экранной кнопкой с красным крестиком (символ?) в строке ввода.

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

Для полной очистки текущей клетки от ранее введенной информации нажимайте клавишу Delete.

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

В России принято отделять целую часть дробного числа знаком запятой, а в США — десятичной точкой. В зависимости от состояния параметров настройки версии Windows на национальный стандарт страны использования компьютера, в Excel корректным разделителем в составе числа может оказаться либо точка, либо запятая, но они не могут свободно заменять друг друга и быть допустимыми символами-разделителями одновременно.

Рис. 3. Превращение числовой информации в календарную в результате ошибки кодирования Вы точно не ошибетесь в том, какой же символ — точку или запятую можно использовать в десятичных дробях, если будете набирать числа не верхнем ряду основной часть клавиатуры, а на дополнительной, в режиме NumLock. Кроме / * - + 9 8 7 6 5 4 3 2 1 0 и клавиши ввода, там есть клавиша с изображением десятичной точки (совмещенная с Del). При работе в Excel она всегда вводит именно тот символ, который и следует использовать как разделитель при записи десятичного числа.

В нашем примере при вводе веса в килограммах пользователем правильно была поставлена запятая, а ввод в клетку C1 в качестве значения цены в рублях числа 12.56 (через точку) приводит к тому, что, благодаря действию настройки компьютера на точку как разделитель компонентов даты, эти пять символов интерпретируются программой как начало 12-го месяца 56-го года текущего века (сначала предпринимается попытка отыскать в календаре 56-й месяц). В строке формул это значение и показано — 01.12.1956, что соответствует 1 декабря, причем дата здесь выводится сокращенно, без указания номера года, как 1.12 в клетке C1.

В зависимости от текущей установки краткого формата даты, пользователь может увидеть и другие варианты, например запись без номера дня — дек.56

Если попробовать улучшить положение, преобразовав дату, появившуюся в результате неправильного ввода десятичного числа, к денежному формату, например, нажав соответствующую кнопку на панели инструментов (см. рис.1), то результатом таких усилий будет 20 790р., что в качестве цены килограмма апельсинов вместо исходного значения 12,56 р. выглядит несколько неожиданно.

Откуда же появилось это загадочное пятизначное число? Дело в том, что в Excel даты кодируются путем пересчета дней с начала века, в этой системе день номер 1 — это 1 января 1900 года. Сериальное число 20 790 обозначает точное количество дней, прошедших между началом XX столетия и 1 декабря 1956 г. Рецептом исправления показанной на рис. 6 ошибки является только повторный ввод числа 12,56 в ту же самую клетку C1, но теперь уже правильно — через запятую. Поскольку в процессе эксперимента этой клетке был назначен денежный формат с округлением до целых, то на экране появится цена 13р. .

Рис. 4. Увеличение точности округления при выводе значения десятичной дроби на экран.

Обратите внимание, что в строке формул (рис. 4) выводится истинное значение введенной в текущую клетку числовой константы 12,56. Для его вывода в денежном формате с точностью до копеек нужно совместить белую стрелку указателя мыши с экранной кнопкой Увеличить разрядность панели инструментов Форматирование и пару раз «нажать на нее», щелкая мышью. Тогда получится 12,56р.

В зависимости от состава вводимой информации, и особенно от её первого символа (префикса) данных электронные таблицы автоматически относят их после ввода к одному из двух типов: константа или формула. Табличные формулы начинаются с префикса и могут состоять из:

· числовых констант,

· знаков действий и скобок,

· адресов и/или имен табличных диапазонов и отдельных клеток,

· имен встроенных функций.

Префиксами формулы, с которых обязательно начинается ее ввод, могут быть символы =, + и ;

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

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

· Даты хранятся как целые числа, хотя формат их записи больше похож на текст.

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

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

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

Рис. 5. Заполнение таблицы исходными данными и их элементарное форматирование

excel таблица данные форматирование Действия оператора по приведению в порядок вида таблицы после ввода данных:

1. Увеличена ширина первого столбца.

1. Вставлена новая первая строка.

2. Текст в строке 1 выровнен по центру.

3. Текст в A10 выровнен по правому краю.

4. Увеличена до тысячных) разрядность записи веса апельсинов в клетке B2.

5. Формат клеток B2 и C2 признан образцовым и скопирован вниз.

Слова, напечатанные здесь полужирным шрифтом, являются ключевыми для Вашей самостоятельной работы с Предметным указателем Справочной системы Excel

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

Самый традиционный подход — найти затраты на отдельные продукты (путем перемножения веса каждого продукта на цену), а затем сложить их вместе.

Чтобы узнать, сколько нужно заплатить за 850 г апельсинов при цене 12,56 р./кг, можно просто поместить в ячейку D2 формулу произведения числовых констант Этот способ соответствует использованию табличной клетки в качестве аналога калькулятора арифметических выражений, только клавиша = в Excel при наборе расчетного выражения нажимается первой, а не последней.

Современные калькуляторы, а тем более персональные компьютеры, хранят в своей памяти не только данные, но и алгоритмы решения задач, их можно программировать. Клетка рабочего листа электронной таблицы соответствует ячейке машинной памяти, предназначенную для хранения изменяемой в процессе работы информации, а имена клеток похожи на идентификаторы переменных в языке программирования и имеют то же назначение. Чтобы запрограммировать некоторое простое вычисление, нужно закодировать его расчетную формулу адресами клеток с исходными данными, соединенными знаками действий в корректное математическое выражение и ввести его в нужное место таблицы, например формулу =B2*C2 поместить в ячейку D2. Чтобы не промахнуться с координатами клеток и не наделать при буквенно-цифровом наборе опечаток, рекомендуется вставлять в формулы табличные адреса, просто щелкая мышью по клеткам с операндами (см. рис. 6). При этом клавишу =, скобки и знаки действий удобно нажимать свободной от мыши рукой.

Рис. 6. Ввод в таблицу формулы с указанием ссылок щелчком по клеткам без нажатия алфавитных и цифровых клавиш для набора адресов сомножителей.

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

Исчислив расходы на апельсины — 10,68р., можно переходить к программированию аналогичных действий в следующей строке и далее вниз до конца списка продуктов. Только что введенная формула отражает суть расчетной модели, в каждой строке одинаковой — число из колонки B умножить на число из колонки C, а значение их произведения вывести на экран в колонке D.

а) выделение блока клеток, принимающих копию б) освобождение левой кнопки мыши Рис. 7. Процесс копирования формулы на блок клеток вниз движением мыши Для кодирования повторяющихся вычислений в электронных таблицах применяется технология копирования клеток с формулами. Источником формулы в нашем примере будет клетка D2, а копировать ее нужно вниз по столбцу на блок ячеек D3: D9. После того как исходная формула правильно записана и введена в клетку-источник, которая уже является текущей, необходимо совместить указатель мыши с угловым манипулятором рамки выделения. Указатель, бывший до того толстым белым плюсом, станет более тонким черным крестом.

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

Не торопитесь отменять выделение блока клеток, сохранившееся после окончания копирования, а нажмите в этом состоянии экранную кнопку Автосуммирование панели инструментов Стандартная (на кнопке изображен математический символ операции суммирования — большая греческая буква, читается «сигма»), и под выделенным блоком в результате автоматической подстановки формулы =СУММ (D2:D9) в ячейке D10 возникнет значение самой популярной встроенной табличной функции, удостоенной персональной кнопки на стандартной панели инструментов. При копировании адреса клеток в составе формулы адаптируются к направлению копирования — в нашем случае номера строк будут расти с шагом 1 от строки к строке.

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

Рис. 8. Ввод формулы массива в выделенный диапазон.

Для ее ввода нужно выделить в таблице заполняемый блок клеток D2: D9, набрать расчетную формулу =B2:B9*C2:C9, и закончить ее ввод (не в одну клетку, а в весь выделенный блок) уже не привычным нажатием клавиши ввода, а комбинацией трех соседних клавиш Ctrl-Shift-Enter.

Такая формула заполняет сразу весь выделенный блок, и необходимость в копировании отпадает. Обратите внимание, что в строке ввода формула массива, которой подчинена текущая клетка, отображается в фигурных скобках {=B2:B9*C2:C9}.

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

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

Пусть — количество продуктов (в нашем примере их семь);

— номер продукта в упорядоченном списке, меняется от 1 до ;

— приобретенное количество-го продукта;

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

где — вектор, в элементах которого записан вес покупаемых продуктов;

— набор соответствующих цен.

Столь широко распространенная в быту учетная операция уже закодирована в Excel встроенной математической функций. В оригинальной (англо-американской) версии пакета она называется =SUMPROD, а в русифицированной имеет идентификатор =СУММПРОИЗВ, что с точки зрения вводимой длины строки и вероятности опечатки при посимвольном наборе менее удачно.

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

Попробуем обратиться ко встроенной функции =СУММПРОИЗВ для помещения итоговой величины затрат в клетку D10. Для этого нужно выделить эту клетку D10 переводом в нее (рамки) табличного указателя, а затем щелкнуть по кнопке Мастер функций, либо использовать команду меню Вставка Функция…

На шаге 1 в левом окне выбирается Категория функций — Математические ?, а затем в правом окне прокручивается алфавитный список имен всех встроенных функций выбранной категории.

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

Диалоговое окно Мастера функций обычно всплывает на экране как раз в таком положении, что закрывает собой нужные ячейки рабочего листа, но его легко подвинуть в сторону мышью, удерживая нажатием левой кнопки мыши стрелку экранного указателя на строке заголовка. Когда данные о весе станут целиком видны на экране, достаточно их выделить (клетки будут обводиться пунктиром), чтобы ссылка на соответствующий диапазон B2: B9 синхронно вписалась в поле ввода табличных координат первого аргумента функции. Затем точка ввода данных | щелчком мыши переводится вниз в поле ввода второго аргумента, и аналогично закрашиваются данные о ценах — C2: C9.

Рис. 9. Состояние ввода в текущую ячейку обращения к функции с помощью Мастера функций

Теперь можно закончить вызов функции, нажав на клавишу ввода или экранную кнопку Готово диалогового окна Мастера функций (крайняя справа внизу). В случае успеха в текущую клетку D10 нами введена последовательность символов =СУММПРОИЗВ (B2:B9;C2:C9), воспринимаемая программой Excel как расчетная формула, состоящая из только обращения к стандартной функции.

В клетке таблицы после ввода в нее формулы появляется ответ — в примере 1 это числовое значение 205,6795, а производящая формула видна над полем таблицы в строке ввода, если клетка выделена текущим положением рамки табличного указателя. Предлагаем читателю самостоятельно справиться с форматированием полученного значения, например вывести его в денежном стиле с точностью до копеек.

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

Если справиться с заменой значения в клетке B5 прямым вводом «сверху» нового числа, то после приема значения данных итог затрат мгновенно станет равен 216,27р., так как электронная таблица автоматически обновляет результаты формул при изменении исходных данных, значения которых заданы не константами, а табличными ссылками на другие клетки (ячейки).

· Клетки, на которые ссылается формула текущей, называются зависимыми.

· Клетки, формулы которых ссылаются на текущую, называются влияющими.

Рис. 10. Поиск на рабочем листе клеток таблицы, в которых есть формулы, зависящие от текущей.

При организации сложных вычислений в таблице важно следить за образующейся структурой формул. Неоценимую помощь в этом способна оказать панель инструментов Зависимости (рис. 10).

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

Попробуем просто вводить в ячейку B8 новые числа — 3,5 (можно побольше), 4 (надо чуть поменьше), и так далее… Этот процесс подбора в Excel автоматизирован. Для вызова процедуры подбора значения одного параметра, приводящего цепочку формул к нужному ответу, дадим команду Сервис Подбор параметра…

Рис. 11. Диалоговое окно процедуры численного решения неявных уравнений.

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

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

Заметим, что если решений несколько, то находится только одно из них — ближайшее к начальному значению влияющей ячейки, которое при подгоне к нужному ответу изменяется. Если нужно найти другой корень — повторите подбор с другим начальным приближением. Давайте подробнее изучим влияние частных затрат на приобретение каждого продукта на показатель «Затраты ИТОГО» .

Нагляднейшей формой сравнения чисел является построение по таблице данных графиков стандартного типа с помощью процедуры Мастер диаграмм, которая активизируется кнопкой панели инструментов Стандартная, или через меню выбором командной последовательности Вставка Диаграмма…

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

Рис. 12. Выделение диапазона, содержащего данные для построения диаграммы — шаг 1.

На шаге 2 выберем тип диаграммы — Круговая, а на шаге 3 — вид диаграммы (7).

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

Удельный вес каждого слагаемого — дробь, в числителе которой стоит текущее слагаемое, а знаменатель всех — полная сумма числителей. Удельный вес затрат на апельсины рассчитывается по формуле =D2/D10. Если мы скопируем ее вниз, то получим =D3/D11, =D4/D12, …

Как только ссылка знаменателя укажет на пустую клетку D11 (по умолчанию числовое её значение считается нулевым), произойдет вывод сообщения об ошибке: предпринимается попытка выполнить математически некорректную операцию — разделить положительный числитель на ноль (сообщение #ДЕЛ/0!).

Рис. 13. Круговая диаграмма по числовым значениям формул в диапазоне D2: D9

Рис. 14. Табличная модель вычисления частных долей общих затрат.

Чтобы закрепить в формуле знаменателя номер 10 строки, защитить его от изменения при копировании выражения вниз на другие строки, необходимо вставить перед 10 знак доллара $, после чего адрес клетки станет абсолютным и запись формулы примет вид =D2/D$ 10

Это исправление в режиме редактирования содержимого клетки можно сделать, переключившись на латинский алфавит, и нажав знак $ в верхнем ряду клавиатуры (этот символ совмещен с цифрой 4), причем одновременно придется удерживать Shift — клавишу переключения регистра.

Удобнее при редактировании превращать адрес клетки в абсолютный, нажимая клавишу F4. Тогда адрес D10, на который указывает точка ввода |, будет сам последовательно преображаться во все возможные варианты: абсолютный $D$ 10, смешанные D$ 10 и $D10, относительный D10.

При копировании формулы =D2/D$ 10 вниз получится последовательность выражений =D3/D$ 10, D4/D$ 10…, при этом в новых дробях изменяется номер строки в числителе, где адрес относительный. Обратите внимание на то, что на построенной ранее круговой диаграмме картофелю соответствует сектор, занимающий 26% площади, а явный расчет дает меньший результат — доля затрат на картофель равна 25,4% общей суммы (см. рис. 14).

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

Чтобы новый расчетный фрагмент таблицы имел на экране компактный и обозримый вид, лучше не заполнять справа новыми формулами большое количество клеток в тех же первых строчках, а разместить еще один экземпляр набора исходных констант в свободных клетках ниже. Например, можно разместить копию блока A1: A9 в таблице, начиная с A13 (выделить A1: A9, команда Правка Копировать, щелкнуть по A13, нажать клавишу ввода), либо ввести в A14 формулу =A2 и методом автозаполнения (мышью за угол) скопировать ее на диапазон A14: C21.

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

Рис. 15. Измерение роста цен с помощью индекса фиксированного состава.

Для оценки изменения покупательной способности денег по заданному набору продуктов за истекший период исчисляется сводный показатель — агрегатный индекс цен:

где p — вектор цен продуктов;

q — вектор объемов потребления продуктов (вес покупки).

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

По проделанным заданиям сформируйте отчет.