Формулы: абсолютные и относительные ссылки

Основы работы в электронной таблице Excel

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

1. Создать рабочую книгу – команда Файл, Создать, указать шаблон – Книга.
2. Сохранить созданную книгу – команда Файл, Сохранить, указать диск, папку и имя – SKLAD.XLS.
3. Переименовать лист Лист1 в Товар – команда Формат, Лист, Переименовать.
4. Установит курсор в ячейку А1 и заполнить шапку таблицы (ячейки A1 – F1):

1. После заполнения выделить ячейки A1 – F1 и выполнить команду Формат, Ячейки. На вкладке Шрифт указать Arial Cyr, обычный, 10 пт. На вкладке Выравнивание указать Переносить по словам. На вкладке Вид выбрать способ заливки – светло-серый.
2. Изменить ширину столбцов и строк таблицы с помощью мыши или команды меню Формат, Строка и Формат, Столбец.
3. Выделить ячейки А2 – А10 и выполнить команду Формат, Ячейки. На вкладке Число указать Текстовый. Этот формат позволит вводить коды товаров, которые содержат ведущие нули, например, 0010.
4. Выделить ячейки D2 – D10 и выполнить команду Формат, Ячейки, Число – Денежный, 2 знака
5. Выделить ячейки F2 – F10 и выполнить команду Формат, Ячейки, Число – Денежный, 2 знака
6. Установить курсор в ячейку F2 и ввести формулу: =D2*E2. Закончить ввод нажатием клавиши <Enter>.
7. Установить курсор в ячейку F2 и выполнить команду Правка, Копировать.
8. Выделить ячейки F3 – F10 и выполнить команду Правка, Вставить.
9. Выделить ячейки A1 – F10 и выполнить команду Формат, Ячейки, Граница, Внутренние и Внешние.
10. Заполнить таблицу данными (столбцы A – E):

1. Вычислить:
• Суммарный запас товаров: =СУММ(F2:F10)
• Средняя цена единицы товара: = СУММ(F2:F10)/СУММ(E2:E10)
• Общее число товарных позиций: = СЧЁТ(F2:F10)

Результаты вычисления поместить в ячейки F12 – F14, поясняющие надписи – в ячейки B12 – B14.

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

Установить курсор в ячейку F12 и выполнить команду ВСТАВКА, Примечание. Ввести поясняющий текст. Повторить эти действия для ячеек F13-F14.

Круговая диаграмма

Построить диаграмму структуры запаса товаров в стоимостном выражении.

1. Выделить диапазон ячеек F2 – F10
2. Выполнить команду Вставка, Диаграмма.
3. Выбрать стандартный тип диаграммы – Объемный вариант разрезанной круговой диаграммы.
4. Ряды – в столбцах, указать подписи категорий =Товар!$B$2:$B$10
5. Заголовок диаграммы – Структура товарных запасов, убрать легенду, подписи – категория и доля, ключ легенды.
6. Разместить диаграмму на отдельном листе с именем Структура товарных запасов.
7. Перейти на лист диаграммы, выделить заголовок и выполнить настройку – команда Формат, Выделенное название диаграммы: Шрифт – 14 пт, фон – непрозрачный.

Рисунок 1

Формулы: абсолютные и относительные ссылки

Обороты движения товаров представлены в виде таблицы:

A B C D E F G H I J K L

1. Перейти на Лист2 и переименовать его в Обороты.
2. Установить курсор в ячейку А1 и ввести Товар.
3. Перевести курсор на лист Товары, выделить диапазон ячеек A1 – D10.
4. Выполнить команду Правка, Копировать.
5. Перевести курсор на лист Обороты.
6. Установить курсор в ячейку А2 и выполнить команду Правка, Вставить.
7. Выделить ячейки A1 – D1 и выполнить команду Формат, Ячейки, Выравнивание, Объединение ячеек.
8. Установить курсор в ячейку Е1 и ввести Остаток на начало.
9. Выделить ячейки E1 – F1 и выполнить команду Формат, Ячейки, Выравнивание, Объединение ячеек.
10. Установить курсор в ячейку G1 и ввести Приход
11. Выделить ячейки G1 – H1 и выполнить команду Формат, Ячейки, Выравнивание, Объединение ячеек.
12. Установить курсор в ячейку I1 и ввести Расход.
13. Выделить ячейки I1– J1 и выполнить команду Формат, Ячейки, Выравнивание, Объединение ячеек.
14. Установить курсор в ячейку K1 и ввести Остаток текущий.
15. Выделить ячейки K1-L1 и выполнить команду Формат, Ячейки, Выравнивание, Объединение ячеек.
16. Установить курсор в ячейку Е2 и ввести Кол-во.
17. Установить курсор в ячейку F2 и ввести Сумма.
18. Выделить ячейки Е2 – F2 и выполнить команду Правка, Копировать.
19. Выделить ячейки G2 – L2 и выполнить команду Правка, Вставить.
20. Выделить ячейки F3 – F11 и выполнить команду Формат, Ячейки, Число – Денежный, 2 знака
21. Установить курсор в ячейку F3 и ввести формулу: =$D3*E3 (абсолютная ссылка по координате имя столбца – клавиша <F4>).
22. Размножить формулу по всем ячейкам столбца F3 – F11: установить курсор на маркер автозаполнения первой ячейки с формулой (правый нижний угол ячейки), нажать левую кнопку мышки и протянуть по ячейкам столбца вниз.
23. Выделить диапазон ячеек F3 – F11 и выполнить команду Правка, Копировать.
24. Установить курсор в ячейку H3 и выполнить команду Правка, Вставить.
25. Установить курсор в ячейку J3 и выполнить команду Правка, Вставить.
26. Установить курсор в ячейку L3 и выполнить команду Правка, Вставить.
27. Установить курсор в ячейку K3 и ввести формулу: =E3+G3-I3.
28. Размножить формулу по всем ячейкам столбца K3 – K11: установить курсор на маркер автозаполнения первой ячейки с формулой (правый нижний угол ячейки), нажать левую кнопку мышки и протянуть по ячейкам столбца вниз.
29. Заполнить произвольными значениями ячейки столбцов E, G, I.
30. Выделить диапазон ячеек K3 – K11 и выполнить команду Правка, Копировать.
31. Перейти на лист Товары.
32. Установить курсор в ячейку E2 и выполнить команду Правка, Специальная вставка, Вставить связь. В результате в ячейки столбца E2 – E10 будет введена формула: =Обороты!K3 (ссылка на текущий остаток товара на листе Обороты). Автоматически изменяется стоимость запаса и диаграмма структуры товарных запасов.

Гистограмма

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

1. Выполнить команду Вставка, Диаграмма.
2. Выбрать Гистограмма, Объемный вариант обычной гистограммы.
3. При нажатой клавише <Ctrl> выделить диапазоны ячеек F3 – F11, L3 – L11, ряды в столбцах.
4. Для Ряд1 указать имя – Запас начальный, для Ряд2 – Текущий запас, в качестве подписей по оси Х - =Обороты!$B$3:$B$11.
5. Заголовок диаграммы – Остатки товаров, легенда – внизу диаграммы, разместить диаграмму на отдельном листе с именем Остатки товаров.
6. Выполнить форматирование элементов диаграммы.

Рисунок 2