Создание формул в электронной таблице EXCEL

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

СОЗДАНИЕ ФОРМУЛ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ EXCEL

Цель работы: Лабораторная работа посвящена работе с таблицей – списком. В этой лабораторной работе вы научитесь:

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

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

Cоздание формулы

Формула начинается со знака равенства (=).

1) Создание простой формулы.

Следующие формулы содержат операторы (знак или символ, задающий тип вычисления в выражении, cуществуют математические, логические операторы, операторы сравнения и ссылок) и константы (постоянное  значение, например, число 210 и текст «Квартальная премия» являются константами).

Пример формулыОписание=128+345Складывает 128 и 345=5^2Возводит 5 в квадрат

Алгоритм:

  1.  Щелкните ячейку, в которую требуется ввести формулу.
  2.  Введите = (знак равенства).
  3.  Введите формулу.
  4.  Нажмите клавишу ENTER.

2) Создание формулы, содержащей ссылки.

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

Пример формулыОписание=C2Использует значение в ячейке C2

Алгоритм:

  1.  Щелкните ячейку, в которую требуется ввести формулу.
  2.  В строку формул введите = (знак равенства).
  3.  Выполните одно из следующих действий.
    •  Чтобы создать ссылку, выделите ячейку, диапазон ячеек, место в другом листе.
  4.  Нажмите клавишу ENTER.

3) Создание формулы, содержащей функцию.

Пример формулыОписание=SUM (A1:B4)Суммирует все числа в диапазоне A1:B4=MIN (С1:D5)Находит минимальное в диапазоне C1:D5

Алгоритм:

  1.  Щелкните ячейку, в которую требуется ввести формулу.
  2.  Для того чтобы начать формулу с функции, нажмите кнопку Вставка функции на панели формул.
  3.  Выберите функцию, которую нужно использовать.
  4.  Введите аргументы. Чтобы ввести ссылки на ячейки в качестве аргументов, нажмите кнопку Уменьшить чтобы временно скрыть его. Выделите ячейки на листе и нажмите кнопку Увеличить.
  5.  По завершении ввода формулы нажмите клавишу ENTER.
  6.  Создание формулы с вложением функций. Вложенные функции используют функции как один из аргументов другой функции.

Пример формулыОписание=IF(MAX(F2:F5)>50; SUM(G2:G5);0)формула суммирует набор чисел (G2:G5), только если максимальное значение другого набора чисел (F2:F5) больше 50. В противном случае она возвращает значение 0

Алгоритм:

  1.  Щелкните ячейку, в которую требуется ввести формулу.
  2.  Для того чтобы начать формулу с функции, нажмите кнопку Мастер функции на панели формул.
  3.  Выберите функцию, которую нужно использовать.
  4.  Введите аргументы.
    •  Чтобы ввести ссылки на ячейки в качестве аргументов, нажмите кнопку Уменьшить около нужного аргумента, чтобы временно скрыть его. Выделите ячейки на листе и нажмите кнопку   Увеличить.
    •  Чтобы ввести другую функцию в качестве аргумента, введите функцию в соответствующее поле аргумента (для этого нажмите кнопку  вставка функции рядом с поле аргумента). Например, можно добавить SUM(G2:G5).
    •  Чтобы переключиться между частями формулы, отображаемыми в диалоговом окне, нажмите кнопку мыши на имени формулы в поле формула. Например, при нажатии на функции MAX появится аргумент для данной функции.
  5.  По завершении ввода формулы нажмите клавишу ENTER.
  •  Задание

Найти наибольший и наименьший элемент в числовой таблице (см. рис. 1)

(рис.1)

  1.  На новом рабочем листе внести элементы таблицы (рис.3) в ячейки.
  2.  Установить курсор в ячейку C4, ввести запись максимальное.
  3.  Перейти в ячейку D4, выполнив команду Вставка\Функции или щелкнуть на кнопку Вставка_функции на панели инструментов.
  4.  В появившемся диалоговом окне выбрать функции Статистические\MAX
  5.  В следующем диалоговом окне необходимо в строке Число 1 ввести диапазон A1:D3 (для этого выделить его в таблице)
  6.  Аналогично пунктам 2 и 3 выполнить действие по нахождению минимального в строке 5.

  •  Задание

Вычислить значения функции в зависимости от значений аргумента на интервале [-5;5] с шагом 1.

y=

(рис.2)

  1.  Перейти на новый рабочий лист.
  2.  В ячейки столбца, озаглавленного буквой X, внести значения от -5 до 5 с шагом 1.
  3.  Пользуясь Мастером функций, в первую строку значений Y ввести логическую функцию IF (ЕСЛИ).
  4.  Скопировать формулу в нижние ячейки.

Абсолютная и относительная  адресация

Термин "относительная" ссылка означает, что ссылка на эту область будет автоматически корректироваться при копировании формул, например C5,E10 –  относительные ссылки.

Абсолютная адресация противоположна относительной адресации. В абсолютной ссылке перед каждой буквой и цифрой помещается знак доллара, например $A$1, $B$2 – абсолютные ссылки. Абсолютные ссылки используются, когда при вычислении ссылаются на одну конкретную ячейку на листе (при копировании формул – эти ссылки некорректируются).

  •  Задание  Заполнить таблицу умножения.

(рис.3)

  1.  Заполните первую строку (B1:J1) и первый столбец (A2:A10) в таблице (см. рис.3)
    1.  Введите в ячейку B2 формулу =$A2*B$1, содержащую смешанные ссылки.
    2.  Заполните таблицу, скопировав эту формулу в ячейки диапазона B2:J10.

Задание

  1.  На новом листе в Excel в ячейках с А1 по Е1 введите заголовки Дата, Продукт, Оптовая цена, Количество и Доход. (Для перемещения вправо по завершении ввода значений вместо клавиши Enter нажмите клавишу Tab. Следите за шириной ячеек).

  1.  В ячейку А2 введите начальную дату 09.10.08. Заполните датами ячейки до А16.

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

  1.  Введите название продукции – кух.стол в ячейку В2. Дважды щелкните на манипуляторе автозаполнения, что приведет к автоматическому заполнению оставшихся ячеек в столбце В списка.
  2.  Введите в ячейку С2 оптовую цену стола – 400р. Воспользуйтесь манипулятором автозаполнения для быстрого заполнения оставшихся ячеек в списке столбца С.
  3.  Следующий столбец – количество. Введите последовательно числа, начиная с ячейки D2: 3,5,4,2,6,5,3,2,1,0,7,2,6,3,2.
  4.  В ячейку Е2 введите формулу = Оптовая цена * Количество. Измените формат ячейки Е2 на денежный. Потом дважды щелкните на манипуляторе автозаполнения.

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

  •  Дважды щёлкните на вкладке рабочего листа.
  •  Введите новое имя и нажмите <Enter>.

  1.  Лист1 переименуйте, задав имя Стол.
  2.  Часто требуется получить итоговые данные по всему списку. Куда их лучше разместить? Лучше сверху. Для этого вставим нужные строки сверху списка и поместим там итоговые значения. Выделите ячейки Е1:Е3, щелкните на пункте Строки в меню Вставка. Теперь появилось место для размещения итоговой информации.
  3.  В ячейку Е1 введите Всего.
  4.  В ячейку Е2 введите формулу =SUM(доход).

Как закрепить заголовки в списке при прокрутке?

  •  Выделите первую ячейку, начиная с которой вы хотели бы разрешить прокрутку.
  •  В меню Окно щёлкните на команде Фиксировать.

  1.  Список может быть достаточно большим и не умещаться полностью на экране. Неплохо было бы при прокрутке списка всегда видеть заголовки и итоговое значение в ходе прокрутки списка вверх – вниз. Для закрепления заголовка используем следующий приём: выделите ячейку А5 (первую под заголовком), а затем в меню Окно выберите команду Фиксировать.

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

Как создать копию рабочего листа?

  •  Удерживайте клавишу <Ctrl> при перетаскивании вкладки рабочего листа.

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

А) Перетащите вкладку листа вправо на Лист 2.

Б) По-прежнему удерживая кнопку мыши, нажмите клавишу Ctrl. На изображении указателя мыши появится знак плюс.

В) Отпустите кнопку мыши, а затем отпустите клавишу Ctrl. Excel создаст копию рабочего листа Стол, поместив его вкладку справа от вкладки оригинала.

  1.  Измените имя Стол на вкладке нового рабочего листа на имя – Шкаф.
  2.  Сначала введём объемы заказов в столбце Количество: 1,3,2,1,4,2,1,0,3,5,2,3,4,2,1.
  3.  Введите новое название – кух. шкаф в ячейку В5. замените весь перечень наименований на «кух. стол».
  4.  Введите в ячейку С5 оптовую цену на шкаф – 500 р. Измените значения в столбце Оптовая цена на 500 р.
  5.  Самостоятельно: создайте еще один дубликат для заказов на табуреты. Рабочему листу дайте название Табурет. Объемы заказов возьмите произвольными двузначными числами, цена одного табурета – 150 р.

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

А) трижды внести изменения во все три листа;

Б) удалить два листа, внести изменения на первом рабочем листе, а потом снова создать дубликаты.

Существует способ внести изменения сразу во все три листа. Это возможно, если выделить все три листа и объявить их группой.

Как объединить несколько листов в группу?

  •  Щёлкните на вкладке для первого рабочего листа.
  •  Удерживая клавишу Shift, щёлкните на вкладке последнего рабочего листа.

  1.  Объедините все три листа: Стол, Шкаф, Табурет в группу. Любое изменение в активном рабочем листе группы будет отражено на других рабочих листах.
  2.  Внесите следующие изменения в рабочем листе Стол: отформатируйте заголовки шрифт – Times New Roman, полужирный, 12; по центру. Название “Количество” заменим на название “ Штук”; добавьте формулу для подсчета среднего значения объема заказов за день для каждого листа. Для этого в ячейке D1 введите Среднее значение, а в ячейке D2 введите =AVERAGE(Штук). Посмотрите, как изменения были применены к остальным двум листам.
  3.  

Вернитесь к листу Стол. Под списком заказов введите заголовки по образцу для создания сводной таблицы заказов за три недели (cм. рис.4)

(рис.4)

  1.  В ячейку В26 введите формулу =SUM (D5:D9). По ней подсчитывается количество заказов за первую неделю. Попробуйте скопировать эту формулу в ячейки D26 и F26. У вас получились нули. В строке формул можно просмотреть саму формулу. Получился не тот результат, который ожидали. До сих пор мы пользовались при копировании формул относительной адресацией. Для того чтобы адрес не менялся при копировании пользуются абсолютной ссылкой.
  2.  Отредактируйте формулу в ячейке В26 - =SUM($D5:$D9). Знак $ является признаком абсолютной адресации, т.е. при копировании в формуле сохранится ссылка на столбец D. Знак $ вставляется в формулу нажатием F4. Снова повторите копирование формулы из В26 в ячейки D26 и F26. Теперь результаты во всех ячейках одинаковые. Отредактируйте формулу в ячейке D26 : дважды щелкните по ячейке и замените числа 5 на 10, а 9 на 14. Аналогично в ячейке F26: 5 на 15, а 9 на 19.
  3.  В ячейку С26 введите формулу: =В26*$C$5 (количество проданных изделий за неделю, умноженное на цену одного изделия). Скопируйте эту формулу в ячейки Е26 и G26. Проверьте правильность результатов копирования. Ссылка на ячейку С5 является абсолютной (содержит оптовую цену на изделие).
  4.  Посмотрите, как внесенные вами изменения отразились на остальных листах группы.
  5.  Разгруппируйте листы Стол, Шкаф, Табурет.
  6.  В определённый момент работы может понадобиться видеть одновременно данные для всех видов продукции, а не переходить от одного листа к другому. В Excel имеется такая возможность созданием дополнительных окон.
  7.  Создайте новые окна: в меню Окно выберите команду Новое. Вы получите ещё одно окно. Повторите операцию по созданию нового окна. У вас должно получиться три окна. В рабочем пространстве Excel теперь появятся все три окна.
  8.  Щелкните на верхнем окне и активизируйте вкладку Стол, во втором окне – вкладку – Шкаф, а в третьем – Табурет.
  9.  Сравните значения в столбцах ежедневных объемов заказов и доходов. Для этого упорядочите окна по вертикали (опция Слева направо) и в каждом окне осуществите прокрутку, пока не будут отображены столбцы Штук и Доход.

Как добавить новый лист?

С помощью кнопки вставить лист 

  1.  Сделайте активным любое из окон и добавьте новый лист, задайте новому листу имя –Заказ.
  2.  Снова упорядочите окна по вертикали. Создайте на новом рабочем листе Заказ таблицу по образцу (см. рис. 5)

(рис.5)

Когда перед вами на экране все данные из трёх листов Стол, Шкаф, Табурет, поставленную задачу легко можно выполнить простым копированием нужных данных с этих листов на лист Заказ.

  1.  Вернитесь к одному окну, закрыв любые два.
  2.  Сохраните рабочую книгу в своей папке под названием Мебель.

← Предыдущая
Страница 1
Следующая →

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

У нас самая большая информационная база в рунете, поэтому Вы всегда можете найти походите запросы

Искать ещё по теме...

Похожие материалы:

Сохранить?

Пропустить...

Введите код

Ok