Домашнее задание Excel 06.
Построение диаграмм.
Наберите таблицу, согласно предложенному рисунку. Выделите диапазон ячеек A2:E7
Рис. 96. Построение диаграмм.
На панели инструментов Вставка щелкните по кнопке Гистограмма. Выберите первую – Гистограмма с группировкой.
L t1UKDXHTtVBSKC5JzEtJzMnPS7VVqkwtVrK34+UCAAAA//8DAFBLAwQUAAYACAAAACEAzEyiBsQA AADcAAAADwAAAGRycy9kb3ducmV2LnhtbESPTWvCQBCG70L/wzKF3nTT0EhJXUMjFKo9qaXnITsm wexsyG5N6q93DgVvM8z78cyqmFynLjSE1rOB50UCirjytuXawPfxY/4KKkRki51nMvBHAYr1w2yF ufUj7+lyiLWSEA45Gmhi7HOtQ9WQw7DwPbHcTn5wGGUdam0HHCXcdTpNkqV22LI0NNjTpqHqfPh1 0vtVvqTpT1pm5+662eEp663fGvP0OL2/gYo0xbv43/1pBX8ptPKMTKDXNwAAAP//AwBQSwECLQAU AAYACAAAACEA8PeKu/0AAADiAQAAEwAAAAAAAAAAAAAAAAAAAAAAW0NvbnRlbnRfVHlwZXNdLnht bFBLAQItABQABgAIAAAAIQAx3V9h0gAAAI8BAAALAAAAAAAAAAAAAAAAAC4BAABfcmVscy8ucmVs c1BLAQItABQABgAIAAAAIQAzLwWeQQAAADkAAAAQAAAAAAAAAAAAAAAAACkCAABkcnMvc2hhcGV4 bWwueG1sUEsBAi0AFAAGAAgAAAAhAMxMogbEAAAA3AAAAA8AAAAAAAAAAAAAAAAAmAIAAGRycy9k b3ducmV2LnhtbFBLBQYAAAAABAAEAPUAAACJAwAAAAA= " filled="f" strokecolor="red" strokeweight="2pt"/> 
Рис. 97. Вставка диаграммы по данным таблицы.
Для форматирования Диаграммы на вкладке Конструктор выберем Экспресс-макет – Макет 9. Отредактируем заголовки, запишем: Диаграмма, Сумма и Расходы. Для этого достаточно два раза щелкнуть левой кнопкой мыши по соответствующему текстовому полю.
Рис. 98. Для форматирования Диаграммы на вкладке Конструктор.
Замените содержимое ячейки С4 на 750. Проверьте, что при этом на диаграмме автоматически увеличился элемент, отражающий расходы на телефон в феврале.
L t1UKDXHTtVBSKC5JzEtJzMnPS7VVqkwtVrK34+UCAAAA//8DAFBLAwQUAAYACAAAACEAHahzFsQA AADcAAAADwAAAGRycy9kb3ducmV2LnhtbESPQWvCQBCF7wX/wzJCb82mS1NC6ipVEFo9VaXnITsm wexsyK4m7a93BcHbDO/N+97MFqNtxYV63zjW8JqkIIhLZxquNBz265cchA/IBlvHpOGPPCzmk6cZ FsYN/EOXXahEDGFfoIY6hK6Q0pc1WfSJ64ijdnS9xRDXvpKmxyGG21aqNH2XFhuOhBo7WtVUnnZn G7nb5ZtSv2qZndr/1QaPWWfct9bP0/HzA0SgMTzM9+svE+vnCm7PxAnk/AoAAP//AwBQSwECLQAU AAYACAAAACEA8PeKu/0AAADiAQAAEwAAAAAAAAAAAAAAAAAAAAAAW0NvbnRlbnRfVHlwZXNdLnht bFBLAQItABQABgAIAAAAIQAx3V9h0gAAAI8BAAALAAAAAAAAAAAAAAAAAC4BAABfcmVscy8ucmVs c1BLAQItABQABgAIAAAAIQAzLwWeQQAAADkAAAAQAAAAAAAAAAAAAAAAACkCAABkcnMvc2hhcGV4 bWwueG1sUEsBAi0AFAAGAAgAAAAhAB2ocxbEAAAA3AAAAA8AAAAAAAAAAAAAAAAAmAIAAGRycy9k b3ducmV2LnhtbFBLBQYAAAAABAAEAPUAAACJAwAAAAA= " filled="f" strokecolor="red" strokeweight="2pt"/> 
Рис. 99. Автоматический пересчет диаграммы.
Щелчком по диаграмме сделайте ее активной. Выполните команду Выбрать данные. Возникнет окно диалога Выбор источника данных. В таблице выделите диапазон A2:E7.
Рис. 100. Выбор источника данных.
Вызовите контекстное меню для ряда Январь. Для этого щелкните правой кнопкой мыши по столбику, отражающему расходы на квартплату в январе. Выполните команду Удалить. Данные за январь удалятся из диаграммы.
Рис. 101. Редактирование данных в диаграмме.
В ячейку А7 таблицы введите слово Прочие вместо слова Досуг. Удостоверьтесь в том, что в диаграмме изменилось название ряда данных.
Рис. 102. Редактирование текста в диаграмме.
Щелкните правой кнопкой мыши по столбцу, отражающему расходы за март. Откроется контекстное меню,в окне диалога выберите Формат ряда данных. В этом окне выберите Заливка, поставьте маркер Градиентная заливка. Выберите: Название заготовки: Мох; Направление: Вниз.
l AAV3bFkcW8y6ngI+a4F7xfJ0jP5BH47SQf2MS2ESs6KJGY65S8qDOwjT0G4AXCtcTCbJDcfRsnBn 5pZH8FjV2HBP22fmbNeYAVv6Hg5T+ao5W98YaWCyDiBV6tyXunb1xlFOzditnbgrjuXk9bIcx78B AAD//wMAUEsDBBQABgAIAAAAIQDe1rF54AAAAAsBAAAPAAAAZHJzL2Rvd25yZXYueG1sTI/BTsMw EETvSPyDtUjcqINJTAlxKlqJA/REqXp2YzeJaq+j2G0DX89yguNqn2beVIvJO3a2Y+wDKrifZcAs NsH02CrYfr7ezYHFpNFoF9Aq+LIRFvX1VaVLEy74Yc+b1DIKwVhqBV1KQ8l5bDrrdZyFwSL9DmH0 OtE5ttyM+kLh3nGRZZJ73SM1dHqwq842x83JU+96mQuxE8vi6L5X7/pQDCa8KXV7M708A0t2Sn8w /OqTOtTktA8nNJE5BfJRCkIV5E+SRhExf8glsL2CQuQZ8Lri/zfUPwAAAP//AwBQSwECLQAUAAYA CAAAACEAtoM4kv4AAADhAQAAEwAAAAAAAAAAAAAAAAAAAAAAW0NvbnRlbnRfVHlwZXNdLnhtbFBL AQItABQABgAIAAAAIQA4/SH/1gAAAJQBAAALAAAAAAAAAAAAAAAAAC8BAABfcmVscy8ucmVsc1BL AQItABQABgAIAAAAIQCjUW0dkgIAAGQFAAAOAAAAAAAAAAAAAAAAAC4CAABkcnMvZTJvRG9jLnht bFBLAQItABQABgAIAAAAIQDe1rF54AAAAAsBAAAPAAAAAAAAAAAAAAAAAOwEAABkcnMvZG93bnJl di54bWxQSwUGAAAAAAQABADzAAAA+QUAAAAA " filled="f" strokecolor="red" strokeweight="2pt"/> 
Рис. 103. Градиентная заливка в диаграмме.
Щелкните правой кнопкой мыши по вертикальной оси, возникнет контекстное меню, выберите Формат оси…. Установите минимальное значение шкалы - 0, цену основных делений - 150 рублей.
L t1UKDXHTtVBSKC5JzEtJzMnPS7VVqkwtVrK34+UCAAAA//8DAFBLAwQUAAYACAAAACEADd/QjsQA AADcAAAADwAAAGRycy9kb3ducmV2LnhtbESPT4vCMBDF74LfIYzgbU0t6yq1UVZhwdWTf/A8NGNb 2kxKk9Xqp98IgrcZ3pv3e5MuO1OLK7WutKxgPIpAEGdWl5wrOB1/PmYgnEfWWFsmBXdysFz0eykm 2t54T9eDz0UIYZeggsL7JpHSZQUZdCPbEAftYluDPqxtLnWLtxBuahlH0Zc0WHIgFNjQuqCsOvyZ wN2tPuP4HK8mVf1Yb/EyabT9VWo46L7nIDx1/m1+XW90qD+bwvOZMIFc/AMAAP//AwBQSwECLQAU AAYACAAAACEA8PeKu/0AAADiAQAAEwAAAAAAAAAAAAAAAAAAAAAAW0NvbnRlbnRfVHlwZXNdLnht bFBLAQItABQABgAIAAAAIQAx3V9h0gAAAI8BAAALAAAAAAAAAAAAAAAAAC4BAABfcmVscy8ucmVs c1BLAQItABQABgAIAAAAIQAzLwWeQQAAADkAAAAQAAAAAAAAAAAAAAAAACkCAABkcnMvc2hhcGV4 bWwueG1sUEsBAi0AFAAGAAgAAAAhAA3f0I7EAAAA3AAAAA8AAAAAAAAAAAAAAAAAmAIAAGRycy9k b3ducmV2LnhtbFBLBQYAAAAABAAEAPUAAACJAwAAAAA= " filled="f" strokecolor="red" strokeweight="2pt"/> 
Рис. 104. Установка диапазона значений оси в диаграмме
Щелкните по Диаграмме – сделайте ее активной. В закладке Конструктор щелкните левой кнопкой мыши по Изменить тип диаграммы. Возникнет окно диалога, гдевыберите тип - Круговая и нажмите кнопку ОК. Вид диаграммы изменится.
Рис. 105. Изменить тип диаграммы.
Представить Диаграмму преподавателю.
Основные функции
Введите в ячейку А2 Продажи: январь – март. Нажмите < Enter >. Активируйте ячейку А4 и введите текст: Макароны и лапша. Подтвердите ввод, нажав клавишу < Enter >. Аналогично в ячейках A4 – D10 заполните таблицу, представленную на рисунке.
Рис. 106. Формирование данных таблицы.
Между строками 4 и 5 добавьте две строки. В ячейку A6 введите слово Артикул. Активируйте ячейку В6. Заполните ячейки по месяцам до D6, как показано на рисунке.
Рис. 107. Редактирование данных таблицы.
Активируйте ячейку А10 и введите в нее текст Вермишель, после чего нажмите <Таb>. Установите указатель мыши на линии, разделяющей столбцы А и В в их верхней части. Нажмите и, удерживая нажатой левую кнопку мыши, переместите мышь вправо на расстояние, обеспечивающее полную видимость слова Вермишель. В ячейку А13 введите слово Сумма и нажмите < Таb >.
L t1UKDXHTtVBSKC5JzEtJzMnPS7VVqkwtVrK34+UCAAAA//8DAFBLAwQUAAYACAAAACEAICMVnMIA AADcAAAADwAAAGRycy9kb3ducmV2LnhtbESPS4vCMBSF9wP+h3AFd2Nq0EGrUVQQHGflA9eX5toW m5vSRK3++okwMMvDeXyc2aK1lbhT40vHGgb9BARx5kzJuYbTcfM5BuEDssHKMWl4kofFvPMxw9S4 B+/pfgi5iCPsU9RQhFCnUvqsIIu+72ri6F1cYzFE2eTSNPiI47aSKkm+pMWSI6HAmtYFZdfDzUbu z2qo1FmtRtfqtd7hZVQb9611r9supyACteE//NfeGg1qMIH3mXgE5PwXAAD//wMAUEsBAi0AFAAG AAgAAAAhAPD3irv9AAAA4gEAABMAAAAAAAAAAAAAAAAAAAAAAFtDb250ZW50X1R5cGVzXS54bWxQ SwECLQAUAAYACAAAACEAMd1fYdIAAACPAQAACwAAAAAAAAAAAAAAAAAuAQAAX3JlbHMvLnJlbHNQ SwECLQAUAAYACAAAACEAMy8FnkEAAAA5AAAAEAAAAAAAAAAAAAAAAAApAgAAZHJzL3NoYXBleG1s LnhtbFBLAQItABQABgAIAAAAIQAgIxWcwgAAANwAAAAPAAAAAAAAAAAAAAAAAJgCAABkcnMvZG93 bnJldi54bWxQSwUGAAAAAAQABAD1AAAAhwMAAAAA " filled="f" strokecolor="red" strokeweight="2pt"/> 
Рис. 108. Редактирование столбцов с данными в таблицах.
Активируйте столбец С и измените ширину столбца таблицы, чтобы привести ее в соответствие с длиной содержащихся в ней данных. Для этого, выполните команды меню Формат - Автоподбор ширины столбца.
Рис. 109. Автоподбор ширины столбца.
Выделите ячейку А6. Обратите внимание, что текст выравнивается по левому краю. Выделите ячейку В7. Числа выравниваются по правому краю. Выделите ячейки с названиями месяцев B6..D6. Для этого активируйте ячейку В6. Удерживая нажатой клавишу < Shift >, щелкните ячейку D6. Щелкните кнопку Выровнять по центру из списка кнопок Выравнивания.
L t1UKDXHTtVBSKC5JzEtJzMnPS7VVqkwtVrK34+UCAAAA//8DAFBLAwQUAAYACAAAACEAn9BLU8MA AADcAAAADwAAAGRycy9kb3ducmV2LnhtbESPX2vCMBTF3wd+h3AF39bUYMuojaLCYG5Pc+Lzpbm2 xeamNFHrPv0yGOzxcP78OOV6tJ240eBbxxrmSQqCuHKm5VrD8ev1+QWED8gGO8ek4UEe1qvJU4mF cXf+pNsh1CKOsC9QQxNCX0jpq4Ys+sT1xNE7u8FiiHKopRnwHsdtJ1Wa5tJiy5HQYE+7hqrL4Woj 92O7UOqkttml+9694znrjdtrPZuOmyWIQGP4D/+134wGpXL4PROPgFz9AAAA//8DAFBLAQItABQA BgAIAAAAIQDw94q7/QAAAOIBAAATAAAAAAAAAAAAAAAAAAAAAABbQ29udGVudF9UeXBlc10ueG1s UEsBAi0AFAAGAAgAAAAhADHdX2HSAAAAjwEAAAsAAAAAAAAAAAAAAAAALgEAAF9yZWxzLy5yZWxz UEsBAi0AFAAGAAgAAAAhADMvBZ5BAAAAOQAAABAAAAAAAAAAAAAAAAAAKQIAAGRycy9zaGFwZXht bC54bWxQSwECLQAUAAYACAAAACEAn9BLU8MAAADcAAAADwAAAAAAAAAAAAAAAACYAgAAZHJzL2Rv d25yZXYueG1sUEsFBgAAAAAEAAQA9QAAAIgDAAAAAA== " filled="f" strokecolor="red" strokeweight="2pt"/> 
Рис. 110. Выравнивание по центру.
Выделите ячейку А13 и выполните команды меню Главная - Копировать. Активируйте ячейку Е6 и выполните команды меню Главная - Вставить. Активируйте ячейку В13 и введите в нее формулу =В7+В8+В9+В10+В11+В12 (В - английская) и нажмите < Таb >.
Рис. 111. Копирование данных в таблице.
Измените значение продажи спагетти за январь с 9876 на 198 и нажмите < Таb >. Сравните свой результат с рисунком. Активируйте ячейку С13. наберите с клавиатуры формулу =СУММ(С7:С12) и нажмите < Таb >. В ячейке С13 появится значение продаж за февраль. Проверьте результат.
Рис. 112. Расчет суммы данных столбца в таблице.
Активируйте ячейку Е7 и вычислите сумму продаж лапши за три месяца с помощью функции суммы.
Рис. 113. Расчет суммы данных строки в таблице.
Заполните все оставшиеся ячейки Сумма по строкам и столбцам любым удобным вам способом.
Рис. 114. Копирование команд по строкам и столбцам в таблице.
Активируйте ячейку С14, введите в нее формулу =ЕСЛИ(С13>В13; "выше"; "ниже). Она означает, что если продажа за февраль больше, чем за январь, то появляется слово выше, иначе ниже. Скопируйте формулу из ячейки С14 в ячейку D14.
Рис. 115. Применение логических команд по строкам и столбцам в таблице.
Измените, данные в ячейке С11 на 0 и нажмите < Таb >. Измените данные в ячейке С12 на 0 и нажмите < Таb >. Обратите внимание, как изменилось значение в ячейке С14. В ячейку Е13 скопируйте формулу из D13.
Рис. 116. Автоматический перерасчет данных по строкам и столбцам в таблице.
Активируйте строку 7. Вставьте новую строку с использованием контекстного меню. В ячейку F6 введите текст: % и нажмите < Enter >. В ячейку F7 введите текст: суммы и нажмите < Таb >.
Рис. 117. Использование контекстного меню при редактировании таблицы.
Активируйте ячейку F8 и введите в нее формулу для вычисления процентного вклада продажи лапши в общий доход =Е8*100/$Е$14 и нажмите < Tab >. Проверьте результат Вашей работы. Выделите ячейки F8..F14 и щелкните по пиктограмме суммы. Если вычисления проведены правильно, в ячейке F14 появится сумма, равная 100%.
Рис. 118. Оценка процентного вклада продажи.
Щелкните правой кнопкой мыши по корешку Лист 1. В контекстном меню выполните команду Переименовать. Введите с клавиатуры новое имя листа → Продажи. Нажмите клавишу < Enter >. Выделите область таблицы A4:F15. Выполните команду меню Главная → Форматировать как таблицу. Выбрать Стиль таблицы средний 3. Появится окно Создание таблицы. Заполните соответствующее окно.
y vgRq65DOipcZJGWMdgdqhPqRbsIkeSWWcJJ8l1xG3CGXsT0AdFWkmkyyGG2jF/Ha3XuZjKeqpoF7 WD8K9N1gRproG9gt5YvhbGWTpoPJMoI2eXKf69rVmzY5D2N3ddKp2Mez1PNtHP8GAAD//wMAUEsD BBQABgAIAAAAIQDf5n+M3wAAAAsBAAAPAAAAZHJzL2Rvd25yZXYueG1sTI/LTsMwEEX3SPyDNUjs qBOTBBLiVLQSi8KKgli78TSJ6kcUu23o1zOsYDl3ju6jXs7WsBNOYfBOQrpIgKFrvR5cJ+Hz4+Xu EViIymllvEMJ3xhg2Vxf1arS/uze8bSNHSMTFyoloY9xrDgPbY9WhYUf0dFv7yerIp1Tx/WkzmRu DRdJUnCrBkcJvRpx3WN72B4t5b6tMiG+xCo/mMv6Ve3zUfuNlLc38/MTsIhz/IPhtz5Vh4Y67fzR 6cCMhLwsM0IliKKkUUQUDykpOwn3WZoAb2r+f0PzAwAA//8DAFBLAQItABQABgAIAAAAIQC2gziS /gAAAOEBAAATAAAAAAAAAAAAAAAAAAAAAABbQ29udGVudF9UeXBlc10ueG1sUEsBAi0AFAAGAAgA AAAhADj9If/WAAAAlAEAAAsAAAAAAAAAAAAAAAAALwEAAF9yZWxzLy5yZWxzUEsBAi0AFAAGAAgA AAAhAHShb3WJAgAAYwUAAA4AAAAAAAAAAAAAAAAALgIAAGRycy9lMm9Eb2MueG1sUEsBAi0AFAAG AAgAAAAhAN/mf4zfAAAACwEAAA8AAAAAAAAAAAAAAAAA4wQAAGRycy9kb3ducmV2LnhtbFBLBQYA AAAABAAEAPMAAADvBQAAAAA= " filled="f" strokecolor="red" strokeweight="2pt"/> 
Рис. 119. Форматировать стиль как таблицы.
В ячейках А2 и А4 установите формат текста шрифт Arial, размер шрифта 14. Выделите ячейки A2:F2 заголовка, и щелкните по кнопке меню Главная → Объединить и поместить в центре. Аналогично для ячеек A4:F4.
Рис. 120. Команда Объединить и поместить в центре.
Выделите область таблицы A9:D15 и щелкните по кнопке Диаграмма → Объемная диаграмма с группировкой.
Рис. 121. Диаграмма Объемная диаграмма с группировкой.
Диаграмма при необходимости может быть дополнительно оформлена. Для этого выделите область диаграммы в меню Работа с диаграммами → Конструктор → Макеты диаграмм → Макет 8. Для улучшения информационной наглядности можно подобрать другой тип диаграммы, воспользовавшись кнопкой Изменить тип диаграммы.
Рис. 122. Диаграмма. Команда Изменить тип диаграммы.
Рис. 123. Результат изменения типа диаграммы.
Результат направить преподавателю.
Задача: «Прогнозирование прибыли»
В ячейку А1 введите заголовок: Закупка и нажмите < Enter >. В ячейку D1 введите заголовок: Реализация.
L t1UKDXHTtVBSKC5JzEtJzMnPS7VVqkwtVrK34+UCAAAA//8DAFBLAwQUAAYACAAAACEAdUV4FcMA AADcAAAADwAAAGRycy9kb3ducmV2LnhtbESPX2vCMBTF3wd+h3AF3zQ1zm10RlFB0Pm0Tny+NNe2 2NyUJmrdpzcDYY+H8+fHmS06W4srtb5yrGE8SkAQ585UXGg4/GyGHyB8QDZYOyYNd/KwmPdeZpga d+NvumahEHGEfYoayhCaVEqfl2TRj1xDHL2Tay2GKNtCmhZvcdzWUiXJm7RYcSSU2NC6pPycXWzk 7levSh3Vanquf9dfeJo2xu20HvS75SeIQF34Dz/bW6NBTd7h70w8AnL+AAAA//8DAFBLAQItABQA BgAIAAAAIQDw94q7/QAAAOIBAAATAAAAAAAAAAAAAAAAAAAAAABbQ29udGVudF9UeXBlc10ueG1s UEsBAi0AFAAGAAgAAAAhADHdX2HSAAAAjwEAAAsAAAAAAAAAAAAAAAAALgEAAF9yZWxzLy5yZWxz UEsBAi0AFAAGAAgAAAAhADMvBZ5BAAAAOQAAABAAAAAAAAAAAAAAAAAAKQIAAGRycy9zaGFwZXht bC54bWxQSwECLQAUAAYACAAAACEAdUV4FcMAAADcAAAADwAAAAAAAAAAAAAAAACYAgAAZHJzL2Rv d25yZXYueG1sUEsFBgAAAAAEAAQA9QAAAIgDAAAAAA== " filled="f" strokecolor="red" strokeweight="2pt"/> 
Рис. 124. Подготовка таблицы расчета.
Пользуясь командой меню Формат ячеек, установите в первой строке начертание шрифта Полужирный, размер - 16 и перейдите на вкладку Граница.
L t1UKDXHTtVBSKC5JzEtJzMnPS7VVqkwtVrK34+UCAAAA//8DAFBLAwQUAAYACAAAACEAa5ZJ/MMA AADcAAAADwAAAGRycy9kb3ducmV2LnhtbESPX2vCMBTF3wd+h3AF3zQ1zrF1RlFB0Pm0Tny+NNe2 2NyUJmrdpzcDYY+H8+fHmS06W4srtb5yrGE8SkAQ585UXGg4/GyG7yB8QDZYOyYNd/KwmPdeZpga d+NvumahEHGEfYoayhCaVEqfl2TRj1xDHL2Tay2GKNtCmhZvcdzWUiXJm7RYcSSU2NC6pPycXWzk 7levSh3Vanquf9dfeJo2xu20HvS75SeIQF34Dz/bW6NBTT7g70w8AnL+AAAA//8DAFBLAQItABQA BgAIAAAAIQDw94q7/QAAAOIBAAATAAAAAAAAAAAAAAAAAAAAAABbQ29udGVudF9UeXBlc10ueG1s UEsBAi0AFAAGAAgAAAAhADHdX2HSAAAAjwEAAAsAAAAAAAAAAAAAAAAALgEAAF9yZWxzLy5yZWxz UEsBAi0AFAAGAAgAAAAhADMvBZ5BAAAAOQAAABAAAAAAAAAAAAAAAAAAKQIAAGRycy9zaGFwZXht bC54bWxQSwECLQAUAAYACAAAACEAa5ZJ/MMAAADcAAAADwAAAAAAAAAAAAAAAACYAgAAZHJzL2Rv d25yZXYueG1sUEsFBgAAAAAEAAQA9QAAAIgDAAAAAA== " filled="f" strokecolor="red" strokeweight="2pt"/> 
Рис. 125. Подготовка Формата ячеек таблицы расчета.
Установите обрамление двойной линией нижней границы строки и нажмите кнопку ОК.
Рис. 126. Подготовка Формата граница таблицы.
Осуществите автоподбор ширины столбцов А и D. Для этого выделите столбец А и, удерживая Ctrl, щелкните на столбец D. Затем выполните команду Формат ячеек - Выравнивание - Автоподбор ширины.
Рис. 127. Подготовка команды Автоподбор ширины таблицы.
В ячейки А2, A3, А4, соответственно, введите наименования: Количество, Цена, Стоимость. В ячейки D2, D3, D4, D5, D7. соответственно, введите наименования: Наценка, Доход. Расход, Зарплата, Прибыль.
В ячейку В2 введите число 30, а в ячейку Е2 - 0,15. Сделайте ячейку ВЗ активной.
В ячейку ВЗ введите следующую формулу: =10000*В2 /(В2-5)
В ячейку В4 - введите формулу =В2*ВЗ
Сделайте ячейку ЕЗ активной. Введите в нее следующую формулу: =В4*Е2
В ячейку Е4 - введите формулу =В4/(150-В2)
В ячейку Е5 - введите формулу =1000*В2. Убедитесь, что в ячейках ВЗ, В4, ЕЗ, Е4, Е5 возникли результаты вычислений соответственно:
12 000, 360 000, 54 000, 3 000, 30 000
Для присвоения имени ячейке Е3 активируйте ее, щелкните правой клавишей мыши, в окне диалога выберите Имя диапазона.
Рис. 128. Редактирование таблицы.
Присвойте имя Доход. В окне нажмите ОК. Аналогично ячейке Е4 присвойте имя Расход. Выделите ячейку Е5, откройте вкладку и напишите имя диапазона Зарплата.
Рис. 129. Присвоение имени ячейке таблицы.
Активируйте ячейку Е7 и наберите в ней знак равенства =. Щелкните на ячейке ЕЗ с именем Доход, и название этой ячейки скопируется в ячейку Е7. Наберите арифметический знак минус - и щелкните на ячейке Е4 с именем Расход. Это имя введется в ячейку Е7. Наберите арифметический знак минус - и щелкните на ячейке Е5 с именем Зарплата. Завершите ввод формулы, нажав клавишу <Enter>. В ячейке Е7 возникнет результат - 21 000.
Сделайте активной ячейку Е7 и убедитесь, что в Строке формул расположена формула, использующая не ссылки, а имена ячеек. Этим обеспечивается использование в формуле абсолютной адресации ячеек (при копировании формулы адреса ячеек не меняются). Во вкладке Формулы щелкните вкладку Параметры вычислений и убедитесь, что для режима вычисления установлен переключатель автоматически.
Рис. 130. Запись формулы с использованием имени ячейки таблицы.
Для проверки правильности решения задачи программой Excel введите в ячейку В2 число 50 и убедитесь, что программа произвела перерасчет по формулам с новыми данными и в ячейке Е7 возник результат 7777.78. Таким образом, можно прогнозировать результат при изменении любого параметра, входящего в расчеты.
/OL 2CruWLM41ph1fQ34rAWuFcsTGe2DPpDSQf2MO2EWs6KKGY65S8qDOzDXoV0AuFW4mM2SGU6jZeHW PFoeg8eqxoZ72j4zZ7vGDNjRd3AYylfN2dpGTwOzdQCpUue+1LWrN05yasZu68RVccwnq5fdOP0N AAD//wMAUEsDBBQABgAIAAAAIQBokv2s3wAAAAsBAAAPAAAAZHJzL2Rvd25yZXYueG1sTI/BTsMw EETvSPyDtUjcqBOnrZoQp6KVOAAnCup5G7tJ1HgdxW4b+HqWE9x2tKOZN+V6cr242DF0njSkswSE pdqbjhoNnx/PDysQISIZ7D1ZDV82wLq6vSmxMP5K7/ayi43gEAoFamhjHAopQ91ah2HmB0v8O/rR YWQ5NtKMeOVw10uVJEvpsCNuaHGw29bWp93Zce/bZq7UXm0Wp/57+4rHxWD8i9b3d9PTI4hop/hn hl98RoeKmQ7+TCaInvUyZ/SoQaV5BoIdKk953YGPeZaBrEr5f0P1AwAA//8DAFBLAQItABQABgAI AAAAIQC2gziS/gAAAOEBAAATAAAAAAAAAAAAAAAAAAAAAABbQ29udGVudF9UeXBlc10ueG1sUEsB Ai0AFAAGAAgAAAAhADj9If/WAAAAlAEAAAsAAAAAAAAAAAAAAAAALwEAAF9yZWxzLy5yZWxzUEsB Ai0AFAAGAAgAAAAhAGmnJT6SAgAAYwUAAA4AAAAAAAAAAAAAAAAALgIAAGRycy9lMm9Eb2MueG1s UEsBAi0AFAAGAAgAAAAhAGiS/azfAAAACwEAAA8AAAAAAAAAAAAAAAAA7AQAAGRycy9kb3ducmV2 LnhtbFBLBQYAAAAABAAEAPMAAAD4BQAAAAA= " filled="f" strokecolor="red" strokeweight="2pt"/> 
Рис. 131. Перерасчет по формулам с новыми данными таблицы.
В ячейках ВЗ, В4, ЕЗ, Е4, Е5, Е7 - установите формат числа Денежный.
Рис. 132. Установка формата числа Денежный данным из таблицы.
В ячейке Е2 - Процентный (Формат -Ячейки).
Рис. 133. Установка формата числа Процентный данным из таблицы.
Установите ширину столбцов В и Е - 12. Для этого выделите столбец В и, удерживая клавишу < Ctrl >, щелкните на столбец Е. Затем щелкните правой клавишей мышки и выберите Ширина столбца - 12.
Рис. 134. Установка ширины столбцов данных из таблицы.
В ячейки А10 и А11 введите числа 10 и 20 соответственно. При помощи автозаполнения введите ячейки А12:А19 последовательность чисел от 30 до 100 через 10. Для этого выделите диапазон ячеек А10:А11. Установите курсор в правом нижнем углу выделенного фрагмента, нажмите левую клавишу мыши и, удерживая ее нажатой, протяните вниз до ячейки А19.
Рис. 135. Редактирование данных из таблицы.
В ячейку В9 скопируйте формулу из ячейки Е7. Выделите диапазон ячеек А9:В19 и исполните команду меню Данные Анализ «если что» - Таблица данных. Возникнет окно диалога.
L t1UKDXHTtVBSKC5JzEtJzMnPS7VVqkwtVrK34+UCAAAA//8DAFBLAwQUAAYACAAAACEA16GbtsMA AADcAAAADwAAAGRycy9kb3ducmV2LnhtbESPX2vCMBTF3wd+h3AF32ZqXEVqo6gguO1JN3y+NNe2 tLkpTdRun34ZDPZ4OH9+nHwz2Fbcqfe1Yw2zaQKCuHCm5lLD58fheQnCB2SDrWPS8EUeNuvRU46Z cQ8+0f0cShFH2GeooQqhy6T0RUUW/dR1xNG7ut5iiLIvpenxEcdtK1WSLKTFmiOhwo72FRXN+WYj 9333otRF7dKm/d6/4TXtjHvVejIetisQgYbwH/5rH40Glc7h90w8AnL9AwAA//8DAFBLAQItABQA BgAIAAAAIQDw94q7/QAAAOIBAAATAAAAAAAAAAAAAAAAAAAAAABbQ29udGVudF9UeXBlc10ueG1s UEsBAi0AFAAGAAgAAAAhADHdX2HSAAAAjwEAAAsAAAAAAAAAAAAAAAAALgEAAF9yZWxzLy5yZWxz UEsBAi0AFAAGAAgAAAAhADMvBZ5BAAAAOQAAABAAAAAAAAAAAAAAAAAAKQIAAGRycy9zaGFwZXht bC54bWxQSwECLQAUAAYACAAAACEA16GbtsMAAADcAAAADwAAAAAAAAAAAAAAAACYAgAAZHJzL2Rv d25yZXYueG1sUEsFBgAAAAAEAAQA9QAAAIgDAAAAAA== " filled="f" strokecolor="red" strokeweight="2pt"/> 
Рис. 136. Анализ данных из таблицы.
В окне диалога Таблица подстановки щелкните мышью в текстовом поле Подставлять значения по строкам в: и щелкните по ячейке В2 мышью. В текстовом поле возникнет абсолютный адрес этой ячейки. Щелкните ОК.
Рис. 137. Подстановка значения для Анализа расчета данных.
В ячейках В10:В19 возникли значения величины прибыли в зависимости от количества проданных изделий. Таким образом, можно прогнозировать результат в наглядном табличном виде при изменении одного параметра. Проверьте правильность Ваших действий: для 60 изделий прибыль должна составлять 90909.09.... Оформите таблицу, активируйте ячейку А8, наберите =А2 и нажмите клавишу < Ente r> (текст из ячейки А2 запишется в ячейку А8). Аналогичным образом запишите в ячейку В8 текст из ячейки D7.
Рис. 138. Оформление таблицы данных.
Сделайте обрамление более толстой линией, а внутренние линии тонкими.
Рис. 139. Оформление Границы таблицы данных.
Измените цвет шрифта на зеленый.
Рис. 140. Изменение цвета шрифта таблицы данных.
В ячейку D9 скопируйте формулу из ячейки Е7. В ячейки D10:D19 скопируйте данные из ячеек А10:А19.
Рис. 141. Изменение данных таблицы.
В ячейки Е9 и F9 введите числа – 0,1 и 0,15 соответственно.
Рис. 142. Редактирование данных таблицы.
Установите в них формат числа - Процентный.
Рис. 143. Редактирование формата данных таблицы.
При помощи опции автозаполнение заполните ячейки G9:K9 значениями наценки до 40% через 5%. Для передвижения экрана вправо щелкните на горизонтальной полосе прокрутки. Выделите диапазон ячеек D9:K19 и исполните команду меню Данные → Анализ «если что» → Таблица данных. Возникнет окно диалога. В окне диалога Таблица подстановки щелкните мышью по текстовому полю Подставлять значения по столбцам в:. Щелкните мышью по ячейке Е2. В текстовом поле возникнет абсолютный адрес этой ячейки. Перейдите в поле Подставлять значения по строкам в: Щелкните мышью по ячейке В2. В текстовом поле возникнет абсолютный адрес этой ячейки. Затем нажмите ОК.
Рис. 144. Редактирование формата данных таблицы.
В ячейках Е10:К19 возникнут значения величины прибыли в зависимости от количества проданных изделий и величины наценки. Таким образом, можно прогнозировать результат в наглядном табличном виде при изменении двух параметров.
Проверьте правильность Ваших действий: для 50 изделий при наценке 20% прибыль должна составлять 1055555,56. Установите обрамление в ячейках последней созданной таблицы данных по аналогии с диапазоном А8:В19.
Рис. 145. Анализ данных таблицы.
Активируйте ячейку D9. Измените формулу на =Доход-Расход-Зарплата +1. Завершите ввод формулы.
Рис. 146. Изменение 1 и Анализ данных таблицы.
В ячейке К9 замените 40% на 50%. После ввода нового значения автоматически произойдет перерасчет таблицы данных в столбце К. В ячейку Е2 введите величину наценки - 20%.
Рис. 147. Изменение 2 и Анализ данных таблицы.
Для установленной величины наценки необходимо определить количество изделий, обеспечивающее прибыль - 50 000. Для чего активируйте ячейку Е7 и выполните команду меню Анализ «если что» - Подбор параметра. Возникнет окно диалога.
L t1UKDXHTtVBSKC5JzEtJzMnPS7VVqkwtVrK34+UCAAAA//8DAFBLAwQUAAYACAAAACEACbryk8QA AADcAAAADwAAAGRycy9kb3ducmV2LnhtbESPS2vCQBSF90L/w3AL7nTSwQSJjsEIhT5W1dL1JXNN gpk7ITM1qb/eKRS6PJzHx9kWk+3ElQbfOtbwtExAEFfOtFxr+Dw9L9YgfEA22DkmDT/kodg9zLaY GzfyB12PoRZxhH2OGpoQ+lxKXzVk0S9dTxy9sxsshiiHWpoBxzhuO6mSJJMWW46EBns6NFRdjt82 ct/LlVJfqkwv3e3whue0N+5V6/njtN+ACDSF//Bf+8VoUFkGv2fiEZC7OwAAAP//AwBQSwECLQAU AAYACAAAACEA8PeKu/0AAADiAQAAEwAAAAAAAAAAAAAAAAAAAAAAW0NvbnRlbnRfVHlwZXNdLnht bFBLAQItABQABgAIAAAAIQAx3V9h0gAAAI8BAAALAAAAAAAAAAAAAAAAAC4BAABfcmVscy8ucmVs c1BLAQItABQABgAIAAAAIQAzLwWeQQAAADkAAAAQAAAAAAAAAAAAAAAAACkCAABkcnMvc2hhcGV4 bWwueG1sUEsBAi0AFAAGAAgAAAAhAAm68pPEAAAA3AAAAA8AAAAAAAAAAAAAAAAAmAIAAGRycy9k b3ducmV2LnhtbFBLBQYAAAAABAAEAPUAAACJAwAAAAA= " filled="f" strokecolor="red" strokeweight="2pt"/> 
Рис. 148. Изменение 3 и Анализ данных таблицы.
Щелкните ячейку В2. В текстовом поле возникнет абсолютный адрес этой ячейки. В окне диалога Подбор параметра нажмите кнопку ОК.
Рис. 149. Изменение 4 и Анализ данных таблицы.
Результат можно увидеть в окне Результат подбора параметра.
Рис. 150. Анализ результата данных таблицы.
Представить работу преподавателю
Работа с базами данных.
Дважды щёлкните мышью в нижней части экрана по названию текущего рабочего листа. Название листа выделится. Введите имя: База данных - и щёлкните мышью вне поля имени листа. Во второй строке рабочего листа с ячейки А2 до ячейки F2 введите следующие имена полей: Название, Цена, Тип, Количество, Дата, № склада. В третьей строке рабочего листа с ячейки A3 до ячейки F3 введите следующие данные в соответствующие поля: Маска, 120, комедия, 140, 03.мар, 1. Как показано выше.
Выделите диапазон ячеек A2:F3. Для этого щёлкните мышью по ячейке А2. нажмите < Shift > и не отпуская его щёлкните на ячейке F3. Исполните команды меню: Формулы – Присвоить имя – Присвоить имя Возникнет окно Создание имени. В поле Имя введите имя: Склад - и щёлкните ОК.
Рис. 151. Построение таблицы реляционной Базы данных.
Как правило, по умолчанию в Excel 2010 кнопка Форма не установлена. Установим ее. Щелкните левой кнопкой мыши по кнопке office, откроется панель. В самом низу есть кнопка Параметры Excel, щелкните по ней. Появится панель Параметры Excel. Выберите команду Настройки. В списке Выбрать команды из выберите пункт Все команды. Найдите команду Форма, щелкните по ней, а затем по кнопке Добавить. Команда Форма добавится в список Настройка панели быстрого доступа. Проверьте чтобы под названием списка было выбрано Для всех документов. Щелкните ОК. В панели быстрого доступа появится кнопка Форма.
Рис. 152. Активация инструмента Форма Базы данных.
Исполните команду меню из панели быстрого доступа - Форма. Возникнет окно с названием текущего рабочего листа – База данных, в котором против соответствующих полей располагаются введённые Вами данные.
Щёлкните по кнопке Добавить, возникнут пустые поля данных. Введите следующие данные в соответствующие поля согласно рисунку и установите необходимый формат в ячейках.
L t1UKDXHTtVBSKC5JzEtJzMnPS7VVqkwtVrK34+UCAAAA//8DAFBLAwQUAAYACAAAACEA1vqx8sIA AADcAAAADwAAAGRycy9kb3ducmV2LnhtbESPS4vCMBSF98L8h3AH3GlqUEeqUVQQfKx0hllfmmtb bG5KE7Uzv94IgsvDeXyc2aK1lbhR40vHGgb9BARx5kzJuYaf701vAsIHZIOVY9LwRx4W84/ODFPj 7nyk2ynkIo6wT1FDEUKdSumzgiz6vquJo3d2jcUQZZNL0+A9jttKqiQZS4slR0KBNa0Lyi6nq43c w2qo1K9ajS7V/3qP51Ft3E7r7me7nIII1IZ3+NXeGg1q8gXPM/EIyPkDAAD//wMAUEsBAi0AFAAG AAgAAAAhAPD3irv9AAAA4gEAABMAAAAAAAAAAAAAAAAAAAAAAFtDb250ZW50X1R5cGVzXS54bWxQ SwECLQAUAAYACAAAACEAMd1fYdIAAACPAQAACwAAAAAAAAAAAAAAAAAuAQAAX3JlbHMvLnJlbHNQ SwECLQAUAAYACAAAACEAMy8FnkEAAAA5AAAAEAAAAAAAAAAAAAAAAAApAgAAZHJzL3NoYXBleG1s LnhtbFBLAQItABQABgAIAAAAIQDW+rHywgAAANwAAAAPAAAAAAAAAAAAAAAAAJgCAABkcnMvZG93 bnJldi54bWxQSwUGAAAAAAQABAD1AAAAhwMAAAAA " filled="f" strokecolor="red" strokeweight="2pt"/> 
Рис. 153. Ввод данных в Базу данных с помощью инструмента Форма.
Скопируйте таблицу, представленную ниже, в ваш рабочий лист Excel.
| Название | Цена | Тип | Количество | Дата | № склада |
| Маска | 120,00р. | комедия | 03.мар | ||
| Тотальная слежка | 90,00р. | боевик | 04.мар | ||
| Тумстоун | 100,00р. | вестерн | 15.мар | ||
| Левиафан | 80,00р. | фантастика | 06.апр | ||
| Профессионал | 100,00р. | боевик | 12.мар | ||
| Пятый элемент | 120,00р. | фантастика | 13.мар | ||
| Марс атакует | 100,00р. | фантастика | 20.мар | ||
| Ягуар | 90,00р. | приключения | 10.мар | ||
| Захват-2 | 90,00р. | боевик | 21.мар | ||
| Вор | 100,00р. | мелодрама | 02.мар | ||
| Завтра не умрет | 90,00р. | боевик | 13.мар | ||
| Титаник | 150,00р. | мелодрама | 10.мар | ||
| Не говори никогда | 110,00р. | боевик | 19.мар | ||
| Мужчины в черном | 110,00р. | фантастика | 06.апр | ||
| Она прекрасна | 90,00р. | мелодрама | 17.мар | ||
| Прибытие | 100,00р. | фантастика | 28.мар | ||
| Рубин Каира | 110,00р. | триллер | 19.мар | ||
| Дьявольщина | 100,00р. | триллер | 20.мар | ||
| Джекнайф | 90,00р. | мелодрама | 11.мар | ||
| Боль любви | 90,00р. | триллер | 20.мар |
Исполните команду меню – Форма, щёлкая по кнопке Далее убедитесь, что в возникшем окне доступны вставленные данные. Щёлкните по кнопке Закрыть.
Рис. 154. Контроль данных в Базе данных с помощью инструмента Форма.
Сделайте активной ячейку А2 и исполните команды меню Данные – Сортировка. Возникнет окно Сортировка. Установите в поле Сортировать по из списка № склада и щелкните по кнопке Добавить уровень. Аналогичным образом установите во втором поле Затем по - Цена и третьем поле - Количество. При этом в первом поле установите режим - По возрастанию, а вo втором и третьем полях окна - По убыванию и щёлкните по кнопке ОК.
Рис. 155. Сортировка данных в Базе данных.
Убедитесь, что данные располагаются в установленном Вами порядке. Исполните команду меню - Форма и в появившемся окне щёлкните кнопку Критерии. Над кнопками окна возникнет надпись Критерии.
Рис. 156. Формирование Критерия Сортировки данных в Базе данных.
В базе данных найдем видеофильмы, поступившие на склад № 2 в апреле, для этого в поле Дата введите условие: >31.03; в поле № склада введите - 2 и щёлкните по кнопке Далее. Щёлкая по кнопкам Далее и Назад убедитесь, что таким условиям отвечают два фильма. Щёлкните по кнопке Закрыть.
Исполните команды меню Данные - Фильтр. Справа от имени каждого поля в таблице появится кнопка раскрытия списка. В базе данных найдите видеофильмы, которые стоят более 120 р., для чего щёлкните кнопку раскрытия списка в поле Цена и щёлкните команду Числовые фильтры. Возникнет окно Пользовательский автофильтр. Войдите в правое верхнее окно, наберите цену - 120 и щёлкните кнопку ОК. В таблице останутся только данные о трех фильмах, удовлетворяющие заданному условию.
Рис. 157. Формирование Пользовательского автофильтра.
Нажмите на кнопку раскрытия списка в поле Цена. Поставьте маркер Выделить все и нажмите ОК.
Рис. 158. Раскрытие списка в поле данных.
Выделите диапазон ячеек A2:F2 (просто выделите вторую строку) с названиями полей, выполните команду Копировать и перейдите на следующую страницу, нажав на клавиатуре кнопку < Page Down >.
Рис. 159. Копирование заголовка сложного фильтра.
Перейдите на ячейку А25. Выполните команду - Вставить.
Рис. 160. Создание заголовка сложного фильтра.
B базе данных найдите записи по фильмам, которые отвечают следующим условиям: цена - 100 р., тип - боевик, количество - более 100, дата поступления - после 10 марта, для чего введите в ячейку В26 -100, в ячейку С26 -боевик, в ячейку D26 - >100, в ячейку Е26 - >10.03 и щёлкните мышью по ячейке А25.
Рис. 161. Создание сложного фильтра.
Выделите диапазон ячеек A25:F26 (для этого удерживая < Shift > щёлкните по ячейке F26). Выполните команды меню Формулы - Присвоить имя - Присвоить имя. Возникнет окно Создание имени. Введите имя: Критерий и щёлкните по кнопке ОК.
L t1UKDXHTtVBSKC5JzEtJzMnPS7VVqkwtVrK34+UCAAAA//8DAFBLAwQUAAYACAAAACEAmK7QvMMA AADcAAAADwAAAGRycy9kb3ducmV2LnhtbESPX2vCMBTF3wd+h3AF3zQ1zrF1RlFB0Pm0Tny+NNe2 2NyUJmrdpzcDYY+H8+fHmS06W4srtb5yrGE8SkAQ585UXGg4/GyG7yB8QDZYOyYNd/KwmPdeZpga d+NvumahEHGEfYoayhCaVEqfl2TRj1xDHL2Tay2GKNtCmhZvcdzWUiXJm7RYcSSU2NC6pPycXWzk 7levSh3Vanquf9dfeJo2xu20HvS75SeIQF34Dz/bW6Nhoj7g70w8AnL+AAAA//8DAFBLAQItABQA BgAIAAAAIQDw94q7/QAAAOIBAAATAAAAAAAAAAAAAAAAAAAAAABbQ29udGVudF9UeXBlc10ueG1s UEsBAi0AFAAGAAgAAAAhADHdX2HSAAAAjwEAAAsAAAAAAAAAAAAAAAAALgEAAF9yZWxzLy5yZWxz UEsBAi0AFAAGAAgAAAAhADMvBZ5BAAAAOQAAABAAAAAAAAAAAAAAAAAAKQIAAGRycy9zaGFwZXht bC54bWxQSwECLQAUAAYACAAAACEAmK7QvMMAAADcAAAADwAAAAAAAAAAAAAAAACYAgAAZHJzL2Rv d25yZXYueG1sUEsFBgAAAAAEAAQA9QAAAIgDAAAAAA== " filled="f" strokecolor="red" strokeweight="2pt"/> 
Рис. 162. Создание имени сложного фильтра.
Вызвать Расширенный фильтр, для этого исполните команды меню Данные - Дополнительно. Возникнет окно Расширенный фильтр. В поле Обработка включите режим Скопировать результат в другое место. Затем убедитесь, что в поле Исходный диапазон указан диапазон ячеек базы данных, а в поле Диапазон условий - диапазон ячеек с установленными Вами критериями (если диапазоны не указаны их надо указать). В поле Поместить результат в диапазон введите, набрав с клавиатуры, диапазон ячеек в абсолютных адресах $A$30:$F$30 и щёлкните кнопку ОК.