Таблица № 1 – Продажи.
Таблица № 2 – Сотрудники.
Таблица № 3 – Товары.
В таблице Продажи во всех строках графы Продавец должен быть выпадающий список со всеми сотрудниками из таблицы Сотрудники.
В таблице Продажи во всех строках графы Товар должен быть выпадающий список со всеми товарами из таблицы Товары.
В таблице Продажи графы Телефон продавца, Цена и Итого должны заполняться автоматически по формулам в соответствии с выбранным сотрудником или товаром.
Перед началом работы необходимо загрузить из интернета и установить программу
LibreOffice (URL: http://ru.libreoffice.org/)
или
OpenOffice (URL: http://www.openoffice.org/ru/).
Все программы, необходимые для выполнения данного урока, бесплатны.
Порядок выполнения работы
1. Запустить программу LibreOffice Calc или OpenOffice Calc.2. Если по умолчанию был создан только 1 лист с именем Лист1, то создать ещё 2 листа в текущем документе. Для этого внизу документа справа от имени текущего листа Лист1 нажать 2 раза кнопку со знаком + для создания второго и третьего листа.
3. Переименовать эти 3 листа, изменив их названия на "Продажи", "Сотрудники", "Товары". Для переименования листа нужно дважды нажать курсором мыши на названии листа и ввести новое название.
4. Перейти на лист Продажи, нажав курсором мыши на его названии внизу документа.
5. Заполнить текущий лист данными. В ячейке A1 написать фразу "Продажи компании" и придуманное название компании вместе с видом организации, например: "Продажи продуктового магазина Родник". В ячейки A3, B3, C3, ..., G3 написать "Дата и время продажи", "Продавец", "Телефон продавца", "Товар", "Цена", "Количество", "Итого".
6. Увеличить ширину столбцов курсором мыши, перетащив линию между заголовками столбцов A и B, B и C и т.д.
7. Выделить ячейки с A1 до G1 и объединить их (пункт меню Формат → Объединить ячейки → Объединить и центрировать ячейки).
8. Выделить ячейки с A3 до G3 и выровнять в них текст по центру (соответствующая кнопка на панели инструментов или пункт меню Формат → Выравнивание → По центру).
9. Перейти на лист Сотрудники.
10. Заполнить текущий лист данными. В ячейке A1 написать "Сотрудники". В ячейки A3, B3, C3, D3 написать
"ФИО сотрудника", "Дата рождения", "Телефон", "Смена".
11. Увеличить ширину столбцов курсором мыши, перетащив линию между заголовками столбцов A и B, B и C и т.д.
12. Выделить ячейки с A1 до D1 и объединить их (пункт меню Формат → Объединить ячейки → Объединить и центрировать ячейки)
13. Выделить ячейки с A3 до D3 и выровнять в них текст по центру.
14. Заполнить таблицу данными. В ячейки A4, B4, C4, D4 написать "Петров П.А.", "12.12.1984", "8-924-123-45-67", "2". Аналогично заполнить следующие 3 строки. Всего в таблице должно быть 4 сотрудника.
15. Перейти на лист Товары и повторить предыдущие 5 пунктов для этого листа. В ячейке A1 написать "Товары". Заголовки столбцов в строке 3: "Название товара", "Количество в упаковке", "Срок годности", "Цена". Образец данных: "Сок Любимый сад 1 л", "10", "12 месяцев", "50". Всего в таблице должно быть 4 товара.
16. Перейти на лист Сотрудники.
17. Открыть диспетчер имён (пункт меню Вставка → Названия → Управление... или Определить).
18. Создать название "ВсеСотрудники" (нажав кнопку Добавить). Ввести название "ВсеСотрудники", задать статический диапазон с фамилиями сотрудников, нажав кнопку со стрелкой справа от поля Объем (или Диапазон, или Назначен на), выбрав курсором мыши ячейки с A4 до A7, снова нажав кнопку со стрелкой, а затем кнопки Добавить и OK.
19. Перейти на лист Товары.
20. Открыть диспетчер имён (пункт меню Вставка → Названия → Управление...).
21. Создать название "ВсеТовары" (нажав кнопку Добавить). Ввести название "ВсеТовары" и задать динамический диапазон с названиями товаров, введя в поле Объем (или Диапазон, или Назначен на): OFFSET($Товары.$A$4;0;0;COUNTA($Товары.$A$1:$A$100)-2;1)
Затем нажать кнопки Добавить и OK.
22. Перейти на лист Продажи.
23. Установить курсор в ячейку B4.
24. Установить связь между листами Продажи и Сотрудники, создав выпадающий список в ячейке B4, в котором будут находиться фамилии всех сотрудников с листа Сотрудники. Для этого выбрать пункт меню Данные → Проверка.... В появившемся окне на вкладке Критерий выбрать тип данных в списке Разрешить "Диапазон ячеек", в поле Источник ввести "ВсеСотрудники" и нажать кнопку OK.
25. Навести курсор мыши на правый нижний угол ячейки B4 (курсор станет в форме креста), нажать левую кнопку мыши, переместить курсор на 6 или более ячеек вниз и отпустить левую кнопку мыши. Теперь устанавливая курсор в любую из этих ячеек, будет появляться выпадающий список справа от ячейки с фамилиями сотрудников с листа Сотрудники.
26. Установить курсор в ячейку D4.
27. Установить связь между листами Продажи и Товары, создав выпадающий список в ячейке D4, в котором будут находиться названия всех товаров с листа Товары. Для этого выбрать пункт меню Данные → Проверка.... В появившемся окне на вкладке Критерий выбрать тип данных в списке Разрешить "Диапазон ячеек", в поле Источник ввести "ВсеТовары" и нажать кнопку OK.
28. Навести курсор мыши на правый нижний угол ячейки D4 (курсор станет в форме креста), нажать левую кнопку мыши, переместить курсор на 6 или более ячеек вниз и отпустить левую кнопку мыши. Теперь устанавливая курсор в любую из этих ячеек, будет появляться выпадающий список справа от ячейки с названиями всех товаров с листа Товары.
29. Теперь нужно проверить, при добавлении нового сотрудника на лист Сотрудники и нового товара на лист Товары появятся они в выпадающих списках или нет. Для этого перейти на лист Сотрудники и добавить ещё одного сотрудника внизу таблицы. Потом перейти на лист Товары и добавить ещё один товар внизу таблицы. Затем перейти на лист Продажи и проверить, появились ли добавленные записи в выпадающих списках в ячейках B4 и D4.
30. Видно, что новый сотрудник не появился, а новый товар появился. Это связано с тем, что диапазон сотрудников был указан вручную, и он автоматически не расширяется при добавлении новых сотрудников, а диапазон товаров был указан через формулу, в которой было указано, что диапазон состоит из товаров со всех строк листа Товары.
31. Теперь необходимо добавить нового сотрудника в выпадающий список. Для этого перейти на лист Сотрудники, открыть диспетчер имён (пункт меню Вставка → Названия → Управление...), курсором мыши выделить имя ВсеСотрудники, нажать внизу кнопку со стрелкой справа от поля Объем (или Диапазон, или Назначен на), выбрать курсором мыши ячейки с A4 до A8 с фамилиями всех сотрудников (включая нового добавленного), снова нажать кнопку со стрелкой, а затем кнопку OK.
32. Перейти на лист Продажи и убедиться, что добавленный сотрудник появился в выпадающем списке в ячейках B4 и ниже.
33. Заполнить лист Продажи данными о 7 продажах. Заполнять только поля Дата и время продажи, Продавец, Товар и Количество. Поля Телефон продавца, Цена, Итого оставить пустыми. В поле Дата и время продажи нужно вставлять текущую дату и время, нажав на клавиатуре клавиши CTRL + Ж (если время не появилось, то написать его вручную). Поля Продавец и Товар нужно заполнять, выбирая значения из выпадающего списка, причём в разных строках значения могут повторяться. Поле Количество заполнять любыми целыми числами.
34. Установить связь между листами Продажи и Сотрудники так, чтобы поле Телефон продавца автоматически заполнялось при заполнении поля Продавец. Для этого в ячейку C4 ввести:
=IFERROR(INDIRECT(ADDRESS(MATCH(B4;Сотрудники.$A$1:$A$1000;0);3;1;1;"Сотрудники"));"")
После этого нажать клавишу Enter.
В этой формуле:
B4 – адрес ячейки, в которой написана фамилия сотрудника, телефон которого нужно найти;
Сотрудники – название листа, на котором нужно искать телефон сотрудника;
$A1:$A1000 – означает, что на листе Сотрудники фамилию сотрудника нужно искать в столбце A;
3 – номер столбца, в котором хранится телефон сотрудника на листе Сотрудники.
Если формула введена правильно и в ячейке B4 выбрана фамилия сотрудника, то в ячейке C4 появится телефон выбранного сотрудника.
35. Заполнить поле Телефон в следующих строках листа. Для этого установить курсор в ячейку C4, навести курсор мыши на правый нижний угол ячейки C4 (курсор станет в форме креста), нажать левую кнопку мыши, переместить курсор на 6 или более ячеек вниз и отпустить левую кнопку мыши. Теперь в следующих строках также будут показаны телефоны сотрудников.
36. Аналогично установить связь между листами Продажи и Товары так, чтобы поле Цена автоматически заполнялось при заполнении поля Товар. Для этого в ячейку E4 ввести формулу как в ячейку C4:
=IFERROR(INDIRECT(ADDRESS(MATCH(B4;Сотрудники.$A$1:$A$1000;0);3;1;1;"Сотрудники"));"")
и внести самостоятельно изменения в эту формулу так, чтобы цена выбранного товара была подставлена в эту ячейку, а затем нажать клавишу Enter.
Если формула введена правильно и в ячейке D4 выбран товар, то в ячейке E4 появится цена выбранного товара.
Подсказка (в этой формуле нужно заменить "B4" на "D4", "Сотрудники" на "Товары" в двух местах, "3" на "4").
37. Заполнить поле Цена в следующих строках листа. Для этого установить курсор в ячейку E4, навести курсор мыши на правый нижний угол ячейки E4 (курсор станет в форме креста), нажать левую кнопку мыши, переместить курсор на 6 или более ячеек вниз и отпустить левую кнопку мыши. Теперь в следующих строках также будут показаны цены товаров.
38. Заполнить поле Итого в строке 4 с помощью формулы, в которое записать произведение цены товара и количества проданного товара. Для этого в ячейку G4 записать формулу: "=E4*F4".
39. Заполнить поле Итого в следующих строках листа. Для этого установить курсор в ячейку G4, навести курсор мыши на правый нижний угол ячейки G4, нажать левую кнопку мыши, переместить курсор на 6 или более ячеек вниз и отпустить левую кнопку мыши. Теперь в следующих строках также будет заполнено поле Итого.
40. Изменить формат ячеек столбца Цена так, чтобы к числу автоматически добавлялась фраза " руб.". Для этого выделить ячейки с E4 по E10, нажать на них правой кнопкой мыши, выбрать пункт меню Формат ячеек..., на вкладке меню Числа выбрать формат "Все", в поле Код формата ввести строку «#0,00" руб."», нажать кнопку OK. Ко всем числам будет добавлена фраза " руб." и числа будут отображаться в формате с копейками.
41. Аналогично изменить формат ячеек столбца Итого листа Продажи и столбца Цена листа Товары и вернуться на лист Продажи.
42. Создать фильтр для всех полей таблицы с листа Продажи. Для этого выделить ячейки с A3 по G3, выбрать пункт меню Данные → Фильтр → Автофильтр.
43. Проверить работу фильтра, отобразив все продажи первого сотрудника. Для этого нажать кнопку фильтра в ячейке B3 и в появившемся окне сбросить галочки возле всех сотрудников, кроме первого, и нажать кнопку OK. Отобразятся записи продаж только первого сотрудника.
44. Сбросить фильтр по полю Продавец. Для этого нажать кнопку фильтра в ячейке B3 и в появившемся окне установить галочку возле строки "Все" и нажать кнопку OK. Отобразятся все записи продаж.
45. Проверить работу фильтра, отобразив все продажи товаров с ценой больше 60 рублей. Для этого нажать кнопку фильтра в ячейке E3 и в появившемся окне выбрать пункт меню Стандартный фильтр, выбрать поле "Цена", условие ">", ввести число 60 и нажать кнопку OK. Отобразятся записи продаж только товаров с ценой больше 60 рублей.
46. Сбросить фильтр по полю Цена. Для этого нажать кнопку фильтра в ячейке E3 и в появившемся окне установить галочку возле строки "Все" и нажать кнопку OK. Отобразятся все записи продаж.
47. Создать обрамление ячеек листа Продажи. Для этого выделить ячейки с A3 по G10, нажать на них правой кнопкой мыши, выбрать пункт меню Формат ячеек..., на вкладке Обрамление нажать на кнопку с внешними и внутренними границами, а затем на кнопку OK.
48. Аналогично создать обрамление ячеек листа Сотрудники. Для этого перейти на лист Сотрудники, выделить ячейки с A3 по D8 и задать обрамление как в предыдущем пункте.
49. Аналогично создать обрамление ячеек листа Товары. Для этого перейти на лист Товары, выделить ячейки с A3 по D8 и задать обрамление.
50. Перейти на лист Продажи и сохранить базу данных в файл на диск в формате "Электронная таблица ODF (.ods)", выбрав пункт меню Файл → Сохранить как..., введя имя файла и нажав Сохранить. База данных готова!
Комментариев нет:
Отправить комментарий