Создание и заполнение таблиц данными. Ввод формул.

Начнем заполнение данными имеющихся рабочих листов: Закупка, Реализация, Цена, Выручка, Доход.

 

Задание. На рабочем листе " Закупка" создайте таблицу и внесите данные, как показано на рис. 3.

 

Указание.

! Для оформления общего заголовка таблицы воспользуйтесь кнопкой " Объединить и поместить в центре", для размещения заголовков таблицы во всех столбцах следует выделить всю 3-ю строку, затем щелкнуть на ней правой кнопкой мыши, из контекстного меню выберите команду Формат ячеек, откройте закладку Выравнивание, установите флажок в поле Переносить по словам, выберите в полях По вертикали – По центру, По горизонтали – По центру, затем нажмите OK.

! Перед вводом данных задайте форматы ячеек таблицы – Числовой, число десятичных знаков 0, выравнивание – по центру. Наименование предметов – формат Текстовый. Выравнивание – по левому краю (отступ) – 1.

! В ячейках H4: Н8 – введите формулы суммирования по строкам (обратите внимание на предлагаемый диапазон суммируемых ячеек, если диапазон не захватывает все исходные данные – укажите мышью весь необходимый диапазон, затем нажмите Enter ).

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

 

Задание. На рабочем листе " Реализация" внесите исходные данные в таблицу и оформите, как показано на рисунке 4.

 

Рис. 4. Общий вид исходной таблицы на рабочем листе Реализация.

 

Сохранение рабочей книги (файла).

Сохраните промежуточные результаты своей работы.

Для этого выберите команду Сохранить как... в меню Файл, затем в диалоговом окне " Сохранение документа" найдите и откройте свою папку, присвойте файлу имя – Расчет дохода и добавьте свою фамилию. Нажмите кнопку Сохранить.

 

Задание. На рабочем листе " Цена" создайте и заполните две таблицы –

Расходы на закупку и Расчет цен как показано на рисунке 5.

Рис. 5. Общий вид рабочего листа Цена.

Указание.

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

Задайте форматы ячеек в таблице Расходы на закупку:

А4: А9 – текстовый;

В4: В8 – денежный, число десятичных знаков – 2, обозначение – р.

C4: C8 – числовой, число десятичных знаков – 0.

D4: D9 – денежный, число десятичных знаков – 2, обозначение – р.

В ячейки А4: С8 внесите данные с клавиатуры.

В ячейку D4 введите формулу = В4*С4.

Для этого наберите с клавиатуры знак =, затем щелкните левой кнопкой мыши на ячейке В4, нажмите клавишу " знак умножения – * на дополнительной клавиатуре и щелкните мышью на ячейке С4, подтвердите формулу – нажмите клавишу Enter.

Чтобы не повторять набор формулы в ячейках D5, D6, D7, D8. Скопируйте в эти ячейки содержимое ячейки D4 вместе с формулой.

Для снятия команды копирования с ячейки D4 нажмите клавишу Esc.

 

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

Задайте форматы ячеек в таблице Расчет цен:

А14: А18 – текстовый;

В14: В18 – денежный, число десятичных знаков – 2, обозначение – р.

C14: C18 – процентный, число десятичных знаков – 0.

D14: D18 – денежный, число десятичных знаков – 2, обозначение – р.

 

В ячейки А14: С18, В14: В18 и С14: С18 внесите данные с клавиатуры.

В ячейку D14 введите формулу = В14*С14+В14

Для этого наберите с клавиатуры знак =, затем щелкните левой кнопкой мыши на ячейке В14, нажмите " знак умножения – *, щелкните мышью на ячейке С14, затем нажмите " знак сложение – + и подтвердите формулу и нажатием клавиши Enter.

Аналогично предыдущей таблице скопируйте содержимое ячейки D14 с формулой в ячейки D15, D16, D17, D18.

Примените к таблицам обрамление, как показано на рисунке 5.

 

Задание. Создайте и заполните таблицы на листе Выручка, как показано на рисунке 6.

Рис. 6. Общий вид рабочего листа Выручка.

Указание.

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

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

Задание. Заполните и оформите таблицы на листе Доход (рис. 7).

Рис. 7. Общий вид рабочего листа Доход.

Указание.

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

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

Внесите данные в таблицы.

В таблице Доход от реализации за полугодие количество проданного товара, число в столбце Н, совпадает с суммой за полугодие на листе Реализация.

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

 

В ячейках E4: E8 находится формула =D4*(B4-C4).

В строке ИТОГО в ячейке E9 находится формула, суммирующая ячейки E4: E8.

В ячейках E14: E18 находится формула =D14*(B14-C14).

В строке ИТОГО в ячейке E19 находится формула, суммирующая ячейки E14: E18.

 

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

 

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

 

Убедитесь в этом на простых примерах:

 

1. Измените наименование товара – Диван в ячейке А4 на листе Закупка на другое – Софа. Проверьте, произошло изменение наименования данного товара на остальных листах или оно осталось прежним?

2. Измените на листе Реализация количество проданных стульев в феврале с 18 на 50. Проверьте, произошли соответствующие изменения на других рабочих листах?

3. Измените на листе Цена в таблице Расходы на закупку в графе Цена закупки (ячейка В4) число 2 000, 00 р. на новую цену закупки – 5 000, 00 р. Проверьте, произошли соответствующие изменения в соседней таблице Расчет цен, и на других рабочих листах где используется данный показатель?

 

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

Изменения происходят только в тех ячейках, которые имеют прямую связь с исходной ячейкой например, при выполнении п.3 на листе Цена изменяется ячейка D4, т.к. содержит формулу со ссылкой на ячейку В4, и как следствие изменяется итоговая сумма в ячейке D9.

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

 

Связывание таблиц в Excel.

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

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

В зависимости от техники исполнения связывание бывает “прямым“ и через команду СПЕЦИАЛЬНАЯ ВСТАВКА.

1 способ – " Прямое связывание ячеек"

Прямое связывание листов используется непосредственно при вводе формулы в ячейку, когда в качестве одного из элементов формулы используется ссылка на ячейку другого листа. Например, если в ячейке таблицы В4 на рабочем Листе2 содержится формула, которая использует ссылку на ячейку А4 другого рабочего листа (например, Листа1) и оба листа загружены данными, то такое связывание листов называется “прямым”.

Термин “прямое” связывание обозначает, что пользователь сам непосредственно при вводе формулы указываетимя листа и абсолютный адрес ячейки, разделенные восклицательным знаком "! ".

Примеры формул: = C5*Лист1! A4

= Лист3! В2*100%

= Лист1! A1- Лист2! A1

 

Примечание.

 

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

2 способ – Связывание ячеек через команду " Специальная вставка"

Связывание через команду СПЕЦИАЛЬНАЯ ВСТАВКА производится, если какая либо ячейка таблицы на одном рабочем листе должна содержать значение ячейки из другого рабочего листа.

 

Чтобы отразить в ячейке С4 на листе Цена значение ячейки Н4 на исходном листе Закупка, нужно поместить курсор на ячейку Н4 исходного листа и выполнить команду Правка–Копировать. На листе Цена поставить курсор на ячейку С4, которую необходимо связать с исходной, и выполнить команду Правка–Специальная вставка–Вставить связь (см рис. 8). Тогда на листе Цена появится указание на ячейку исходного листа Закупка, например: = Закупка! $Н$4

 

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

Задание. Свяжите ячейки С4, С5, С6, С7, С8 в таблице Расходы на закупку на листе Цена с соответствующими ячейками на листе Закупка, используя различные способысвязывания ячеек (рис. 8).

Рис. 8 Связывание ячеек различных рабочих листов

 

! При связывании ячеек определите, какие ячейки являются исходными.

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

Задания для самостоятельной работы.

 

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

 

Например:

 

1. на листе Цена в таблице Расходы на закупку ячейки А4: А8 связаны с ячейками таблицы Количество закупленной продукции на листе Закупка;

2. ячейки В4: В8 являются исходными, т.к. содержат первоначальные сведения о ценах закупленного товара;

3. ячейки С4: С8 связаны с ячейками Н4: Н8 на листе Закупка;

4. ячейки D4: D8 содержат формулы подсчета затраченных средств на приобретенный товар и ссылаются на ячейки собственной таблицы (например, формула в ячейке D4 имеет вид =В4*С4, что означает умножение цены товара на его количество);

5. ячейка D9 является суммой ячеек D4: D8;

6. во второй таблице Расчет цен на этом же листе ячейки А14: А18 связаны аналогично п.1;

7. ячейки В14: В18 являются связанными с исходными ячейками текущего листа В4: В8;

8. ячейки С4: С8 являются исходными, т.к. содержат первоначальные сведения о наценке салона на закупленный товар;

9. ячейки D14: D18 содержат формулы расчета цены продажи товара и ссылаются на ячейки собственной таблицы (например, формула в ячейке D14 имеет вид =В14*С14+В14, что означает умножение закупочной цены на установленный процент наценки, что дает сумму наценки, которую надо прибавить к закупочной цене);

После выполнения всех операций с этими таблицами произведите проверку их " работоспособности".

Измените наименование товара – Диван в ячейке А4 на листе Закупка на другое – например Софа.

Измените количество закупленного товара Софа в июне (в ячейке G4 на листе Закупка введите число 11).

Измените цену закупки Софы в ячейке В4 на листе Цена на другую – 2500, 00 р.

Измените процент наценки Софы в ячейке С14 на листе Цена с 50% на 32%.

 

Проверьте, произошли изменения в связанных таблицах или нет?

Обратите внимание, на какие ячейки различных таблиц повлияли внесенные изменения.

Внимание! При связывании ячеек через СПЕЦИАЛЬНУЮ ВСТАВКУ... копирование на соседние ячейки становится проблематичным из-за абсолютной адресации ячеек.

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

 

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

 

Задание 2. Создайте на листах Выручка и Доход таблицы по расчету за 2 квартал. Свяжите эти таблицы с соответствующими исходными данными.

 

Указание. В таблицах по расчету выручки и дохода за 2 квартал используйте исходные ячейки только 2 квартала.

 

Задание 3. Постройте круговую диаграмму на листе Доход и проанализируйте распределение дохода по видам продукции.

 

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

 

Указание. В таблицах по расчету выручки и дохода за 2 квартал используйте исходные ячейки только 2 квартала.

 

Microsoft Excel 97. Работа № 5
Автоматизация документа
(на примере формирования прайс-листа).

 

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

 

В этой работе мы создадим прайс-лист фирмы " МультиМедиа Технологии".
По аналогии с этим прайс-листом Вы самостоятельно сможете создавать любые аналогичные документы в Excel.

 

Исходные текстовые данные уже набраны и находятся в файле
С: \ Мои документы \ Заготовки для Excel \ price.xls (рис.1).

 

Откройте его. Вам нужно оформить имеющийся документ в конечный проект (см. листы приложений 1 и 2).

 

 

Рис. 1 Исходный вид документа прайс-лист (из файла price.xls).

 

Проанализируйте содержание документа: название фирмы, название прайс-листа, почтовый адрес, телефон, факс и два адреса электронной почты.

Также обратите внимание на расположение текста в ячейках, на ширину столбцов, имена Рабочих листов.

 

Оформление таблицы

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

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

2. Настройте ширину столбца А. Для этого выделите весь столбец А и выберите команду Формат – Столбец – Автоподбор ширины.

Если бы не переместили заголовки, то ширина столбца А была бы совсем другая!

3. Создайте шапку самой таблицы: Наименование и Цена продукта ячейки А7 и В7. Так как цены будут выражены в долларах и в рублях (цены в рублях будем рассчитывать по текущему курсу ММВБ! ), то ячейку В7 в заголовке объединим с ячейкой С7, а в ячейках В8 и С8 обозначим цены в USD и руб. соответственно. Выполните центрирование текста заголовка таблицы. Если нужно, измените ширину столбцов В и С.

4. Оформите шапку документа:

в строке 1 – Мультимедиа Технологии (шрифт Arial, полужирный, курсив, размер – 14 пт, выравнивание – по центру трех ячеек);

в строке 2 – Прайс-лист программной продукции (шрифт Arial, полужирный, размер – 18 пт, выравнивание – по центру трех ячеек);

в строках 3, 4, 5 – почтовый адрес, телефоны, факс и электронный адрес фирмы (шрифт Arial, обычный, размер – 9 пт, выравнивание – по правому краю ячеек).

5. На каждый продукт отведено три строки: название продукта, краткий комментарий содержания курса и требуемые ресурсы компьютера.

Задайте обрамление всей таблице, используя команду Границы в меню Формат ячейки.

6. Оформите заголовок и название каждого продукта полужирным шрифтом.

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

 

Если в колонтитулах (верхнем и/или нижнем) находится текст, например Лист1 или номер страницы и т.д., то выберите команду Файл – Параметры страницы и в окне диалога Колонтитулы удалите предлагаемый текст или в режиме Предварительного просмотра эту операцию можно сделать через команду Страница.

8. Задайте форматы ячеек с ценами:

для цен в долларах – финансовый без десятичных знаков с обозначением в долларах США;

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

9. Введите числа в ячейки долларовой цены: 40, 80, 60, 90, 90, 35, 35, 35.

10. Присвойте рабочему листу новое название – Прайс-лист.

 

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

 

Итак прайс-лист принял привычный вид, но только с ценами в долларах.

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

Цена в рублях это не просто перемножение цены в долларах на текущий курс рубля!!!

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

Ввод переменных и задание имен ячейкам

 

Перейдите на второй лист. Сделайте столбец А шире, для того, чтобы уместилось название.

В А2 запишите: Текущий курс рубля;

В А3 запишите: Дополнительный процент;

В В2 введите текущий курс рубля – 6, 02;

В В3 введите размер дополнительного процента – 3%.

 

Для дальнейшей работы удобно ввести " собственные имена" (абсолютный адрес) для переменных.

Выделите А2: В3. Выберите Вставка–Имя–Создать и по умолчанию в окне " Создать имена" должен стоять флажок (галочка) в столбце слева, нажмите OK. Таким образом Вы присвоили имена ячейкам В2 и В3. Слева от строки формул находится поле имен ячеек, открыв список этих имен можно увидеть весь список только что присвоенных имен ячеек наших переменных (в них автоматически дописаны знаки подчеркивания между словами). Например: текущий_курс_рубля. Если курсор находится в именованной ячейке, то ее имя будет в поле имени ячейки.

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

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

В ячейке С9 должна быть формула: =В9*Текущий_курс_рубля.

Мышью щелкните на ячейке В9, затем на дополнительной клавиатуре нажмите клавишу умножения – *, затем перейдите на второй лист, щелкните на ячейке В2, нажмите ENTER (если все сделано правильно, то в ячейке С9 будет результат расчета – 240, 80).

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

Для этого щелкните мышью в строке формул и добавьте к имеющейся формуле дополнительные множители: *(1+ Дополнительный_процент).

С клавиатуры наберите знак умножения – *, затем круглую скобку и 1 со знаком +, после чего перейдите на второй лист и щелкните на ячейке В3, затем ENTER.

В ячейке С9 формула приняла вид:

=В9*Текущий_курс_рубля*(1+Дополнительный_процент), а в самой ячейке появилась рублевая цена – 248, 02.

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

Присвойте второму рабочему листу новое название – Переменные.

Итак, мы практически получили прайс-лист для конечного потребителя.

 

Оформление таблицы для дилеров

 

Но фирмы работают еще и с дилерами, которые имеют скидки при покупке товаров.

Необходимо ввести в прайс-лист цены с учетом скидок для дилеров.

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

Для этого нужно создать таблицу цен для дилеров (она будет находиться под основной таблицей).

Добавьте две строки: поместить курсор в строку перед которой будет вставлена новая, в меню выберите Вставка–Строки.

Теперь отступите одну строку и введите название таблицы Цены для дилеров. Название таблицы: шрифт – полужирный курсив, размер шрифта 14 пт (центрировать на три ячейки).

Сама таблица для дилеров похожа на первую только цены будут в долларах и отличие их в том сколько экземпляров приобретает дилер следовательно и размер скидки будет разный. Поэтому таблица будет иметь три столбца: Наименование товара, Цена продукта: от 5 до 20 и свыше 20, т.к. при заключении контракта с дилерами нужно будет учитывать курс рубля (см. образец приложения к работе).

Шапку таблицы оформите также как и в первой таблице и сделайте обрамление.

 

Чтобы не набирать снова наименования продуктов, в ячейку А38 введем ссылку на соответствующую ячейку первой таблицы и запишем ее в виде формулы: =А9, т.к. каждый продукт содержит три строки, то номера ячеек будут увеличиваться на 3.

Аналогично введите ссылки на остальные ячейки прайс-листа с наименованием товара.

 

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

 

Для оформления ячеек с ценами для дилеров надо внести дополнительные переменные на втором листе. В ячейку А5 введите Скидки для дилеров, в ячейки А6 и А7 введите текст: от 5 до 20 и свыше 20. Т.к. скидки даются в процентах, поэтому в ячейки В6 и В7 введите соответственно 25% и 50%.

Аналогичным способом создайте два новых имени для ячеек В6 и В7.

Посмотрите в поле имен ячеек должно быть теперь четыре разных имени ячеек.

 

Вернитесь на первый лист и продолжите заполнение таблицы для дилеров.

Введем формулу для расчета цены первого продукта с учетом существующих скидок для категории дилеров от 5 до 20.

Примечание: цены для дилеров должны быть меньше на величину скидки, т.е. при цене продукта в 40$, дилер от 5 до 20 получит скидку 25%, что составит 10$, следовательно цена для него получится 40$–10$=30$.

 

Исходные данные для первого продукта находятся в ячейке В9, поэтому в ячейку В38 введем формулу: =В9*(1-от_5_до_20). После нажатия клавиши ENTER вы увидите цену $ 30, 00 (не забудьте задать формат этой ячейке – Финансовый с двумя десятичными знаками и обозначением валюты – $ США).

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

Сделайте то же самое для столбца со скидкой «свыше 20».

⇐ Предыдущая12345678Следующая ⇒

Поделиться: