WWW.MASH.DOBROTA.BIZ
БЕСПЛАТНАЯ  ИНТЕРНЕТ  БИБЛИОТЕКА - онлайн публикации
 

Pages:     | 1 | 2 || 4 |

«И.М. Погосян TeachPro™ Microsoft® Excel 2003 Мультимедиа Технологии и Дистанционное Обучение МОСКВА Погосян И.М. Мультимедийный самоучитель на CD-ROM: TeachPro™ MS Excel 2003/ Погосян И.М. — М.: ...»

-- [ Страница 3 ] --

Для того чтобы это было удобнее делать, мы выводим на экран очень полезную для данного случая панель инструментов, которая называется Веб-узел. Это у нас, вид, панель инструментов, сейчас мы с вами найдем вот эту вот строчку, Веб-узел. Вот, она у нас вот здесь появилась .

Давайте начнем с поля адрес, собственно вот оно и есть. Это поле адрес. Сейчас здесь показан полный путь на наш файл, это C:\Documents and Settings\PC1\Мои документыExcel (version1) .

Полный путь к тому файлу, который как вы сами догадываетесь, у нас в данный момент открыт на 186 TeachPro Microsoft Excel 2003

–  –  –

Он у нас даже выделен. Нажимаем, на кнопочку открыть и у нас сейчас происходит открытие нашего файла, вот. Файл у нас теперь открыт .

Итак, на этом уроке мы с вами научились работать с файлами в сетях Internet и Intranet, а так же научились работать с рабочей книгой на узле FTP. На следующем уроке мы начнем изучать тему, которая называется публикация данных WEB с помощью Excel .

10.7.4. Работа с гиперссылками Сейчас мы с вами поговорим о публикации данных Web с помощью Excel. На этом уроке мы продолжим беседовать об особенностях работы с файлами в локальной сети Интернет .

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

Чем отличается работа с файлами в Интернет? Во-первых, в Интернет адреса дается с помощью URL. Это специальная форма указания пути к файлу. Давайте сейчас мы с вами попробуем открыть файл, который мы с вами ранее сохранили на узел FTP. Открываем, как мы с вами уже сказали узел у нас FTP ufar.am Мы войдем туда под именем ufar и соответственно введем пароль, вот так вот, ufar544114 ОК. Вот наш узел, он у нас сейчас появился. Как вы помните, мы с вами ранее сохранили вот этот вот файл. Сейчас мы с вами ее откроем, у нас сейчас открывается данный файл .

Вот. Давайте выведем на экран нашу панель инструментов Веб-узел. Чтоб подробно изучить адрес, который ведет к данному файлу. Это перед вами URL. Во-первых, самое первое, что мы указываем, это тип протокола. В данном случае, тип протокола у нас указан FTP, это означает, файл трансферт протокол. Протокол точно так же может быть HTTP, затем идет имя пользователь, в данном случае это ufar. Затем наш пароль для пользования, вот он. А потом, название самого FTP, после собачки, это у нас uafr.am а затем, уже непосредственно путь на сервере. Если мы сейчас с вами внимательно посмотрим на адресацию, мы собственно это все поймем. Любой URL адрес имеет одну и ту же структуру адреса. С адресацией мы разобрались .

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

Исправим. Мы с вами набрали это несколько неправильно и посмотрим, что сейчас мы с вами это сделаем. Во-первых, выделим эту ячейку и выполним следующую команду, вставка, гиперссылка. У нас появилось диалоговое окно, которое называется, добавление гиперссылки .





Сейчас вы должны, связать гиперссылку с какой, не будь, например, либо рабочей книгой в текущей папке, либо с просмотренными ранее страницами, либо с последними открытыми файлами и так далее. Ну, давайте попробуем связывать, с какими не будь файлами в текущей папке. Текущая папка в данном случае у нас вот эта вот самая FTTP узел. Давайте перейдем, на 188 TeachPro Microsoft Excel 2003 какую не будь другую папку, войдем, Documents and settings, затем сюда, войдем в папку, мои документы и попробуем найти, какой не будь Excel-ий файл. Вот предположим вот этот, нажмем на кнопку ОК. Видите, у нас сейчас имеется гиперссылка .

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

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

Вернемся обратно и поработаем дальше с гиперссылками. Давайте введем слово гиперссылка еще раз. В данном случае у нас будет, Гиперссылка 1 и попробуем связать нашу гиперссылку уже с внешним источником, а не с внутренним. Например, с каким не будь HTP адресом. Давайте мы с вами это введем, ну, например, сейчас нам подсказывает, сто можно ввести rambler.ru Предположим, нас это вполне устраивает, вторая гиперссылка у нас осуществит переход по сайту rambler.ru. Нажимаем. Сейчас в данном случае у нас открылся explorer, в котором уже загружен вот этот сайт rambler.ru закрываем его. И продолжаем работать с гиперссылками далее .

Сейчас мы попробуем с вами создать гиперссылку в пределах документа. Например, для чего это может быть нужно? Это удобное средство при работе с большими документами и собственно может повысить производительность данной работы. Давайте введем слово гиперссылка, переключимся на русский шрифт соответственно, гиперссылка 2 ?????, выделяем данную ячейку и соответственно вставляем гиперссылку. Сейчас мы с вами хотим связать не с файлом и не с Web страницей, а на самом деле с, местом в документе .

Здесь мы с вами можем ввести, во-первых, адрес ячейки, с какой именно мы хотим связать нашу гиперссылку, а собственно и номер листа. Предположим мы хотим связать нашу гиперссылку с листом 2, но с ячейкой не А;1, а к примеру С;1. Давайте так и сделаем, а еще лучше не С;1 а D;1, переключимся на латинский шрифт при этом. Нажмем на кнопку ОК. И посмотрим, что у нас при этом получится. Подвели курсор мыши, нажали. Видите, мы сейчас находимся на втором листе, на ячейке D;1. Точно так же мы можем поставить ссылку на новый документ .

Давайте здесь введем опять таки слово гиперссылка. К стати уже не понятно, какой номер, предположим, пуст это у нас будет 5, хотя на самом деле номер не совсем правильный, вставка, гиперссылка, и здесь мы пытаемся связать с новым документом и указать имя нового документа. К стати здесь указан и путь. На самом деле давайте на FTP узле путь мы с вами указывать не будем .

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

Нажимаем на ОК, вот у нас открылся файл F1 соответственно, параллельно с нашим файлом, Гиперссылка 5. Меняются гиперссылки следующим образом, выделяем данную ячейку, вот так вот. Сейчас мы с вами ее открывать не будем, и выполняем следующую команду. Опять таки, вставка гиперссылка. Вот сейчас мы видим, что у нас гиперссылка идет на текущую папку вот на этот файл, F1.htm мы можем его поменять, например, на просмотренные страницы, на последние файлы, на навое место в документе и так далее. И точно так же, после того как мы поменяем, нажать на кнопку ОК. И гиперссылка у нас собственно будет изменена. Таким образом, мы с вами научились пользоваться гиперссылками. На следующем уроке мы с вами научимся пользоваться программой Интернет Explorer .

–  –  –

нажмем на кнопку стоп. Чтобы наш браузер не открывал ту страницу, которая загружена у нас по умолчанию, и продолжим разговор. Во-первых? в Internet Explorer имеется возможность открыть веб-страницы по, заданному, URL. Эта возможность имеется во всех Internet браузерах потому, что открывать те страницы URL это и есть основное предназначение. В поле адрес собственно вы вот сейчас его видите. Мы вводим адрес, какой не будь веб-страницы .

Давайте попробуем сейчас это сделать введем уже знакомый нам адрес rambler.ru и нажмем на клавишу Enter, клавишу ввода. Сейчас перед нами открывается сайт который называется, rambler.ru Очень часто с помощью Internet Explorer-а нам приходится открывать документ, который находится на нашем локальном диске. То есть задавать адрес не в поле адреса, а открывать файл. Для этого удобнее всего сделать следующее. Меню, файл. Выполняем команду, открыть. Сейчас у нас открылась диалоговое окно, в котором с помощью этой кнопочки, обзор мы можем увидеть содержимое любого нашего локального диска. Собственно найти любой файл. В данном случае у нас здесь внизу устанавливается файл HTML. Но мы собственно с вами можем в этом списке файлов выбрать другой тип .

Вот так. В данном случае, мы с вами изучаем Internet Explorer. И не какой файл мы с вами открывать не собираемся. По этому нажимаем на кнопку отмена. И давайте вот это главное окно тоже собственно закроем. Здесь у нас иметься вот такие две кнопочки, кнопочка, назад. И кнопочка, вперед. С их помощью мы передвигаемся между ранее открытыми, веб-страницами .

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

Сейчас мы только, что Internet Explorer, поэтому страниц для отображения у нас собственно нет .

Необходимо заметить, что существует возможность настройки параметров работы с Internet, а так же параметров внешнего вида веб-страниц документов. Как мы это делаем? Для этого, из меню, сервис. Вот собственно это меню. Мы вызываем диалоговое окно, которое называется, свойства обозревателя .

С помощью этого диалогового окна и настраивается Internet Explorer. Изучение этого диалогового окна мы начнем с вкладки, общие. В поле адрес вот у нас вот это самое поле, адрес мы задаем адрес, начальной веб-страницы с которой Internet Explorer начнет свою работу. Как только мы эго запустим, он начнет открывать установленную страницу. На самом деле лучше всего, если он начинает с пустой страницы .

Давайте мы собственно это и установим .

Вот так вот. Во-первых, это будет быстрее, не нужно ждать пока он откроет какую там страницу и не нужно нажимать на кнопку стоп. Internet Explorer, имеет специальный, Кеш, для ускорения работы. Иногда появляется необходимость его отчистки. Если эта необходимость иметься, мы нажимаем на кнопку, которая называется, удалить файлы. Вот эта кнопка. Кроме того, Internet Explorer ведет специальный журнал, который называется, history в английской версии, или журнал. В котором регистрируются, те страницы, которые мы посещали ранее. Например, сейчас вы здесь видите, что нужно хранить ссылки в течении двадцати дней. Если мы, по какой то причине хотим очистить эти ссылки, мы должны нажать на соответствующую кнопочку, вот собственно кнопочка, очистить. С помощью, вот этих следующих, четырех кнопок, вы сейчас их видите. Мы определяем цвета, шрифты, языки и оформление. Давайте попробуем нажать на кнопочку, цвета .

Здесь предлагается использовать цвета установленные Windows, для просмотренных ссылок, и не просмотренных. Видите, вот просмотренные ссылки у нас будут обозначаться таким фиолетовым цветом, а не просмотренные соответственно синим. Закроем данное окно. И перейдем 190 TeachPro Microsoft Excel 2003 на кнопочку, шрифты. Сейчас, здесь, мы определяем, какой набор знаков и шрифтов изображать особенно то, что мы хотим. Закрываем данное окно. И продолжаем далее. Ну, языки и оформление, собственно понятно. Перейдем на вкладку, безопасность .

Здесь мы с вами устанавливаем уровень безопасности, выбора какой то определенной зоны .

Чем отличаются уровни безопасности? Уровень безопасности, вот здесь, он может быть высоким, средним и низким соответственно. Давайте нажмем на копку, другой. И, посмотрим собственно, чем они могут отличаться. Вот здесь видите? Здесь, для каждого уровня безопасности мы устанавливаем соответствующий параметр. В результате чего ??? уровень безопасности на самом деле не заменяется. Перейдем на вкладку, содержание .

Здесь мы с вами, во-первых, можем ограничить доступ к информации, которая получается из Интернета .

Вот здесь мы сейчас видим ограничения доступа к информации, получаемой из Интернета. Здесь содержится информация о насилии не нормативной лексики, обнаженных тел и страниц, связанных с сексом. Если, мы нажмем на кнопочку, включить мы собственно увидим диалоговое окно ограничения доступа. Эта возможность на самом деле очень полезна с учетом, того, что дети имеют доступ к компьютерам и могут собственно увидеть там и секс, и обнаженные тела и так далее. Ну, давайте закроем это окно. И продолжим. Что здесь у нас имеется еще? У нас имеется такое понятие как автозаполнение .

Нажмем на кнопочку и поговорим о нем .

Автозаполнение это возможность в Internet Explorer завершать начатый вами набор .

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

Эта возможность к стати и имеется в Excel, она называется автозавершение, мы с ней на самом деле знакомы. Нажмем на кнопку, отмена .

И перейдем на вкладку, подключение. Вот так .

Если мы с вами не имеем подключение Интернет, то следует нам нажать на кнопку, добавить, чтоб это самое подключение с вами инсталлировать .

Ну, в данном случае если мы нажмем на кнопку, отмена, мы увидим, что подключение у нас есть. Оно у нас называется, arminco, по этому мы можем на самом деле либо его просто удалить, либо нажать на кнопку настройка, чтоб каким то образом его настроить. Первая группа автоматическая настройка. Она изменяет, уставленную в ручную, параметры. Это опция как вы видите, у нас собственно отключена. Автоматически мы с вами не чего не определяем. Переходим на proxy сервер. Очень часто Интернет провайдеры используют, proxy сервер, он промежуточный, фактически между ними и пользователем .

Соответственно нам сообщается установки proxy сервера, то есть адрес и порт для определенных протоколов и типов серверов. В данном случае у нас используются для всех протоколов, proxy сервер, который называется, proxy.aic.net а порт соответственно, 3128. Это для всех протоколов .

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

Модем, который в данный момент у нас имеется, и так далее. Нажмем на кнопку, ОК. На самом деле ходить по всем этим бесконечным вкладкам, которые тут имеются, смысла нет. Лучше мы с вами поговорим… Закрыв к стати это окно так же, о прочих вкладках вот этого главного окна, которое называется, свойства обозревателя. Перейдем на вкладку, программа .

Это очень важная вкладка, она содержит список программ, которые работают параллельно с Internet Explorer. Во-первых, мы можем выбрать редактор HTML. Предположим, мы хотим отредактировать ту или иную страницу. В данном случае нам предлагаются, следующие Глава 10. Совместная работа Excel с офисными приложениями 191 возможности. Это Word, Publisher и Excel по умолчанию у нас был установлен Word. Кроме того, необходимо установить почтовый клиент, по умолчанию. Которым именно почтовым клиентом мы с вами хотим получать и отправлять нашу почту. Сейчас здесь установлен, Microsoft Outlook .

Можем установить Outlook Express, а собственно и Hotmail и также MSN Explorer. Ну, на самом деле Outlook, наверное, оптимальный вариант .

Продолжаем. Группа новостей, чем мы именно получаем новости. В данном случае у нас выбор один, Outlook Express. Связь по Интернету, точно так же один выбор, NetMeeting. Ну, собственно календарь и адресная книга нас не интересуют. Кстати надо сказать, что Microsoft Office Outlook имеют свою адресную книгу, которая отличается от адресной книги Microsoft Windows. Мы к стати можем выбрать именно ее. Здесь, имеется флажок, проверять является ли Internet Explorer используемым по умолчанию, обозревателем. На самом деле, если у нас установлено, несколько Интернет обозревателей, например NetSkip или Opera, тогда стоит проверять Internet Explorer обозревателем по умолчанию, или нет в данном случае у нас один обозреватель Интернет. По этому стоит эта галочка или не стоит, разницы нет. Нажимаем на кнопку, ОК. И попробуем завершить текущий урок, потому, что мы с вами познакомились, с тем как настроен обозреватель Интернет, и пробежались практически по всем вкладкам диалогового окна свойства обозревателя. На следующем уроке мы с вами научимся просматривать документы Microsoft Office с помощью Internet Explorer .

10.7.6. Панели инструментов Web-узел Сейчас мы с вами поговорим о просмотре документов Microsoft Office в Internet Explorer .

Как мы с вами уже говорили, Internet Explorer позволяет непосредственно открывать просматривать и редактировать документы Microsoft Office. Для того чтобы открыть какую не будь страницу, мы должны сейчас задать ее адрес. Вот, предположим, зададим здесь какой не, будь адрес, к примеру, www.mail.ru. Сейчас перед нами откроется страница вот этого известного mail сервера. Открывается именно вот таким вот образом .

Если мы хотим, открыть какой не будь именно файл, мы делаем вот что. Меню файл, выбираем команду, открыть. После того как у нас открылось вот такое диалоговое окно. Мы либо здесь должны ввести адрес документа или папки в Интернете или выполнить нажатие на кнопку обзор и уже искать непосредственно на нашем компьютере. В качестве типа файлов мы можем оставить файлы HTML, а можем выбрать любой другой, например все файлы. Мы здесь увидим весь список файлов, который в данный момент имеется на нашем компьютере. Ну, мы сейчас с вами этого делать не будем, попробуем закрыть эти окна и сделать вот что .

В Internet Explorer имеется возможность поиска нужной информации. Делается это с помощью нажатия на кнопку поиск, вот эта самая кнопка. С левой стороны экрана у нас появляется инструменты для поиска. Сейчас собственно у нас и появится. Что мы можем в этом случае сделать? Во-первых, вот видите, у нас уже имеется предложение выбрать категорию поиска, мы можем искать веб-страницу, здесь мы можем дать ключевое слово, которое должна содержать та или иная веб-страница. А можем искать любые другие объекты. Собственно в качестве содержания той или иной веб-страницы мы можем определить слова, последовательность слов, которые могут быть соединены знаками и, или, не и так далее .

192 TeachPro Microsoft Excel 2003 Нам сейчас ничего искать не нужно мы просто хотели посмотреть, что представляет собой поиск информации в Internet Explorer, собственно мы это и увидели. Примерно понятно, здесь также имеются кнопочки, избранное, медиа и так далее. Давайте прикроем Internet Explorer. И вернемся обратно, мы вернемся к этой самой панели инструментов, которая называется, веб-узел .

Если вы обратили внимание, у нас в данный момент открыт файл, который находится на узле FTP .

Мы уже упоминали с вами эту панель и собственно упоминали кнопки назад в данном случае, а если мы нажмем на кнопку, назад, мы сможем перейти на кнопку собственно вперед .

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

Следующая кнопка, вот это вот, которая сейчас закомментирована, она называется остановить .

Она сейчас не активна, потому что в данный момент не одна страница не находиться в процессе загрузки. С помощью этой кнопки мы останавливаем загрузку документа. Следующая кнопка обновить, то есть перезагрузить документ, а именно начать его загрузку заново. Далее идет переход на домашнюю, веб-страницу. Мы можем указать любую веб-страницу, в качестве, нашей домашней. После этого, ну, например, сейчас мы нажали на эту кнопку. Как вы помните, ранее в Internet Explorer мы дали, пустую страницу в качестве нашей домашней, о чем нам сейчас сообщается, открывается about:blank, то есть пустая страница, но, не взирая на это, Microsoft Excel предупреждает нас о том, что гиперссылка представляет угрозу для компьютера .

Мы игнорируем это суровое предупреждение и сейчас у нас в Internet Explorer, если вы обратили внимание, вот он у нас, пытается открыться пустая страница. Чем она собственно здесь и откроется. Просто происходит несколько медленно, ну давайте прикроем сейчас, не будем дожидаться пока, у нас откроется, и перейдем к следующей кнопке, которая называется найти в Интернете. Это копия той кнопки, которую мы только что запускали на обозревателе Интернет .

Правда там она называлась поиск. Вот сейчас мы с вами ее нажали, видите, опять таки в левой части окна открывается панель поиск. Мы здесь можем собственно опять таки задать ключевые слова, которые содержит данная страница. Правда нам сейчас сообщается, что произошла ошибка, мы собственно эту ошибку игнорируем. Вернемся к нашему приложению Интернет и посмотрим на следующую кнопку. Ну, кнопка избранное, это, в общем, понятно .

Мы собственно с этой кнопкой уже познакомились и даже успели поработать, вот здесь у нас ссылка на наш файл Excel (version)1. Следующий открывающийся список, это переход. Этот список дублируют клавиши перехода, то есть, например можно входить назад вперед, открыть определенную гиперссылку, начальную страницу, найти в Интернете и так далее .

Ну, с панелью адреса мы уже, в общем, то мы отлично знакомы. Сюда мы ввозим адрес нашему документу, таким образом, мы бегло прошлись по просмотру в документах в Microsoft Office, то есть вот по этой самой панели, которая называлась у нас веб-узел. У нас получился такой коротенький урок, на следующем уроке мы с вами начнем новую тему, которая называется публикация данных в Интернет средствами Excel .

10.7.7. Статическая публикация рабочей книги Сейчас мы с вами начнем новую тему, которая называется публикация данных в Интернете средствами Excel. Необходимо заметить, что уже достаточно давно Microsoft Office имеет возможность сохранять свои документы в формате HTML. Это значит, что мы сохраняем наши данные в таком формате, что можем открывать их с помощью любого Интернет браузера. Более того, мы сможем опубликовать их на каком не, будь веб-узле .

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

Глава 10. Совместная работа Excel с офисными приложениями 193 Во-первых, для начала нужно выбрать имя файла, под которым нужно сохранить документ .

В данном случае, по началу у нас выбрано Excel (version)1. А тип файла у нас веб-страница, папки при этом у нас никакой нет. Давайте перейдем на наш жесткий диск. Потому как если вы обратили внимание, у нас находитесь на нашем веб-узле. Перейдем в папку, мои документы, здесь создадим новую папку. Которую например, назовем web. Нажмем на кнопочку ОК и нажмем на кнопку, сохранить. Сейчас нам сообщается, что некоторые возможности книги не будут сохранены в таблице, а именно пользовательские представления. Затем задается вопрос, продолжит ли сохранение в идее веб-странице. Мы согласны с предупреждением. По этому нажимаем на кнопочку, да. Сейчас наш файл сохранен. Давайте попробуем открыть публикацию в Интернет браузере. Запустим наш Интернет браузер .

Отключим кнопочку поиск, из меню файл, выполним команду, открыть, обзор, перейдем на папку мои документы, затем в папку web и попробуем открыть то, что мы с вами сохранили и нажмем на кнопку открыть. Затем нажмем на кнопочку ОК. Посмотрим, что у нас при этом получилось. У нас получилось вот это следующее. Мы увидели нашу страницу, то есть всю таблицу созданную, даже созданные гиперссылки и даже диаграммы, вот так .

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

Давайте закроем Интернет браузер и попробуем с этим поработать. Что мы с вами делаем? Нам нужно сохранить часть нашей рабочей книги, то есть определенный диапазон, давайте его для начала выделим. Предположим, только этот диапазон мы с вами хотим сохранить, опять выполняем команду файл, сохранить как веб-страницу. В данном случае, мы с вами должны сделать вот что, выбрать флажок не всю книгу, а только выделенное. В данном случае у нас файл будет страница HTM. Сохраняем, попробуем опять таки запустить наш браузер, открыть наш файл, обзор, и вот здесь имеется файл страница .

ОК. Видите, сейчас у нас на экране, фактически часть нашего рабочего листа. То есть, часть информации, которую мы только что выделили .

Таким образом, мы с вами сохранили отдельный элемент рабочей книги, то есть некоторый диапазон. Что сейчас мы с вами хотим сделать? Предположим, мы хотим опубликовать наш фрагмент. Перейдем на Excel. Предположим мы хотим опубликовать вообще только вот столько вот. Файл. Сохранить как веб-страницу, выделенное, но нажимаем кнопку, не сохранить, а опубликовать. Что у нас сейчас появляется на экране? У нас открывается диалоговое окно, которое называется, как вы видите публикация веб-страниц. Диапазон ячеек у нас здесь уже собственно с вами выбран .

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

194 TeachPro Microsoft Excel 2003 Здесь нам предлагается сделать вот что. Так как в свое время у нас страница HTML уже существовала, нам предлагается заменить файл, или, добавить данные в имеющиеся. Давайте выберем, заменить файл, посмотрим, что у нас при этом с вами получиться. В данном случае, у нас открылся вот такой вот фрагмент .

Причем который в себе на самом деле копирует фрагмент Microsoft Excel. Мы здесь можем вводить, например отдельные, даже взятые элементы, как вы видите. Таким образом, страница у нас фактически является динамической, например даже нажмем на кнопочку сумма и нажмем клавишу Enter. Здесь, в данном случае у нас даже произошло суммирование. То есть наша страница получилась динамической. Введем здесь, например число 10 и мы уведем, что сумма меняется соответственно. Итак, на этом закончим текущий урок. На следующем уроке, мы продолжим знакомство с редактированием веб-страницы и собственно уже непосредственно начнем тему публикации .

–  –  –

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

10.7.9. Настройка публикации Сейчас мы с вами поговорим о замене опубликованных ранее данных. Допустим, нам нужно, заменить опубликованные данные в рабочей книге. Мы открываем с помощью команды файл, и открыть, мы собственно это с вами проходили. Наша HTML страницу, в нашем случае, это у нас просто страница. И собственно здесь совершаем определенные изменения. Предположим здесь у нас не видеомагнитофоны, а, к примеру, стиральные машины, давайте переключимся на русский шрифт, стиральные машины, после того, как мы сохраним данный файл .

Закроем его. А потом попробуем открыть в Explorer-е. Сейчас мы с вами вот так сделаем, вот наша страница, мы увидим, что изменения у нас собственно были проведены. То есть Internet Explorer загрузил фактически новую версию нашего файла. Заменит данные Excel опубликованные на веб-странице в качестве отдельного элемента рабочей книги, это более трудоемкая задача, по этому мы сейчас этим заниматься не будем. Делается, точно так же как мы делали до того, то есть корректируется и сохраняется, просто изменяется не сама, HTML страница, а ее прототип. То есть сам Excel-ий файл. Дело в том, что когда сохраняются статистические HTML файлы, то мы затем можем их редактировать, по этому в начале редактируется HTML файлы, а потом опубликовывается WEB. Сейчас мы займемся настройкой параметров публикации WEB. Что, собственно говоря, интересно и полезно. Давайте перейдем на Excel, вот он сюда и выполним следующую команду, сервис, параметры и перейдем на вкладку общий. Вот так .

Здесь у нас имеется кнопочка, которая называется, параметры веб-документа. Нажмем на нее .

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

Кроме того, если необходимо загружать картинки с веб-страницы, не созданные в Excel, то соответственно у нас должен быть включен вот и этот флажок. Если это необходимости нет флажок можно вот таким вот образом сбросить .

Следующая вкладка обозреватели. На этой вкладке, вот здесь, первым раскрывающемся списке. Лучше всего установить Microsoft Internet Explorer версии 5,0 или более поздней версии, а еще луче 6,0 .

Есть пользователи, которые на приме используют Netscape Navigator навигатор и есть пользователи, которые используют Internet Explorer более старой версии. Ну а мы соответственно будем использовать самую новую. То есть Internet Explorer 6,0 или более поздний. Далее вот здесь, идет список параметры. Мы должны последить, чтобы был, во-первых, установлен вот этот вот значок, использовать CSS при форматировании шрифтов, это список стилей, который поддерживает Internet Explorer, начиная с версии 5,0, затем обязательно использовать VML, при отображении графики в обозревателях, он тоже должен быть установлен. Мы устанавливаем флажок в том случае, если графических файлов необходимо 196 TeachPro Microsoft Excel 2003 использование современных технологий и, например сохранение новых веб-страниц в виде вебстраниц в одном файле. Пусть этот флажок так же будет установлен. Ну и установлен флажок, разрешить графический формат PNG .

Перейдем на вкладку файлы, здесь мы определяем параметры файлов. Во-первых, стоит этот флажок оставить включенным, то есть создать папку, для вспомогательных файлов. Затем по возможности использовать длинные имена файлов, он тоже должен быть установлен. И соответственно обновлять, ссылки при сохранении, причем устанавливаем этот флажок, для того, чтобы при сохранении и при записи документов связи бы обновились, ссылки шли не из Интернета, а из локальной папки. Далее, редактор, используемый по умолчанию. Необходимо всегда проверять является ли приложение Microsoft Office редактором по умолчанию для вебстраниц. Ну и последний пункт, загрузить веб-компоненты Office .

А собственно здесь имеется тот путь, откуда мы с вами Microsoft Office и устанавливали .

Переходим на вкладку картинки, здесь задаются параметры сохранения изображения, для большей экономичности документа, нужно задать параметры монитора. Здесь мы задаем размер экрана. А здесь число точек, на дюйм соответственно. Переходим на вкладку кодировка, в которой имеется два списка, во-первых, загрузить текущий документ как, у нас здесь установлена кириллица Windows, затем имеется вот этот флажок. Всегда сохранять веб-страницы кодировки по умолчанию, флажок на самом деле луче не устанавливать, потому что сохранение документов на самом деле должно быть гибким. И имеется вот этот точно так же, вот этот вот, второй список .

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

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

Ну, на этом давайте закончим текущий урок, мы с вами научились настраивать параметры публикации в Excel. Ну, и завершим рассмотрение вопросов настройки. На следующем уроке мы с вами начнем изучать азы разметки текста HTML. А сейчас закончим текущий урок .

–  –  –

вы его видите в закрывающемся виде, а тут на верху вы его увидите в открывающем виде, если мы сейчас его здесь, где не будь, собственно с вами найдем. Вот к стати он, вот (body) в открывающем виде. Точно так же и (title). Видите, имеется, тег (title), заголовок нашей веб-страницы, это открывающий тег, а этот с косой чертой, соответственно закрывающий тег. То есть насколько вы понимайте, любой документ в формате html, включает в себя парные теги. Сейчас мы с вами постараемся самостоятельно создать блокнотик, какой не будь html документ. Давайте закроем вот это все. Откроем программу блокнот, давайте полностью откроем данное окно, стандартный, вот имеется у нас программа блокнот. Сейчас мы с вами сохраним этот пустой файл, как html документ. Сохраняем мы его, на наш рабочий стол и назовем таким вот образом, 111.html Вот такой вот у нас файл. Видите, он у нас сейчас сохранен на нашем экране, если мы его откроем, откроется автоматически Internet Explorer и в нутрии будет совершенно пустой документ. Сейчас мы с вами открываем его с помощью программы блокнот и начнем сюда, что не будь записывать. Самый главный тег, это тег html. Он означает, что начата страница html. Давайте с вами собственно это сделаем. Html это, открывающий, тег и сейчас вставим, закрывающий .

Закрывается, с помощью как мы с вами уже сказали с косой черты. Вот так. У нас сейчас имеется два парных тега. Сохраним .

То, что мы с вами только, что создали, попробуем открыть это в Интернет браузере и посмотрим, что у нас при этом получится. Понятное дело никаких изменений у нас нет, потому, что html страница у нас совершенно пуста. Откроем программу блокнот и продолжим то, что мы сделаем. После того, как мы с вами создали открывающий и закрывающий тег html, нам нужно проставить заголовок .

Заголовок у нас ставиться в виде тега, который называется (head). Давайте это сделаем, это открывающий тег и соответственно мы сейчас закроем, у нас будет закрывающий тег, вот. Два таких тега. Как только мы поставили открывающий закрывающий теги, которые, как правило, содержат информацию о названии некоторой общей установки документа. Нам необходимо ввести название. Это мы делаем с помощью тега, который называется title. Обратите еще раз внимание, мы это делаем в нутрии тегов (head), (title), открывающий тег и соответственно давайте прямо рядышком и введем, у нас будет закрывающий. Сейчас введем соответственно название нашей веб-страницы. Ну, пусть это будет, например My web. Вот такое название будет иметь наша страница. Давайте попробуем сохранить данный файл и открыть его в Internet Explorer .

И посмотрим, что у нас при этом получиться. Если вы обратили внимание, вот здесь прямо в заголовке Internet Explorer у нас уже есть информация, что, мы открываем страницу, который называется My Web. То есть наш (title), фактически загрузился сюда. Давайте закроем соответственно Explorer, откроем программу блокнот. И будем продолжать. После того, как мы с вами описали заголовок, нужно описать еще и тело документа. Для тела документа существует специальный тег, который называется соответственно (body). Тег (body) мы с вами будем вводить после тега (head), вот так вот, это открывающий, ну и соответственно сейчас введем закрывающий, то есть тело html у нас будет именно это, фактически мы сейчас с вами познакомились с тремя главными тегами нашего документа .

Сейчас перейдем к форматированию текса, Давайте, например, введем вот сюда (body) какой не, будь текст. Ну что мы с вами можем ввести, давайте на русском языке введем ну, например информацию, что это, Наш web. Вот, что-то в этом роде. Сохраняем соответственно и открываем с помощью explorer, посмотрите, что у нас при этом получилось. Видите, информация о том, что это Наш Web, мы сейчас, в нашем браузере видим. Сейчас постараемся наш текст отформатировать. Закроем Internet Explorer, откроем собственно наш блокнот и посмотрим, что мы с вами можем здесь сделать. Во-первых, если мы хотим сделать текст полужирным, мы можем проставить тег, который называется (bold) .

Давайте скопируем вот этот фрагмент, буфер обмена и размножим его вот таким вот образом несколько раз. Первый тег, который мы только что с вами упомянули, это тег (b), то есть тег (bold). Давайте здесь мы его закроем, посмотрим, что у нас при этом получится, а, кроме того, до 198 TeachPro Microsoft Excel 2003 этого мы можем вставить еще тег, который называется (i), (italic). Сейчас собственно мы с вами его тоже вот так вот красиво вставим. Сейчас сохраним нашу страничку, откроем explorer и посмотрим, что у нас при этом получилось. А у нас получилось вот, что. Здесь у нас имеется (bold), здесь у нас имеется (cursive), а здесь у нас собственно ничего не имеется. Сейчас мы видим один из самых больших недостатков нашей страницы. Абзацы у нас не проставлены, все идет друг за другом. Что делать, чтобы были проставлены абзацы? А это на самом деле очень просто, нам достаточно вот здесь, везде проставить тег, который называется (break), вот так вот .

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

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

Это первое, что мы с вами сделали, начало и конец. Затем, помещается заголовок списка, называется лист (head) и его закрывающий тег. Ну, давайте попробуем, это лист (head), давайте здесь на русском языке напишем список, закроем, закрывающим тегом, вот так, а затем элементы идут с тегом, который называется (li), (list item). Ну, давайте здесь сделаем (li), это у нас (list item) закрывающим тегом. Соответственно здесь сделаем (li) открывающий. Тоже самое давайте сделаем вот здесь. Здесь пусть он тоже будет закрывающим. Давайте сохраним и посмотрим, что у нас здесь при этом получилось. На самом деле нам достаточно открыть старую. Ранее открытую веб-страницу и нажать на кнопочку обновить. Видите, у нас здесь имеется список, собственно здесь это сказано и вот, два элемента, не нумерованного списка соответственно .

Если мы хотим создать нумерованный список, давайте мы все это прикроем, здесь у нас будет не (ul) соответственно, а по-другому (ol), соответственно давайте закроем соответствующий тег .

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

Мы можем выделять заголовки первого уровня, второго уровня и так далее. Как это делается?

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

На самом деле здесь нам ничего открывать не нужно, мы просто откроем вот отсюда. Давайте, эти теги со списками мы уберем, они в данный момент нам абсолютно не нужны, кроме того, уберем и вот, эти фрагменты, вот так. Вот мы так основательно почистили наш html. Сейчас попробуем обозначить заголовки первого уровня, это у нас будет (h1), соответственно закрывающийся тег, (h1), затем (h2), естественно закрывающий (h2), ну и давайте для разнообразия, пусть у нас будет (h3). Вот нечто вроде этого, опять таки сохраним, и откроем любой из ранее открытых. И нажмем на кнопку обновить. Сейчас вы видите, что у нас каждый из трех написан своим видом заголовка .

Кроме того, текст html также можно вставлять гиперссылки, они делаются с помощью тегов, которые называются (Ancor), это первый тег, а второй с помощью ключевого тега (href). Ну, давайте попробуем сейчас ввести какую не, будь, гиперссылку. Ну, давайте пусть гиперссылка у нас будет на это слово, которое гласит юююю. Так, открываем, проставляем (Ancor), это первый тег, затем, (href), затем равняется и здесь в кавычках мы с вами набираем непосредственно гиперссылку, предположим мы хотим перейти в гиперссылке http, собственно www.rambler.ru вот, по такой гиперссылке мы с вами хотим перейти. Что с вами делать дальше. Дальше, закрываем данную гиперссылку, сразу Наш Web мы оставляем, а здесь опять таки закрываем, тег (А). Ну, у нас получился нечто, вроде этого .

Давайте сохраним и попробуем посмотреть, что у нас при этом получиться. Видите, вот эта последняя фраза, Наш Web сейчас имеет гиперссылку, если мы собственно на нее нажмем, у нас Глава 10. Совместная работа Excel с офисными приложениями 199 откроется непосредственно rambler, как вы видите. Ну, на этом мы с вами освоили основную часть языка html, выяснили, что такое открывающий тег, что такое завершающий. На этом урок мы заканчиваем и закрываем нашу программу блокнот, собственно .

10.7.11. Web-компоненты На этом уроке мы с вами поговорим об использовании Web компонентов в Microsoft Office. В нем существуют возможность представления данных в Интернет. Страницы, которые мы с вами пробовали создать на прошлых уроках, они были интерактивны, имели частичные функциональности Excel, которые собственно реализуются с помощью специальных средств. Эти средства называются Web компоненты и являются элементами ActiveХ, один из таких компонентов таблица, которая обеспечивает публикуемым данным ограниченную функциональность рабочего листа. Другой компонент соответственно диаграмма, она позволяет создавать на веб-странице диаграмму, которая изменяется соответственно при изменении в данных компоненте таблиц .

Имеется, также третий компонент, с которым мы с вами пока не знакомы, он называется, сводная таблица, позволяет публиковать сводную таблицу соответственно. Давайте попробуем создать интерактивную веб-страницу, с использованием Wed компонента, выполняем команду файл, сохранить как веб-страницу. В данном случае, мы хотим с вами сохранить наш весь рабочий лист, мы с вами это соответственно выбрали, пусть называется страница html. Нажимаем на кнопочку опубликовать. Что мы с вами здесь делаем, соответственно флажок, добавить, у нас активизирован. Имя файла у нас здесь имеется и соответственно открыть страницу в обозревателе, давайте нажмем на кнопочку опубликовать и посмотрим, что у нас при этом получилось. У нас открывается вот такой вот фрагмент, это таблица с усеченным как вы видите вариантом Excel .

Которым мы можем соответственно редактировать наши данные. Это и есть результат публикации Web. Если вы обратите внимание, вот здесь на верху имеется соответствующая панель инструментов. И эти кнопки соответственно интерактивность собой представляют, вот выбрали, нажали на сортировку, видите у нас соответственно, все от сортировалось .

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

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

Имеется у нас здесь правда возможность фильтрования, так называемая кнопочка автофильтр, что это такое, вы пока не знайте. И несколько еще других кнопочек. Например, экспорт, в Microsoft Office Excel, кнопочка, Команды и параметры. И так далее. Необходимо заметить, что сводные таблицы, а это понятие как мы уже сказали нам пока не знакомо. Таким способом не публикуются. Что мы сейчас попробуем сделать?

Мы сейчас попробуем опубликовать интерактивную диаграмму .

Давайте вернемся в Microsoft Excel, на ту страницу, где у нас имеется диаграмма, и выполним следующую команду, файл, сохранить как веб-страницу. Флажок, отвечающий, за интерактивность, мы с вами добавим и нажмем на кнопку опубликовать. Выбираем опубликовывать, в данном случае всю книгу или нет, вот здесь имеется вот такой вот списочек, ну мы сейчас с вами хотим опубликовать всю рабочую книгу, а собственно можем даже выбрать просто элементы. И опубликовать, например, просто диаграмму, вот. И соответственно пункт, работа с диаграммами, после чего нажимаем на кнопочку, опубликовать. Здесь опять таки нам 200 TeachPro Microsoft Excel 2003 предлагается заменить файл, добавить файл и отмену, ну давайте добавим файл. Вот если вы обратите внимание, у нас внизу сейчас имеется вот такая вот наша диаграмма .

Давайте, попробуем поменять наши данные, например. Ну, что мы с вами можем поменять?

Ну, за 2000 год видеомагнитофонов сделать, к примеру, соответственно сто штук и нажать на кнопку ОК. Ну, давайте немножко спустимся, если вы обратите внимание, у нас соответственно какие то данные, ну-ка давайте немножечко опустимся сюда, на самом деле мы с вами данные изменили не там где нужно, данные нам нужно менять вот тут и если видите, соответственно диаграмма у нас меняется. Давайте, сделаем две тысячи. Видите, диаграмма у нас изменилась вообще. То есть, у нас, получилась интерактивная диаграмма. С полученной веб-страницей, мы совершенно спокойно можем менять данные. Ну, на этом мы заканчиваем тему коллективной работы Microsoft Office, на следующем уроке мы начинаем новую тему, связанную с базами данных .

–  –  –

этого вполне собственно и достаточно. К стати надо сказать, что такой способ совершенно не пригоден, если списков у нас несколько, потому что нескольким списком соответственно одно и тоже имя присвоить не возможно. Для большинства команд в этом меню можно отдельно указывать диапазоны в базы данных. Причем, имеется ввиду, вот это меню команд. Именно с его помощью мы будем работать с нашими базами данных. Это, во-первых, а во-вторых, если вы хотите указать еще один список, то в списке друг от друга должно отделять не мене одного столбца и не менее одной строки .

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

Также мы можем проставить флажок только уникальные записи, для того, чтоб, если имелись повторяющиеся, они бы не выводились на экран. И нажмем на кнопочку ОК. После нажатия на кнопочку ОК, что у нас получилось? У нас получилось следующее, что мы выделили только Иванова и Сидорова, почему, потому что их год рождения равен 1960-ому .

А собственно 1960 у нас был критерием. Итак, мы с вами фактически завершили наш первый урок знакомства с базами данных. Фактически мы с вами получили, наши базы данных, определенный результат. На следующем уроке мы с вами все это изучим более подробно .

10.8.2. Ввод данных в списке Итак, на этом уроке мы продолжаем, знакомится с вводом данных. Во-первых, списки стоит располагать таким образом, чтобы было бы предусмотрено место для их роста. Например, вот здесь у нас имеется список, которое называется, база данных, если мы его продолжить собственно мы сможем, это сделать всего лишь на три записи, дальше, нижние будут мешать .

Что мы сейчас должны с вами сделать? Мы должны переместить наши данные в другие ячейки, но затруднение у нас следующее. У нас эти данные имеют имена, например, если мы сделаем, извлечь, у нас соответственно выделиться вот этот фрагмент, видите, у нас вот этому фрагменту присвоено, имя, извлечь. А вот этому фрагменту критерий, причем присвоение имен у нас произошло автоматически, тогда, когда мы выбирали данный фильтр, соответственно, расширенный фильтр. Сейчас мы с вами удалим данные имена. С помощью, например команды, присвоить. Мы фактически удалим имена, извлечь и имена, критерий. База данных пусть остается, она у нас останется на своем месте. Сейчас, просто на просто переносим наши данные сюда, вставить, ну и соответственно .

Вот этот фрагмент. Вставляем на свое место. Что мы сейчас делаем, перемещенному диапазону присваиваем имена обратно, это у нас будет критерий. А это у нас будет соответственно извлечь, вот так. Фактически присвоили имена обратно. Теперь поговорим немного о способах ввода данных в список. Существует несколько способов, первый это непосредственно ввод данных, то 202 TeachPro Microsoft Excel 2003 есть мы вручную набираем, что не будь. Ну, давайте введем какой не, будь, что не будь, предположим, Прохоров. Предполагаем, что Прохоров у нас родился в 1978 году .

Нажимаем клавишу ввод и любуемся результатом. Этот ввод данных называется вручную, следующий способ, это ввод данных в список с помощью формы ввода. Это очень наглядный способ, но с ним познакомимся мы чуть позже. Кроме того, при непосредственном вводе данных мы можем использовать возможности автозавершения. И далее мы можем вводить данные, выбирая из списка по средствам форм Access или с помощью средства, который называется мастер шаблонов. При вводе данных в ячейки, проблемы возникают с именованными списками. Видите, данные мы добавили, но на самом деле, если мы выберем весь этот диапазон. У нас не именованный. У нас именована только вот эта часть диапазона. Которая называется соответственно база данных. Как в данном случае нам правильно осуществить ввод. Давайте удалим эту строку. Удаляем. Куда именно будем воздвигать ячейки в данном случае не интересно. А выполним следующую команду, вот сюда, выполняем команду, вставка, соответственно строки. И уже сюда вводим вручную, Прохоров, ну те же самые наши 1978, было там девять, не важно, на самом деле, что у нас сейчас получилось .

Давайте проверим, является ли этот диапазон у нас базой данных. Является, фактически нам следовало вводить данные в середину, а не в конец. Следующим способом ввода данных является использование формы, как мы с вами сказали ранее. В Excel предусмотрено мощное, удобное и простое средство форма вода данных. Что мы должны сделать в самом начале. Сначала выделяем вот этот самый диапазон базы данных, выделили, надо отметить, что он может и не иметь имени, базы данных. Выполняем команду, данные. Давайте, полностью откроем это меню, форма. Перед нами открывается диалоговое окно с именем нашего рабочего листа. На котором находиться, база данных. Наша база данных находиться на рабочем листе, (Лист1), соответственно здесь у нас, (Лист1) и указан. Мы можем выбирать любую запись. Мы можем выбирать на самом деле, как мы только что уже сказали, любую запись. Можем создавать новую запись, давайте введем сюда, что не будь, например пусть это будет, к примеру, Распутин .

Ну, какого года рождения у нас может быть Распутин. Ну, пусть он у нас будет 1968-ого, и нажимаем на кнопочку, добавить. Если вы обратили внимание, вот здесь у нас возникла новая фамилия, Распутин, 1968-ой год. Что мы еще можем сделать, можем вернуться назад. Вот так пробежаться, по всем фамилиям, соответственно. Можем, нажимая на кнопку, далее пойти вперед .

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

Имеется несколько способов ускорения ввода, например выбора списка и автозавершение. Что такое автозавершение вы знайте, например, если мы сейчас нажмем на букву, Р. Видите, у нас появляется слово Распутин. Правда, слово Распутин мы с вами вводить не хотим. Следующий способ ускорения ввода, это выбор из списка, как это делается. Мы можем выполнить, вызвать контекстное меню и выполнить команду, выбрать из раскрывающегося списка. И соответственно выбирать любую из имеющихся, здесь фамилий, вот таким вот образом .

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

Глава 10. Совместная работа Excel с офисными приложениями 203 10 .

8.3. Форматирование данных в списке Довольно наглядного представления данных в списке, их можно отформатировать .

Форматирование осуществляется с помощью встроенного в Excel средства, которое называется автоформатирование. Для его запуска мы выполняем следующую команду, формат. Автоформат .

Сейчас нам сообщается, что в Microsoft Office Excel не может определить, к каким ячейкам применять автоформат, так как выбрана одна ячейка, окружающие ячейки пусты. Давайте, отодвинем это диалоговое окно, нажав на кнопку ОК предварительно, мы увидим, что это действительно так оно и есть. Выделим нашу базу данных и вызовем эту команду еще раз, вот так, сейчас перед нами имеется определенные способы автоформатирования. Ну, давайте выберем, какой не будь подходящий вариант, что-то вроде этого, Финансовый 2. И посмотрим, что у нас получиться после нажатия на кнопочку ОК, вот у нас будет такое изменение формата, видите, у нас здесь имеются даже такие линии .

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

Нажмем на кнопочку, параметры. Сейчас у нас наше окно открылось в свою полную величину, давайте немножко приподнимем, что мы с вами хотим делать, мы хотим, чтобы формат чисел у нас при этом не менялся, и нажмем на кнопочку, ОК. Видите, сейчас у нас форматирование применено, но формат чисел не поменялся, у нас здесь то, что было написано ране. Вернемся, к дальнейшему изучению диалогового окна автоформат. Давайте вызовем его и опять таки нажмем на кнопку параметры. Перейдем к опции, который называется шрифт. Если мы с вами не желаем форматировать шрифт, то есть не хотим менять не тип шрифта, цвет, шрифта не его, начертание, то соответственно, галочку следует, отключать. Далее идет следующая опция, которая называется опция, узоры. Видите, соответственно появляется или преподает, цвет закраски, собственно если мы отключим, рамки, то у нас будут появляться они и пропадать. Ну, здесь соответственно, ширина и высота, она у нас не будет регулироваться, если мы отключим данный, флажок и соответственно выравнивание, то есть будет все так, как было изначально. Имеется очень много вариантов автоформатирования, если вы внимательно прокрутите вот эту полосу, то собственно увидите их все. Как говориться, выбирай на любой вкус. Как используют, мы уже то же поняли. Давайте, закроем это окно. На следующем уроке мы с вами будем изучать, мастер шаблонов. И ввод данных с помощью искомого, мастера .

–  –  –

он у нас установлен. Здесь имеется строка мастер шаблона, рядом которого стоит галочка. Если у вас подобной строки нет, вам следует сделать вот что .

Вам следует подсоединиться, в Интернет, выйти на сайт который называется Office update это сайт фирмы Microsoft и загрузить соответственный vise art. После чего с помощью кнопки, обзор выйти на него и подключить его соответственно к вашей системе, ну в данном случае у нас есть, нажмем на кнопку, ОК и продолжим работать. Для того чтобы его запустить, мы с вами должны сделать вот что. Мы должны в меню, данные, выполнить команду, которая называется, мастер шаблонов. После того, как мы это сделали, у нас открывается шаг один из пяти, мастера шаблонов. В первом вот этом самом списочке, мы должны, с вами указать имя книги, на основе, которой мы собираемся этот самый шаблон и строить .

Наша книга если вы обратили внимание, называется Excel (version1), далее нам следует указать имя шаблона. По умолчанию, Excel создает имя шаблона точно таким же, как и имя книги, на основе которой он ее делает. Ну, сейчас мы с вами ничего менять не будем, просто нажмем на кнопочку, далее. После того как мы с вами нажали на кнопочку, далее. У нас возникает, мастер шаблонов, шаг два из пяти. Во-первых, в этом списке мы с вами должны указать формат базы данных, в которую мы помещаем записи, ну сейчас здесь установлен Microsoft Excel, хотя на самом деле это могут быть и другие форматы, например, Access, dBASE lll .

dBASE lV. Следующая строчка, где нас приглашают указать, имя и положение, базы данных, если нас вот это самое положение не устраивает, мы можем нажать на кнопочку обзор и выти на любое другое. Ну, сейчас нас это устраивает, нажимаем на кнопку далее. Здесь у нас имеется мастер шаблонов шаг три из пяти. Здесь нас приглашают указать ячейку, значение которой следует помещать базу данных. А затем указать имя поля. Что это значит, это значит, что вот, например если мы собираемся фамилию вводить вот здесь то мы должны указать адрес ячейки, В;9. Вот так вот .

А имя поля у нас пусть будет, фамилия. Так, давайте эту самую фамилию здесь укажем .

Давайте, перейдем сюда, здесь мы малость, ошиблись. Вот так. Это у нас, фамилия, давайте поднимемся ююю этих данных и посмотрим, что здесь у нас еще имеется. Соответственно у нас должна быть следующая строчка, которая называется, С;9. А здесь у нас собственно год рождения, давайте это мы здесь, введем, вот так. После того, как мы это все здесь ввели, мы со спокойной совестью нажимаем кнопочку, далее. Здесь нам сообщается, что мы можем добавить в базу данных данные из других книг Excel. Мы этот шаг с вами сейчас пропустим, потому что на самом деле ничего некуда добавлять не собираемся. Нажимаем на кнопку, далее и сейчас нам сообщается, что, мастер шаблонов создал базу данных и шаблон. Нажмем на кнопочку готово .

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

10.8.5. Создание файла на основе шаблона Сейчас мы с вами продолжаем работать с нашим шаблоном. На прошлом уроке мы собственно его создали. Как же с ним работают? Шаблоны используются для того, чтобы на их базе создавать новые рабочие книги. Что для того нужно? Для этого нужно выполнить команду, файл. Создать. В данном случае выбрать, что мы хотим создавать рабочую книгу именно на основе шаблона. Или на основе тех шаблонов, которые хранятся на нашем компьютере. Вот здесь в списке общих шаблонов у нас имеется следующий пункт, Excel (version1). Это тот самый шаблон, который мы с вами создали на прошлом уроке .

Нажмем на кнопку, ОК. Сейчас система безопасности нас предупреждает, что наш шаблон может содержать вирусы, потому что он содержит макросы, а макросы могут содержать вирусы .

Безопаснее всего отключать макросы, но при этом часть функциональности может быть утеряна .

Ну, давайте выберем не отключать макросы и откроем наш шаблон. Перед нами файл, который создан на основе нашего шаблона. Выглядит он собственно точно так же, как и предыдущий, вот Глава 10. Совместная работа Excel с офисными приложениями 205 таким образом мы собственно можем их сравнить, вот, мы с вами их сравнили и видим что-то же самое. Давайте его сохраним .

После того, как мы сохраняем вот этот вот файл, который создан на основе шаблона, Excel нам сообщает следующее, он сообщает, что данный документ связан с базой данных и предлагает, добавить новую запись или не изменять базу данных. Давайте выберем вариант добавить новую запись и нажмем кнопку, ОК. После чего, сохраним данный документ. Что мы сейчас можем с вами еще сделать, давайте введем, какую не будь другую информацию, например Прохоров и какое не будь число, предположим 1970-ый .

Ввели, еще раз сохраним нашу книгу, нам сообщается то же самое. Нам сообщается, что данный документ связана с базой данных, который, такая запись уже существует. Мы можем обновить существующую запись, добавить новую запись и не изменять базу данных. Ну, мы можем опять таки обновить выбираем первый вариант и нажимаем собственно, ОК. Таким образом, мы сейчас в шаблон вводим записи и после того как мы питаемся сохранить файл. Excel задает вопрос. Что делать с файлами? Ну, давайте закроем данный файл. Вот так вот. И на этом закончим текущий урок. Если вы обратили внимание, файл у нас остался один .

10.8.6. Сортировка Сейчас мы с вами начинаем новую тему, которая называется сортировка данных. Для чего предназначена сортировка данных? Она предназначена для удобного их представления .

Предположим у вас есть огромная телефонная книга, она была бы совершенно бесполезна. Если бы его нельзя было бы сортировать. Как делают сортировку в Excel? Это делается с помощью следующей команды, данные, сортировка. Предварительно нужно выделить некоторый список .

Давайте попробуем, что не будь выделить. Например, вот этот вот списочек и выполнить команду данные сортировка, как мы только что сказали .

Вот у нас открывается диалоговое окно. Вот оно .

Которая называется сортировка диапазона .

В этом окне, во-первых, мы можем задавать дополнительные параметры сортировки. То есть, как именно сортировать строки? Например, по возрастанию, по убыванию. Какой первый столбец сортировать? Видите, здесь имеется, вот имеется вот такой вот списочек. И как идентифицировать диапазон данных, то есть, по подписям, или по, обозначениям столбцов листа, вот такой вот список, опций. В Excel при сортировке используются следующий порядок данных, сначала это числа, затем текст, который упорядочивается по алфавиту, затем логические значения типа лож и истина, значение ошибок или пустых значений. Давайте закроем это диалоговое окно и продолжим разговор о средствах сортировки Excel. Необходимо заметить, что Excel предлагает на самом деле несколько средств сортировки, которые частично дублируют друг друга. Для сортировки данных у нас имеется команда сортировка, которую мы собственно с вами только что видели, вот она .

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

Кроме того, имеются еще некоторые способы сортировки списков, ну давайте займемся непосредственно сортировкой. Как мы только что сказали, данные сортируются при помощи 206 TeachPro Microsoft Excel 2003 команды сортировка. Еще раз выделяем диапазон, который мы с вами собирались сортировать, выполняем команды данные сортировка. Что мы здесь с вами делаем, во-первых, мы можем сортировать по фамилиям, то есть именно фамилии выставить в алфавитном порядке, а можем по году рождения, чтобы они были выстроены по возрастанию или по убыванию, это уже как мы решим. Ну, давайте пусть будет по фамилиям. Предположим, мы хотим, по возрастанию, то есть, по алфавитному порядку, здесь имеется вот эта группа, которая называется, идентифицировать диапазон данных, по. Мы с вами будем идентифицировать их по подписям, то есть первая строка диапазона. Если мы с вами переключимся, то здесь у нас уже будут не фамилия и год рождения, вот не эти колонки, а столбец, В и столбец, С. На самом деле удобнее это делать вот таким вот образом. Нажмем на кнопку ОК, посмотрим, что у нас при этом получиться. Если вы обратили внимание. У нас сейчас все отсортировалось, на самом деле в алфавитном порядке. Итак, поля мы отсортировали, все по алфавиту у нас расположено, рядом года рождений. К сожалению, здесь у нас нет дублирующихся фамилий. На самом деле, давайте попробуем их продублировать. Ну, давайте введем их, куда не будь сюда несколько строчек, предположим, создадим еще одного Иванова, вот он и предположим еще одного Сидорова, ну года рождения введем другие. Здесь предположим, у нас 1969 пусть будет, а здесь у нас пусть будет 1972 .

Сейчас попробуем отсортировать еще раз. Выделили эту таблицу и выполнили команду данные сортировка. Сейчас мы будем сортировать, по убыванию, на самом то деле, а годы рождений вот здесь мы с вами их выберем по возрастанию. Посмотрим, что у нас при этом получиться. Нажмем на кнопку ОК. Что у нас вышло? Самый близкий к началу алфавита, это у нас был Иванов, переместился на самое последнее место, а самый близкий к концу алфавита Сидоров на начальное, потому что мы с вами отсортировали по убыванию. Что же у нас получилось с годами? У нас получилось, с годами следующее, что года у нас на самом деле возрастают. Правда, они возрастают относительно имен. Сначала сортируются имена, а затем относительно их года, то есть сначала 1960 год потом 1969, потому что мы года поставили сортировать по возрастанию .

Ну, таким образом мы с вами упорядочили значения по двум ключам. Давайте попробуем выполнить по трем ключам. Введем здесь произвольно, какие не будь значения, пусть они будут даже вот такие вот цифровые и посмотрим, что у нас при этом получится. Выделили весь списочек, выполнили команду, данные, сортировка, предположим мы хотим вообще сортировать по столбцу D. Так как данный столбец у нас не имеет заголовка, здесь он просто обозначен столбец D, за тем по фамилиям, а уже затем по годам соответственно. Нажимаем на кнопку ОК .

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

–  –  –

того чтобы поменять местами наши столбцы, то есть отсортировать их по какому то признаку, нам достаточно вот здесь в строке диапазона переключить на столбцы диапазона. Тут у нас имеется так же сортировка по первому ключу. В данном случае она у нас отсутствует. Предположим мы хотим при этом учитывать регистр, хотя на самом деле у нас здесь это делать не обязательно, ну нажмем на кнопочку, ОК. И затем также на, ОК. Здесь у нас будет отсортировано, как вы видите, по Строке 4 .

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

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

Следующая тема, которую мы сейчас затронем, это индексация записи списка. Иногда в процессе работы возникает необходимости вернуться к тому состоянию, которая была до сортировки. Именно в таких случаях водиться понятие индексации. Что такое индекс? Индекс это специальное поле, в котором содержится уникальный номер для каждой записи. В качестве индекса обычно используют ну либо порядковый номер, либо дату и время внесения записи. Что нам следует сделать для того, чтобы индексировать нашу запись. Во-первых, нам понадобится определенный столбец, который смежен с нашим списком .

Вот у нас предположим вот этот самый столбец, что мы дальше делаем. Для первой записи вводим, число 1, выделяем эту ячейку, нажимаем клавишу Ctrl и протаскиваем мышь по всему столбцу. Индексация у нас фактически произведена. Потому, что столбец в нашем случае заполнен возрастающей последовательностью. Фактически он и является индексной последовательностью. В дальнейшем если мы будем проводить сортировку, вот давайте, например ее мы сейчас с вами проведем. Данные, сортировка, давайте, вернемся к строкам диапазона, ну предположим, хотя мы сейчас выделили на самом деле не совсем удачно. Нажмем, на кнопку, отмена, вот таким вот образом мы сейчас произведем выделение и опять выполним команду, данные, сортировка. Вернемся, на кнопочку, параметры и установим строки диапазона. Нажмем на кнопку, ОК и, предположим, сортировать мы будем вот по этому ключу, причем, сортировать по убыванию к примеру. Ну, пусть в данном случае это будет, например, Столбец С .

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

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

Давайте, выделим нашу базу данных, вот она. И выполним команду, данные, форма. У нас появилась вот наша форма, сейчас мы можем нажать на кнопочку, критерии. И ввести определенные критерии. Ну, давайте предположим, мы хотим с вами, вывести данные из этого списка, тех людей чьи, года рождения больше, например шестьдесят девятого года. Ну, давайте так и введем. Больше 1969-ого года. Ввели. Сейчас, нам надо нажать на кнопочку, далее. Первый, элемент у нас сейчас Сидоров, следующий, Сидоров который, родился в восьмидесятом году, и всё. Давайте, вот таким вот образом, просмотрим, все записи в нашей базе данных. Если вы 208 TeachPro Microsoft Excel 2003 видите, у нас первый идет, Петров который семидесятого года рождения, затем, Сидоров так сказать номер один. Затем, Сидоров номер два, и всё. Все остальные элементы данного списка не удовлетворяют нашему критерию. Сейчас, попробуем задать какой не, будь другой критерий. Как вы знаете, критерии можно задавать так же с помощью определенных шаблонов. Давайте, нажмем на кнопочку, критерий. Год рождения, мы сейчас с вами отсюда уберем. И хотим сделать выборку, по фамилиям. Причем, по фамилиям так чтоб, например, выбрались все элементы, которые начинаются с буквы, И. Ну, давайте введем вот это вот, (И), и поставим звездочку .

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

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

Перед этим, как использовать автофильтр, его нужно выделить. Затем, выполняем команду, данные, фильтр, автофильтр. Что у нас здесь происходит? У нас, вот здесь происходит появление вот таких вот кнопочек. Когда мы на них нажимаем, соответственно появляется, вот такой вот, списочек. Если мы, выделяем какой не, будь элемент с этого списочка, например, давайте выделим 1960-ый, у нас автоматически фильтруются данные, и мы видим на экране только, те элементы, которые удовлетворяют данному условию. То есть, годы рождений, которых равны 1960-и. Мы можем, например, сделать то же самое 1970. Но здесь у нас, в этом случае будет одна запись. Или, к примеру, восемьдесят, в данном случае, тоже одна запись. Если мы хотим увидеть всё, мы нажимаем на соответствующий элемент, увидеть всё. Какие еще элементы имеются?

Имеются, первые десять .

Если бы, наш список был большой, то на экране у нас, были бы видны, только первые десять записей. В данном случае, так как у нас, наша база данных, достаточно маленькая, то после того как мы это выбрали, у нас вот здесь проявляется вот такое вот окно. Где нам предлагается вообщем, каким то образом уменьшить. Ну, пусть, например, у нас будет три. Видите. Сейчас, у нас видны три наибольших элементов, в нашем списке. Ну, давайте получим, на экране все элементы. Затем еще имеется пункт, условие. Давайте мы его выберем. И получим вот такое вот диалоговое окно. Что мы здесь, можем сделать? Это диалоговое окно, называется, пользовательский автофильтр. Предположим, мы с вами хотим, опять таки выбрать, что бы, либо год рождения, к примеру, был бы больше 1970-и. Либо меньше. Давайте, здесь переключим, соответственно вот на этот элемент, потому, что мы хотим либо меньше, например 69-ого года .

Вот так. Нажмем на кнопочку, ОК и посмотрим, что у нас получилось на экране. Мы сейчас видим, те элементы списка, которые удовлетворяют данные комбинации условий .

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

10.8.9. Обработка фильтрованных данных Продолжаем изучение обработки отфильтрованных данных. С отфильтрованными данными можно производить большую часть тех же действий что и с обычными. Предположим мы хотим вывести отфильтрованные списки. Очень часто списки бывают достаточно большие. В данном Глава 10. Совместная работа Excel с офисными приложениями 209 случае у нас маленький список, но мы сейчас его увеличим. Мы скопируем вот этот фрагмент. И вот таким вот образом его размножим, вот так вот. Ну, здесь надо быть внимательным, после того как мы с вами это сделали нужно задать имя данному диапазону заново .

Имя как вы помните, у нас была, база данных. Вот, вот так вот. Задали заново имя .

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

Разделить. Что у нас получилось? У нас каждая область сейчас вида самостоятельно. На самом деле, это один и тот же файл, просто вот мы его можем вот таким вот образом, видите, вот так вот отдельно видеть. После того, как нам это надоест, мы выполняем команду окно и соответственно .

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

Вот мы, у нас здесь появились вот эти кнопочки, предположим, нас интересует вообще фамилия Иванов. Выбрали Иванов и сейчас на экране мы видим записи, в ячейки которых содержат вот эту самую фамилию, содержит Иванов. Мы с вами список несколько раз копировали, и Ивонов у нас встречается достаточно часто. Строки, которые данному критерию не соответствуют, у нас сейчас не видны, но они на самом деле не пропали. После того, как мы с вами уберем автофильтр, делается вот точно таким же образом, каким он ставиться, то мы эти строки видим на экране. Давайте, еще раз выполним команду автофильтр, вот так, вот у нас эти кнопочки и сейчас опять вызовем списки, содержащие фамилию Ивонов. Сейчас давайте попробуем скопировать эту информацию. Выделяем данный диапазон, выполняем команду, правка, копировать, а здесь сюда выполняем команду, правка, вставить. Если вы обратите внимание, вот по порядковым номерам у нас вставились только вот эти вот отфильтрованные данные. Если здесь у нас, например, выделилась строка 9, затем 10, а потом 15, потому что промежуточные данные не видны .

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

Мы хотим видеть данные больше чем 1969-й год, вот так. Вот сейчас мы видим вот эти данные, попробуем отсортировать, выделим, выполняем команду, данные, сортировка. Мы отсортируем, например по… На самом деле фамилии имени и отчеству, так удобнее. Нажмем, на кнопку ОК .

Видите, совершенно простым способом все данные у нас от сортировались. У нас сейчас на экране Сидоров, затем Петров. То есть мы с вами можем это делать, на отфильтрованных данных. Вот .

Точно так же мы можем построить по отфильтрованным данным диаграмму. Делаем к стати точно так же, как построение любой диаграммы. Давайте попробуем это сделать. Сейчас у нас данные отфильтрованные, выделим их и построим, какую не будь диаграмму. Ну, на самом деле понятно, что диаграмма у нас будет достаточно странная, по таким то данным, ну можем сделать, что-то в этом роде, хотя можно сделать и вот так. Значения пусть у нас будут вот эти вот, нажмем 210 TeachPro Microsoft Excel 2003 на кнопку, далее, далее и готово. Ставим на имеющийся лист, вот у нас получились вот такие вот данные. То есть вот такая вот диаграмма .

Причем обратите внимание, построена эта диаграмма именно по отфильтрованным данным, то есть на экране мы видим только данные по Петрову с Сидорову. Все, что у нас осталось, как бы между строк, на диаграмме не видно. К стати попробуем убрать фильтр. Давайте выведем на экран все элементы, вот сейчас у нас на экране все элементы и если вы обратите внимание. Диаграмма у нас соответственно изменилась. У нас сейчас на экране есть и Иванов и Петров и сидоров. Все элементы присутствуют в списке. Вот такая вот у нас получилась диаграмма .

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

Давайте, эту диаграмму мы с вами уберем. Сейчас она нам не нужна, отфильтруем наши данные, ну введем это любимое нами условие больше 1969-ого года, нажмем на кнопочку, ОК, вот наши данные на экране и попробуем обнаружить вот эту команду, о которой мы только что говорили .

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

Закрыть. Сейчас вот этот фрагмент мы с вами выделим именно таким образом .

Видите, вот мы так вот нажали, сейчас построим диаграмму, она у нас опять таки линейчатая, такая, какую мы строили только что, перейдем на ряд, значения мы с вами как уже собирались, зададим вот эти, вот, далее и готово. Вот наша диаграмма. Теперь попробуем снять автофильтор и посмотрим, что у нас получиться, если вы обратили внимание, ничего у нас глобально собственно и не изменилось. Вот у нас сейчас на экране все, но видим мы только на самом деле подписи Петрову и Сидорову, все остальное мы просто не видим .

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

–  –  –

точно так же как любое приложение Microsoft Office. Давайте, посмотрим на наши компоненты .

Выполняем из меню, пуск команду, настройка, затем, панель управления, выбираем, установку и удаления программ, сейчас мы с вами их найдем, вот, сделаем двойной щелчок мышью. И выбираем Microsoft Office профессиональный выпуск версии 2003. Нажимаем, на кнопочку, изменить. Ну, давайте, предположим мы хотим добавить или удалить компоненты. Нажмем на кнопку, далее и посмотрим, что у нас здесь имеется. У нас в данном случае имеется, в общем, список тех приложений, которые установлены. Можем поставить вот эту галочку и нажать на кнопку далее. Сейчас если мы внимательно поищем вот здесь, мы собственно найдем Microsoft Query. Ну, давайте средства Microsoft Office, что у нас здесь имеется?

Вот он Microsoft Query. Как нам сообщается, он обеспечивает прямую связь с базами данных для анализа данных в Excel. Ну, в данном случае он у нас установлен, как вы видите, по этому мы нажмем на кнопку, отмена, прерывая установку. И продолжим, работать. Мы убедились, что он у нас есть. Что мы сейчас с вами сделаем? Мы сделаем вот, мы попробуем запустить Microsoft Query. Он у нас запускается двумя способами. Первый способ заключается в следующем, если нам требуется возвратить полученные данные прямо в Excel, то в Microsoft Query мы запускаем именно из Excel, при этом вы должны помнить, что Microsoft Query является самостоятельным приложением, который собственно тоже можно запускать из меню? пуск. Сейчас, мы пробуем, это сделать из Excel. Выполняем команду? данные, импорт внешних данных .

В данном случае у нас все эти команды не активны. Нам нужно создать новый лист Excel и только после этого попробовать выполнить эту команду, вот у нас уже три команды здесь активны. Сейчас мы выполним следующую команду, создать запрос. Здесь мы должны, выбрать какой именно базой данных мы с вами хотим работать. Перед нами как вы видите, открылось окно, который называется выбор источника данных. Нам сейчас нужно определить определенный источник. Ну, давайте предположим мы хотим работать с базой данных в Microsoft Access. Мы ее выделили, нажали на кнопочку, ОК. Сейчас у нас происходит подключение к базе данных .

Давайте, сейчас попробуем найти ту базу данных, которую поставляет Microsoft для обучения работе в Access. Эта база данных называется база данных, Борей. Сейчас мы с вами и ее, ну я думаю вот здесь мы с вами ее, и обнаружим. Вот у нас имеется база данных Борей. Нажмем, на кнопку, ОК. И у нас возникло вот такое вот диалоговое окно. Ну, сейчас мы с вами не будем ничего включать в запрос, мы с вами просто показали, как именно запускать Microsoft Query, нажмем на кнопку отмена .

Изменять запрос Microsoft Query мы не будем .

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

Но в диалоговом окне, выбор источника данных мы можем выбрать собственно совершенно новый источник, не существующий Access, не dBASE, не даже Excel, а просто новый источник данных. И после чего нажмем на кнопочку, ОК. У нас появилось диалоговое окно, которое называется, создание нового источника данных .

Сейчас мы попробуем создать какое не, будь имя .

Ну, давайте просто назовем его, источник. Вот что-то в этом роде. В следующем окне следует выбрать драйвер, для требуемого типа базы данных. В этом списке мы с вами опять выберем драйвер для Microsoft Access .

Но вот, к примеру, вот он и нажмем на кнопочку связь. Вот у нас возникла следующее диалоговое окно, которое называется, Установка драйвера ODBS для Microsoft Access. Данные, которые сюда необходимо вводить зависит от конкретного источника для данных, для Microsoft 212 TeachPro Microsoft Excel 2003 Access нужно вводить следующее. Во-первых, имя, базы данных и установить, что именно делать, выбрать базу данных, например, нажмем на кнопку, отмена. Создать, с помощью этой кнопочки мы создадим новую базу данных, восстановить, к примеру, здесь надо указать какую именно базу данных, ну и сжать соответственно. Здесь опять таки нужно выбрать какую именно базу данных мы будем сжимать. Сейчас мы с вами ничего этого делать не будем, мы просто нажмем на кнопку отмена и закроем эти диалоговые окна. Хотя на самом деле мы можем сделать вот что, мы могли бы нажать на кнопочку связь и выбрать нашу базу, Борей. Вот, тут нам опять придется, найти установленный, Microsoft Office, затем Office11. И здесь найти Samples, для того, чтобы найти, где именно находиться наша база, Борей .

К стати вот он. Нажмем на кнопку, ОК. И еще раз на кнопку, ОК. Вот связь, мы с вами создали .

Ну, давайте нажмем на кнопку, ОК и посмотрим, что мы с вами делаем дальше. Вот у нас сейчас имеется здесь новый пункт, который называется источник, мы только что с вами его создали .

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

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

Если мы хотим получить на экране все данные, никаких условий не вводить, то нам достаточно просто нажать на кнопку далее .

Если мы хотим получить определенные данные, то делаем следующее. Предположим мы хотим получить следующие данные, мы хотим, чтобы домашний телефон данного сотрудника был бы равен, давайте здесь напишем нулю, то есть был бы, например пустым, его бы, например не было или начинался бы ну, например с 251, вот так. Мы ввели, какие то определенные критерии, какие то определенные условия. Давайте, сейчас нажмем на кнопку, далее и посмотрим, что нас ждет дальше. Здесь нам предлагается установить, по какому именно столбцу нам нужно сортировать данные. Ну, давайте логично выбрать по фамилии сотрудников. Ну, и соответственно по возрастанию, то есть, чтоб это у нас было бы от, А до, Я соответственно. Затем, опять нажимаем на кнопочку, далее. Здесь мы с вами выбираем из трех пунктов, первый, Вернуть данные в Microsoft Office Excel. Второй пункт просмотреть или изменить данные Microsoft Query и Создание куба OLAP из данного запроса. Что такое, куб OLAP, мы с вами совершенно не знаем .

По этому пусть у нас останется, Вернуть данные в Microsoft Office Excel .

Ну, давайте сейчас нажмем на кнопку, готово, Microsoft Query нам сообщает, что у нас имеется синтаксическая ошибка. Почему это может произойти? Потому, что данные в таблице могут быть, чем-то не устраивать Microsoft Query, но может быть пустые строки, пустые символы и так далее .

Ну, давайте нажмем на кнопку, ОК. И постараемся вернуться обратно. Предположим мы вообще не хотим, чтобы были, какие не будь критерии. В данном случае, критерий у нас есть, мы сейчас установим так, чтобы их не было вообще, чтобы нормально все у нас как-то работало бы. Готово .

Видите, сейчас нам предлагается поместить данные на имеющийся лист, давайте согласимся с этим и мы видим, вот следующий список наших сотрудников .

Они у нас были выведены соответственно уже, без каких либо критериев. Просто выведено все подряд. Таким образом, мы с вами смогли с помощью Microsoft Query из базы данных Access получить в Excel-е определенные данные. На следующем уроке мы с вами научимся писать по этим данным более интересные запросы, а сейчас завершим знакомство с Microsoft Query .

Глава 10. Совместная работа Excel с офисными приложениями 213 10 .

9.2. Импорт внешних данных. Запросы Итак, на прошлом уроке мы с вами попробовали создать наш первый запрос. Рассмотрим все окна мастера запросов более подробно. Давайте, выполним еще раз команду, данные, импорт внешних данных, и соответственно, создать запрос. Но сейчас мы с вами пойдем другим путем .

Что мы сейчас с вами сделаем? Мы выберем не источник данных, который у нас был, а собственно базу данных Microsoft Access. Нажимаем, кнопочку, ОК и в появившемся окне пытаемся найти еще раз нашу базу, Борей. Как вы помните, это у нас в программах, Microsoft Office, Office11 и примеры, то есть Samples. Сейчас мы с вами найдем. Вот Борей.mdb. Давайте, сейчас опять выберем список сотрудников, давайте найдем этот списочек .

Вот наши сотрудники. Выберем весь список. Вот так, нажмем на кнопку, далее. Вот здесь мы можем, создать определенный запрос, а можно выбрать собственно весь список. Здесь можем задать условия. Ну, сейчас мы с вами это задавать не будем, сортировать собственно тоже не будем, и нажмем на кнопку, далее. Вот здесь, у нас имеется последнее окно создание запроса, заключительный шаг, здесь мы это окно на прошлом уроке рассмотрели поверхностно. А сейчас рассмотрим более подробно. Здесь имеется вот этот вот переключатель, у которого собственно три состояния, как мы с вами уже сказали. Первым пунктом мы с вами воспользовались на прошлом уроке, мы вернули данные Microsoft Office Excel. Второе состояние .

Просмотр или изменение данных Microsoft Query. При выборе этого переключателя, мы можем усовершенствовать запрос, который создается мастером запросов. То есть, например, установить, дополнительные условия фильтрации. Или, например, сформатировать данные. И полученные в результате данные все равно вернуться в Excel. Ну, и последний пункт. Это создание куба OLAP, из данного запроса, вот это состояние. Что такое куб OLAP. OLAP, это данные, которые иерархически собраны, таким образом, что хранятся в кубах. Не в таблицах, а кубах, они трехмерные. Это достаточно сложное понятие, сейчас мы на нем останавливаться не будем, а изучим его в дальнейшем .

Ну, если мы с вами вернем данные в Excel и нажмем на кнопочку, готово. То, в данном случае, точно так же как на прошлом уроке нам будет предложно вставить данные либо, в имеющиеся листы, либо в новый. Собственно мы с вами можем поместить их в новый лист. И нажать на кнопку, ОК. Вот у нас, на листе номер четыре, образовалась точно такая же таблица, как на прошлом уроке. Если мы с вами, хотим схоронить запрос, то нам необходимо для этого нажать на соответствующую кнопочку. Предположим, что мы хотим создать сложный запрос. Необходимо заметить, что в не зависимости от того используется мастер запросов или нет, создание запроса начинается с выбора источника данных. В диалоговом окне выбор источника данных, соответственно. Давайте, еще раз выполним команду, данные, импорт внешних данных, создать запрос. Ну, в данном случае у нас уже результат запроса выведен на экран. Давайте, перейдем на новый чистый лист и сделаем это еще раз, создать запрос. Предположим мы с вами не хотим использовать мастера запросов .

Уберем эту галочку и продолжим работу. Выберем собственно нашу базу, сейчас мы с вами ее выберем, она находиться в Program Files, Microsoft Office, Office11, база, сначала Samples. Вот база, Борей. Сейчас мы с вами видим интерфейс Microsoft Query, это стандартный интерфейс .

Окно запроса имеет несколько подокон, давайте откроем, какие не будь таблицы. Ну, например заказы, пусть это будет, добавить, ну затем откроем таблицу клиенты .

214 TeachPro Microsoft Excel 2003

–  –  –

в среде Microsoft Query, что особенно важно. На следующем уроке мы продолжим изучение этой среды и займемся, объединим таблицы. Далее создадим определенные связи .

10.9.3. Работа с запросам и базами данных Microsoft Access Итак, на прошлом уроке мы с вами создали наш прошлый запрос в среде Microsoft Query. Его результат мы сейчас в данный момент наблюдаем на нашем экране. Давайте, попробуем сделать еще раз. Закроем, Microsoft Query. Не будем ничего импортировать на наш лист, нажмем на кнопку, отмена и продолжим .

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

Вызываем базу данных Microsoft Access, ОК. Сейчас, найдем нашу базу данных, эта база данных Борей. Сейчас, она у нас появиться на экране, вот Samples, вот наша база, Борей. Так как галочка у нас не стояла, соответственно у нас сразу появляется Microsoft Query. Сейчас, мы видим диалоговое окно, добавление таблицы. Сейчас добавляем следующие таблицы, как уже только что сделали, заказы, затем, клиенты, а затем сотрудники, точно так же как мы это делали это в прошлый раз, добавили. После чего нажимаем на кнопочку, закрыть .

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

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

Давайте, откроем эти окна на весь экран, чтобы нам было лучше видно, вот таким вот образом, вот код сотрудника, это тоже ключ .

Здесь, если мы хорошо откроем эту базу, вот здесь тоже увидим, что это вот у нас фактически связь происходит за счет вот этих вот ключей. Для указания связи таблицы используют такой интересный термин, который называется, обеднение. Обеднение показывает, какого типа связь между двумя таблицами. Мы сейчас, выбрали три таблицы. Microsoft Query, автоматически создал их объединение. Он обнаружил, что здесь имеются код сотрудника, код клиента, соответственно показал эти связи и так далее. То есть две связи Microsoft Query создал автоматически. Сейчас мы с вами попробуем создать еще один запрос. Для начала откроем поле условия, давайте мы с вами это сделаем. Во-первых, чтобы оно было видно на экране. Предположим мы хотим для поля условия, вот здесь сделать вот что. Мы хотим, чтобы поля условий входили бы, во-первых, сотрудники. Где у нас, сейчас мы их найдем и еще сейчас мы найдем имя сотрудника и мы хотим, чтобы это имя имело бы значение, например, Анна. Сейчас, мы на русском языке мы с вами введем, вот, слово Анна мы с вами ввели .

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

Пусть это будет сотрудники город, вот сейчас мы здесь город обнаружим, на самом деле чего-то, 216 TeachPro Microsoft Excel 2003

–  –  –

10.9.4. Запросы Microsoft Access и запросы SQL Продолжаем тему создания запроса. Необходимо заметить, что в Microsoft Query, во-первых, мы можем запоминать запрос, а затем одновременно работать с несколькими запросами. В процессе определения запроса, можно открыть имеющийся запрос, или создать новый. Давайте, сохраним какой не, будь запрос, то есть тат, который создан нами сейчас. Сохранить запрос. Ну, Глава 10. Совместная работа Excel с офисными приложениями 217 предположим запрос из базы данных Microsoft Access, сохраняем. Что мы сейчас с вами будем делать? Давайте, все закроем, все, что у нас есть на экране и выполним команду, файл .

Открыть запрос, вот запрос, который мы с вами только что сохранили. Нажимаем, на кнопочку, открыть и вот этот запрос перед нами. Мы сейчас его и видим, и видим собственно результат его выполнения. Как вы знайте, выполнение запросов позволяет получать или обновлять информацию из области данных. Выполнение запроса осуществляется либо вручную, либо в автоматическом режиме. Переключение осуществляется с помощью вот этих вот двух кнопочек, вот это вот у нас автоматический режим. Вот эта кнопка, а это выполнение запроса вручную. Давайте, изучим вот эту вот панель инструментов, вообще посмотрим, что здесь у нас имеется. Первые кнопочки, первое это создать запрос, открыть запрос, сохранить запрос и вообще выйти и вернуть данные .

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

Если мы хотим, чтоб наш запрос выполнялся бы автоматически, вот здесь записи, следует устанавливать вот эту галочку, автоматический режим. Предположим, мы с вами отключаем этот автоматический режим, давайте проверим, что у нас выключен, сейчас посмотрим, как выполняется вручную. Давайте, что не будь, изменим в данном запросе, здесь сделаем не Анна, а, например, Нина. Для того чтобы сейчас вручную выполнить запрос нам нужно сделать вот, что. Либо, например, нажать вот эту кнопочку выполнения запроса .

Давайте нажмем. Со значением Нина, у нас в данном случае ничего нет. Давайте, попробуем поменять это на, Елена, может быть в данном случае, нам повезет больше. Нет. Кстати, в общем, то не повезло. Ну, давайте, переменим это на, Анна соответственно, точно так же как у нас было, вот видите, несмотря на то, что мы ввели значение, у нас данные автоматически не приводятся, потому что выключена вот эта галочка, автоматический режим. Мы выполняем запрос, сейчас у нас данные имеются на экране .

Необходимо заметить, что в среде Microsoft Query можно выполнять инструкции на языке SQL, то есть можно создавать запрос вообще без интерфейса Microsoft Query. Изучение этого языка, к стати не входит в рамку данного курса, но мы можем просмотреть вот этот вот запрос, который вот у нас сейчас в данный момент имеется на вот этом самом языке. Как мы это делаем?

Мы из меню, вид выполняем команду, запрос SQL .

Вот, сейчас мы видим, инструкцию SQL, вот она. Первая команда Select. Select это выбрать. У нас выбирается из таблицы сотрудники, сначала идет таблица, затем через кочку идет ее поле .

Выбирается поле имя, затем поле фамилия, поле город, соответственно из базы Борей, который у нас на экране имеются заказы, клиенты и сотрудники, вот. Собственно вот такие вот условия, вот это все запрос. Выбирается, что затем откуда, а затем вот слово, Where, где идет уже непосредственно условия, вот. Вот эта наша собственно идет условия, условия это имя сотрудника должно быть равно Анна. В общем, понятно .

Этот запрос можно соответственно вручную редактировать. Ну, что мы с вами можем сделать?

Мы можем, например, выбирать ни полный вот этот вот комплект, а только сотрудники и имя .

Сейчас мы аккуратно это сотрем. Вот так вот, нажмем на кнопочку, ОК. И посмотрим, что у нас при этом поменялось, все, здесь в результате мы видим только Анна, мы не видим не фамилии не 218 TeachPro Microsoft Excel 2003 города соответственно. Наша таблица, в общем, значительно уменьшилась. На этом уроке мы с вами фактически научились редактировать и использовать запросы, давайте на этом урок закончим. На следующем уроке мы будем форматировать данные Microsoft Query .

–  –  –

Вот так. Сейчас, перетаскиваем обратно фактически, вот мы, таким образом, перетащили наши столбцы. Что еще у нас имеется? Мы можем, выполнить команду, например записи и соответственно добавить столбец. Ну, давайте, добавим какой не, будь столбец, например вот, всетаки, добавим фамилию. Вот так вот. И закроем данное окно. Видите, у нас здесь добавилась также фамилия, выполнить, запрос. Сейчас фамилия у нас присутствует на экране .

Ну, на этом уроке мы поняли, как форматировать данные Microsoft Query. Как работать соответственно со столбцами, как их добавлять менять ширину или высоту? На следующем уроке мы будем изучать таблицу результатов .

10.9.6. Возврат данных в Excel На этом уроке мы с вами начинаем тему, которая касается перемещения и выделения таблицы результатов. Как мы уже говорили на прошлых уроках, записи и поля базы данных в таблице результатов представлены в виде строк и столбцов, вот собственно строки и столбцы. Что мы сейчас с вами будем делать? Мы сейчас заметим, что текущая строка у нас помечена с боку вот такой вот стрелочкой. Предположим мы хотим переместиться на строку с определенным номером .

Что мы сейчас сделаем? Мы фактически вот здесь, предположим, хотим переместиться, на пятую строку вели, 5 нажали клавишу Enter, то есть клавишу ввод. Сейчас мы для перехода будем попробовать некоторые комбинации клавиш. Если мы нажмем сейчас клавишу F5, то, что у нас получиться? Ну, например, давайте перейдем сюда и нажмем клавишу F5. Вы видите, активизировалась вот это вот нижнее поле фактически, сейчас мы можем ввести туда, уже любое число и нажать на клавишу Enter. Опять нажимаем F5, активизировалось вот это нижнее поле, фокус ввода вот здесь, мы введем, например 12, нажмем клавишу Enter, мы перешли соответственно на 12-ое поле .

Давайте к стати здесь уберем вот этот вот критерий, вот это вот условие. Для того, что бы мы получили больше данных, вот так вот, мы хотим получить наши данные, чтобы были бы и Павел и Ольга и Мария, чтоб было бы какое то разнообразие. Что еще у нас имеется, ну давайте введем сюда, например цифру 6. Нажмем на клавишу Enter. Если мы нажмем сочетание клавиш Ctrl+Enter, то мы фактически перейдем вот таким вот образом, если у нас здесь введено, например 8, нажали Ctrl+Enter, мы вот таким вот образом стерли старые данные, вот еще раз попробуем 78, видите. Фактически мы стираем данные, вот так вот .

Что еще у нас имеется? С помощью стрелочек, если у нас активизировано вот это поле, стрелки в бок вот таким вот образом у нас переключаются с записи на записи. С помощью стрелочек вверх и вниз, соответственно мы перемещаемся по вертикали, вот. Предположим мы хотим выделить, всю текущую строку, мы нажимаем клавишу Shift и пробел. Вот видите, у нас выделилась вся текущая строка. Если у нас большая таблица результатов то с помощью нажатой клавиши Shift и Page Down, вот так вот, мы можем соответственно вот таким вот образом на весь выделенный диапазон перемещаться по нашему экрану. То же самое соответственно Shift+Page Up, просто это происходит соответственно вверх. Если мы хотим выделить всю таблицу результатов, что мы делаем? Мы нажимаем следующее сочетание клавиш, Ctrl+Shift и пробел .

Вот у нас произошло выделение всей таблицы результатов. Сейчас мы с вами поговорим о редактировании данных в таблице результатов. Необходимо заметить, что редактирование данных 220 TeachPro Microsoft Excel 2003 в этой самой таблице влечет за собой соответствующие изменения в исходной базе данных. Ну, это, в общем, то и понятно. В каких случаях изменение содержимого базы данных, которые участвуют в запросе не возможно. Во-первых, естественно, когда источник данных защищен от изменения. Во-вторых, когда запрос строиться на нескольких таблицах и содержит групповые операции. То есть те, которые соединены с помощью (и) или (или). Или, например, содержит такие операции как сумма, среднее значение и так далее. И, последнее. К стати наиболее часто встречающийся случай, когда администратор базы данных запретил изменение содержимого .

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

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

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

Вот у нас имеется вот такой вот список. Сейчас мы с вами попробуем менять содержимое записей результатов, как мы уже с вами только что сказали. По этому выполняем команду, записи, разрешить правку, вот сейчас если вы обратили внимание, ничего нам не сообщается, что у нас многотабличный запрос. Таблицы результатов выбираем, например какой не будь столбец, где мы хотим что-то поменять. Предположим мы хотим поменять, к примеру, Инна на ну что-то типа Ирина. Давайте, это введем, вот. Мы сейчас изменили и нажимаем клавишу ввод, то есть клавишу Enter. Изменения у нас произошли. Предположим мы хотим, удалить какой либо кусок с содержимым таблицы результатов. Как мы это делаем? Перед этим нам опять нужно выполнить команду, разрешить правку, в случае если эта галочка не установлена, в данном случае эта галочка у нас есть. Выделяем нужную сточку и выполняем следующую команду, правка соответственно удалить .

Сейчас нам задается вопрос. Удалить ли следующее число записи, равное единицы. Мы с этим соглашаемся. Давайте, мы сейчас это все удалим. На, что нам сообщается, что удаление или изменение записи не возможно, в таблице заказы, имеются связанные записи. Это кстати то, о чем мы говорили в самом начале. Удалить записи, если имеются связанные с ними не возможно. Нам придется, с этим согласится, нажмем на кнопку, ОК. Кстати именно по этому у нас была закрыта, операция, вырезать. Потому, что Microsoft Query знал, что здесь имеются связанные элементы .

Сейчас нам осталось освоить последнюю операцию. Передачу данных из Microsoft Query обратно в Excel. Так как Microsoft Query, у нас был запущен не как отдельное приложение, а непосредственно из среды Microsoft Excel, то мы выполняем следующую команду, меню, файл, выполняем команду? которая называется, вернуть данные в Microsoft Office Excel .

Сейчас нам предлагается, поместить наши данные, либо в имеющийся лист на эту выделенную ранее позицию, либо на новый лист. Нажимаем на кнопочку, ОК и соответственно получаем вот эти вот данные. Именно те, которые у нас были, в свое время там созданы. Если бы у нас Microsoft Query, был бы запущен как отдельное приложение, то мы могли бы, например, эти данные получить здесь с помощью буфера обмена. Необходимо заметить, что с результатами запросов к внешним базам данных можно работать, так же как и с любыми другими данными. То есть мы можем их копировать, изменять и так далее. Если нам необходимо результаты запросов обновить, что мы должны сделать? Мы должны выполнить команду данные и здесь имеется вот эта команда, которая называется, обновить данные, вот так. Сейчас нам сообщается, что запрос используется при обновлении данных, для импорта, внешних данных в Excel, но предупреждают, что данные могут быть, на самом деле созданы, злонамеренными пользователями. Мы сообщаем, что мы доверяем источнику, данного файла нажав на кнопку, ОК. И в данном случае у нас происходит Глава 10. Совместная работа Excel с офисными приложениями 221 такое вот фоновое обращение к Microsoft Query, и данные на самом деле обновляются. Вот к стати здесь имеется даже вот такая вот кнопочка, которая называется, обновить все .

Ну, сейчас давайте на этом, закончим текущий урок, мы фактически научились работать с Microsoft Query, получать данные из Microsoft Query и передавать данные соответственно в неё .

На следующем уроке мы с вами начнем тему, работа в Excel с данными различных форматов .

10.10. Работа с данными различных форматов в Microsoft Excel 10.10.1. Типы данных, с которыми работает Microsoft Excel На этом уроке мы начинаем работу в Excel с данными в различных форматах. Необходимо заметить, что Excel может получать данные не только с расширением xls и баз данных, но и с файлов других типов, а также, например, непосредственно со страниц Web. В Excel имеется целый набор средств для работы с данными нестандартного формата .

90% случаев экспортирования файлов совершается с помощью вот этой команды. Из меню File выполняем команду Cохранить как… Вот здесь раскрывается вот такое вот окно. И вот здесь в нижнем списке мы можем посмотреть, как именно мы можем сохранить наш файл. Первый тип, это книга Microsoft Office Excel. Это обычный файл, который поддерживается Microsoft Office Excel. Затем идет Таблица XML .

Поддержка языка XML появилась, начиная с Office-а 2002. Ранее этой возможности, собственно, не было. А сейчас уже и наша версия, а это 2003, может это делать, поддерживать XML. Ну, соответственно XML данных. Затем идут Web страницы. Web страницы в одном файле. И просто Web страницы. Разницу мы с вами пройдем чуть позже. Шаблон. Что такое Шаблон вы знаете. С шаблонами мы с вами уже работали. Затем идут текстовые файлы. На самом деле текстовых файлов может быть несколько. Мы сейчас их увидим. Все зависит от разделителей. Может быть разделители между данными табуляции, как в данном случае. Может быть запятая, Может быть пробел и так далее .

Мы сейчас позже это посмотрим. Tекст Юникод. Символы Юникод это те символы, которые кодируются не одним байтом, как обычно, а двумя. Затем идет устарелый формат. Это книга Microsoft Excel версии 5.0 / девяносто пятого И, соответственно, вот такая вот. Затем, CSV – разделители - запятые. Фактически тот же самый текстовый формат, только разделитель у нас будет уже не табуляция, а запятая. Затем идут опять таки старые версии File Microsoft Excel 4.0, 3.0, 2.1 и так далее. Вот здесь. Вот здесь у нас это форматы электронных таблиц LOTUS. LOTUS – это распространенная программа, работающая с табличными данными. И для совместимости, собственно, из Excel прямо можно сохранять формат LOTUS. Дальше идет вот этот формат. Это Quattro Pro/DOS. Как Windows-овская версия, так и вы видите здесь DOS-овская. Затем идет тип баз данных, созданных на языке dBAZE - dBASE IV, dBASE III и dBASE II. Потом форматированный текст с разделительными пробелами .

Для совместимости имеется так же текстовый формат Macintosh. Все Macintosh, собственно их версии для Dos-а так же имеются. Также Interchange Format. SYLK это Symbolic Link. Этот формат используется при переносе данных в программу Multyplan. Ну, и последний пункт, 222 TeachPro Microsoft Excel 2003 соответственно, Настройка Microsoft Office Excel. Ну давайте попробуем что-нибудь экспортировать, например, имеющуюся таблицу, к примеру в XML. Выбираем здесь таблицу XML, И выбираем какое-нибудь название. Предположим table, или таблица1, к примеру. Таблица1

– сохраняем. Вот. Сохранили наш файл в виде XML .

Давайте попробуем с помощью программы блокнот, его с вами открыть. Сейчас мы с вами найдем программу блокнот. Вот она. Откроем созданный файл. Как вы помните, это у нас был table1. Тип файлов сделаем - Все файлы, чтобы увидеть сразу все. И поищем table1. Вот он .

Открыли. Вы сейчас видите здесь перед собой обычный XML файл. Если посмотрите внимательно, везде присутствуют открывающий тег, вот, например, Author. Закрывающий тег .

Нормальный обычный XML файл. Экспорт данных в виде текста позволяет легко решать проблему совместимости платформ. Основный недостаток теряется форматирование. А достоинство - это независимость и небольшой объем. Ну, давайте попробуем сохранить нашу рабочую книгу, как текст. Выполняем команду File - Сохранить как. Давайте здесь выберем текст .

Ну. Давайте, текстовый файл с разделителями табуляции. Вот так. А имя файла, у нас будет просто текст. Сохраняем. Сейчас нас Excel предупреждает, что в файле выбранного типа, может быть сохранен только текущий лист. Мы с этим соглашаемся .

Затем нас предупреждают, что форматирование может быть утеряно. Мы, собственно, даже готовы на это. Все, мы с вами все сохранили. А теперь попробуем открыть наш файл, опять таки при помощи программы Блокнот. И посмотреть, что у нас, при этом получилось. Здесь мы тип файлов выбираем. Можем выбрать все файлы. А можем, собственно, оставить и текстовые .

Видите, текст наш на экране имеется. Вот у нас получился вот такой вот текстовый файл .

Если вы обратите внимание, разделитель у нас от табуляции. С помощью стрелок вот таким вот образом по ним перепрыгиваем по нашим данным. Ну способы импорта данных мы пройдем на следующем уроке. А на этом мы, фактически, представили, как делается экспорт данных из Microsoft Excel .

10.10.2. Способы обработки данных Сейчас поговорим о способах импорта данных. Большим достоинством Excel является возможность обработки данных, которые созданы в другом приложении. Это свойство позволяет производить более простую интеграцию различных программных средств. Для того, чтобы открыть файл, сохраненный в одном из допустимых форматов, выполняем следующие действия. С помощью меню File выполняем команду Открыть .

Вот у нас открылось вот такое вот диалоговое окно – Открытие документов. В списке Тип файлов мы выбираем нужный нам. Предположим мы хотим открыть файлы XML. Здесь у нас имеется сохраненный нами ранее файл XML. Пусть это будет в нашем случае table1. Ну давайте сделаем двойной щелчок. И мы увидим тот файл, который сохранили с вами ранее, а именно на прошлом уроке. Ну давайте попробуем открыть, например, базу данных. Какую-нибудь базу данных, к примеру, например Access. Посмотрим, имеется ли она у нас. Здесь вот имеется вот такая вот база данных, которая называется Заказы на работу .

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

И следующие запросы. Вот они. Внизу у нас расположены таблицы. А вот сверху такими вот значечками помечены запросы. Ну, предположим, нас интересует только одна таблица, к примеру, Клиент. Мы с вами ее выбрали. И нажмем на кнопочку OK. Вот .

У нас открылись вот такие вот данные. На самом деле данных у нас сейчас нету, а есть только названия полей. То есть таблица у нас оказывается была не заполнена. На самом деле вообщем-то Глава 10. Совместная работа Excel с офисными приложениями 223 понятно, как это делается. Давайте закроем не сохраняя изменений Книги3. И продолжим нашу работу. Сейчас попробуем поработать с текстовым форматом .

Выполняем команду File. Опять таки Открыть. Здесь мы с вами выбираем уже не Базы данных, а соответственно Текстовый файл. У нас был сохраненный ранее текст. Вот он. Сейчас нам здесь предлагается выбрать разделители. Видите у нас. Мы сейчас видим список. Но нам нужно сделать так, чтобы каждый столбец был в своей колонке, то есть, в своем месте. Так в случае у нас с разделителями, то есть, значение полей отделяются знаками разделителей. А сейчас здесь уже Excel автоматически распознал, что разделители у нас – знак табуляции. Ни точка с запятой. К примеру, ни пробел, ни запятая, а, именно, знак табуляции. Кстати, если мы поставим пробел и уберем знак табуляции, у нас будет вот таким вот странным образом поделен файл .

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

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

На самом деле же это не так. Например, здесь у нас явно текстовый формат должен быть. Вот здесь. Здесь не понятно. У нас здесь присутствует и объем продаж, и, вообщем-то, год, и, просто, данный .

Значит, он вполне может остаться и общим .

Собственно, как и здесь. Вот это, то, что текстовый, достаточно однозначно. Сейчас мы можем сделать вот что. Можно нажать на кнопочку готово. И в результате, если вы обратили внимание, у нас текстовый файл был импортирован вот в нашу таблицу. Причем если посмотрите совсем внимательно рабочий лист у нас имеет имя, название файла. Можно попробовать сделать то же самое, но сделать собственно с другими разделителями. Давайте попробуем. Выберем опять таки Текст. Нам сообщается что файл Текст уже открыт. Но мы не взирая на грозное предупреждение, его сейчас откроем. Сейчас мы можем здесь выбрать формат файла, например, скажем кириллицу в Windows. Например, вот такая кириллица KOI8-U. Видите, здесь у нас начертания все изменилось. Нам нужно обязательно вернуть обратно .

А здесь указывается с какой строки нужно начать импорт. Фиксированная ширина, это, когда поля имеют заданную ширину. Ширина определяется по количеству символов. А здесь будут искать разделитель. Ну давайте попробуем сделать вот что. Этот знак табуляции убрать. И мы предположим, что разделен другими элементами, например запятой. Посмотрим, что у нас, при этом, получится. Давайте здесь просто пробежимся. Вот так вот, видите у нас все вот эти элементы были помещены в один столбец. То есть Excel сделал все неправильно. Это произошло все потому, что мы дали неправильный разделитель. Ну, на этом давайте закончим текущий урок .

На следующем уроке мы попробуем получить данные со страницы Web .

10.10.3. Получение данных со страниц Web На этом уроке мы с вами будем изучать получение данных со страниц Web. Microsoft Office 2003 очень тесно интегрирован с Web. Как мы создаем новый веб-запрос? Давайте попробуем это сделать. Для того, чтобы получить данные с Web страницы, делаем следующее. Выполняем команду Данные - Импорт внешних данных. Далее создаем веб-запрос. Сейчас у нас открывается вот такое вот окно, которое называется Создание веб-запроса. Вот сюда, в поле Адрес, мы должны ввести URL. Давайте введем какой-нибудь простенький URL. Ну, пусть это будет, например, mail.ru. И нажмем на кнопочку Пуск .

224 TeachPro Microsoft Excel 2003 Сейчас у нас происходит открытие этой самой Web страницы. Если вы обратили внимание, она у нас вот таким вот образом, тихо – тихо открывается. Вот, открывается наша Web страница .

Давайте еще немножечко подождем. И увидим вот что. После того, как страница откроется, видите, она уже почти вся, у нас открылась. У нас данная страница вся покроется вот такими вот желтенькими значками, которые будут обозначать на самом деле, таблицы. Сейчас откроется страница. В данном случае, у нас происходит загрузка определенных рисунков. Вот. Уже, значит, у нас страница загрузилась. Если вы обратили внимание, у нас имеются вот такие вот симпатичные стрелочки. Мы должны щелкнуть на тех стрелочках, таблицы которых мы хотим получить на нашем листе. Ну, предположим, мы хотим получить вот эту симпатичную страницу и вот эту. Вот эти две .

Давайте нажмем на кнопочку Импорт. В данном случае, у нас появляется окно Импорт данных, где нас спрашивают, куда следует поместить данные? Имеется следующее предложение в Имеющийся лист или в Новый. Давайте поместим в Новый лист. Нажмем на кнопочку OK. И сейчас у нас происходит фактически получение данных. Если мы подождем пару секунд, то данные у нас появятся на экране. Вот у нас имеются эти вот данные. Давайте это вот на самом деле уберем. Вот такая вот табличка. Вот, мы ее фактически получили на нашем экране с помощью веб-запроса. Что у нас здесь имеется?

У нас здесь имеется следующее. Мы можем собственно поменять запрос. Видите, у нас опять возникает вот это вот диалоговое окно. И кстати сейчас вот, после того, как оно полностью появится, мы увидим вот что. Давайте. Мы увидим, что те таблицы, которые мы с вами импортировали ранее, они, вообщем-то отмечены, они есть на экране. Вот с помощью этих вот галочек. Что еще интересно посмотреть нам в данном экране? На данном экране интересно изучить следующую кнопочку, которая называется Параметры. Что у нас имеется в этом диалоговом окне? Во-первых, имеются следующие опции по настройке веб-запросов. Во-первых, Формат. Это видите, такой большой список переключателей. Во-первых, можем вообще формата не иметь, как у нас в данном случае .

Можем использовать формат RTF. И можем полностью иметь формат HTML с ссылками, линками и со всем остальным. Далее, Импорт настроек для блоков PRE. В данном случае мы определяем параметры преобразования текста, в которые включен тек PRE. Мы можем установить следующие флажки: первое - Импорт блоков PRE в столбцы. Второй – считать последовательно разделители одним и третий - Одинаковые настройки для всего раздела. Вот. Все эти параметры лучше всего оставлять по умолчанию. Здесь другие параметры импорта. Отключить распознавание дат и отключить направление Web запроса. На самом деле лучше это включать .

Давайте нажмем на кнопочку OK. А еще лучше опять вызовем параметры, в данном случае, мы с вами вызвали не то, вызываем так. Мы хотим сейчас полностью формат HTML, чтобы посмотреть в чем разница. Ну кодировки, предположим, нас вообщем-то не интересуют. Давайте запрос выполним еще раз. Сейчас у нас происходит опять таки запрос. Смотрите внимательно на экран .

Видите, сейчас у нас пришли гиперссылки, то есть подчеркнуты синим цветом. И курсор преобразуется вот в такую руку. Вот так .

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

С его помощью мы сможем внешний диапазон, а именно, его свойства, каким-то образом менять .

Можно сделать следующее. Можно, во-первых, сохранить определение запроса. Это первое. Что еще можно установить? Как часто обновлять экран? То есть, фоново обновлять, обновлять каждые сколько-то минут, обновлять при открытии файлов, можно удалять внешние данные гиперссылки с листа сохранения. Видите здесь имеются такие вот опции .

Что у нас еще есть? у нас еще есть формат разметка данных. На самом деле здесь мы можем указать, задавать ширину столбца, Автоформатировать, например, данные. Но, если продолжить в таком случае обновление, с гиперссылки во внешнем диапазоне работать не будут, если у нас будет применен Автоформат. Здесь мы указываем, что именно делать, если, количество строк в Глава 10. Совместная работа Excel с офисными приложениями 225 диапазоне изменится, то есть добавлять новые строки, удалять существующие; добавлять новые строки и очищать пустые ячейки и так далее. И кстати можем заполнять формулами соседние столбцы. Ну, давайте будем заполнять. И нажмем на кнопочку OK. На самом деле, в данном случае, у нас ничего не изменилось. Что мы еще сейчас с вами будем делать? Мы будем делать вот что. Как мы с вами уже сказали, свойства внешнего диапазона данных можно менять. Его можно создавать и при создании запросов, так и после получения данных. Вот у нас имеется следующая Панель инструментов. Она называется, собственно, Внешние данные, если вы посмотрите, например, внимательно. Вот они. Что мы здесь можем сделать? Ну, свойства диапазона данных мы, собственно, уже нажимали, и убедились в том, что менять их можно .

Посмотрим, что здесь еще имеется. Мы можем обновить данные. Видите. Начинаем обновлять .

Сейчас у нас опять идет подсоединение к Интернету. И данные обновляются. Это следующее. Ну, со свойствами мы уже знакомы. Затем можем обновить все, не только данные, но и все. И имеется еще несколько закомментированных кнопок, которые в данный момент у нас сейчас не активны. А можем собственно даже, как мы сделали это раньше, менять запрос. Ну, давайте закроем это окно .

Менять мы с вами сейчас ничего не будем. Ну, на этом уроке давайте закончим текущий урок .

Вообщем-то мы поняли, как именно нам нужно обращаться с веб-запросами .

226 TeachPro Microsoft Excel 2003

–  –  –

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

11.1.2. Способы связывания ячеек В Excel предусмотрено несколько способов связывания ячеек. Начинаем изучение первого способа. Это с помощью команд меню. Причем эти команды располагаются вот здесь, в меню Правка. Как мы связываем ячейки? Предположим мы хотим вот в эту ячейку C11 поместить вот эти вот данные, то есть связать ячейку C11 ячейку B3. Как мы это делаем? Для начала мы выделяем ячейку B3 и выполняем команду Правка – Копировать. После того, как мы с вами эту команду выполнили, мы возвращаемся в ячейку C11, и выполняем команду Правка – Специальная вставка. Тут надо быть внимательным. Мы выполняем команду Специальная вставка .

После появлкния вот этого вот диалогового окна, которое вы сейчас видите, мы нажимаем на кнопочку Вставить связь - ни копируем содержимое этой ячейки, ни ее формулу, ни ее значение, ни форматы, ничего, а именно нажимаем на кнопку Вставить связь. Нажали на кнопку. Что у нас получилось? Если вы обратили внимание, в данной ячейке у нас значение 552, оно совпадает вот с этим значением, но содержимое ячейки как раз ссылка на ячейку B3. То есть, таким образом нам удалось связать эти две ячейки. Это первый способ. Второй способ, мы то же самое можем сделать с помощью перетаскивания .

Давайте сотрем содержимое этой ячейки. Выделим вот эту. Приведем курсор мыши на самый край рамки, так, чтобы курсор превратился в крестик. И с помощью правой клавиши мыши нажимаем на нее, на правую клавишу. В этом месте надо быть очень внимательным. Это именно правая клавиша .

Начинаем, при нажатой правой клавише, вот так вот перетаскивать. Потом мы здесь отпускаем правую клавишу. Появляется контекстное меню, в котором надо выполнить команду Связать .

Выполним команду Связать. Обратили внимание, здесь у нас опять 552. И в строке формул видна ссылка вот на эту ячейку. То есть, у нас две эти ячейки получились связанные. То же самое, создание связи может быть сделано между двумя разными рабочими книгами. Давайте попробуем это с вами сделать. Как вы помните, у нас здесь эта ячейка B3 Лист1, соответственно, книга называется Книга2 .

Давайте попробуем создать новую рабочую книгу. У нас она сейчас новая. И в ячейке приемники, то есть, в той ячейке, которую мы хотим связать с исходной, давайте мы с вами напишем здесь это слово. Вот приемник. Вот здесь в этой самой ячейке мы вводим следующие данные. Во-первых, в квадратных скобках вводим Книга2, ну, вводить, это понятно, надо русскими буквами. Книга2, точка, xls, это уже, соответственно, латинскими. После того, как столько мы ввели, мы идем вот сюда, и вводим здесь соответственно, Лист1, потому что у нас располагаться должна в Листе1. Лист1 - не забудем восклицательный знак, для того, чтобы сейчас уже назвать ячейку. А ячейка у нас, как вы помните, B3. B3. Вот так. И нажимаю клавишу Enter .

Видите у нас сейчас здесь, невзирая, что написана вот такая вот формула, у нас здесь значение

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

В данном случае, мы набрали это значение вручную. Если мы не хотим набирать значение вручную, что мы здесь делаем? Мы здесь делаем равняется. Переходим соответственно на нашу 228 TeachPro Microsoft Excel 2003 книгу, и вот таким вот образом выделяем нужную ячейку. Затем нажимаем клавишу Enter. И если вы обратили внимание, здесь у нас уже ввелось автоматически. Но у нас не номер ячейки, а в данном случае, даже именованный диапазон. То же самое можно сделать и с помощью меню Правка. Там Правка - Копировать, а здесь Правка - Специальная вставка, а затем Вставить связь .

Ну на этом уроке мы с вами прошли несколько способов связывания ячейки диапазонов. На следующем уроке мы начнем тему Изменение и удаление связей .

11.1.3. Изменения и удаление ячеек На этом уроке мы с вами будем изучать Изменение и удаление связей. В процессе работы наша рабочая книга может переместиться. Мы можем добавить новые столбцы и строки и так далее. То есть возникнет необходимость изменения и удаления связей соответственно. Как же нам поменять связь? Вот здесь у нас имеется ячейка, которая ссылается соответственно на Лист1 ячейку B3 .

Самый простой способ выделить одну ячейку и в строке форма изменить. Например, у нас B3 можем написать Е3. И нажать на клавишу Enter. После того, как мы здесь написали Е3, у нас в данном случае опять таки ссылается на Е3, и появились вот эти вот данные .

Ну, давайте вернем все, как говорится, на «круги своя». Вот так вот. Вернем старое значение .

То есть, меняем в строке Форма. Но если нам придется изменить большое количество ссылок, то вручную менять не представляется рациональным. Что нам нужно сделать для этого? Нам нужно сделать вот что. В команде Правка, нам нужно выполнить команду Связь. На самом деле эту команду мы сейчас выполнить не можем, потому что у нас она не активна. Почему? Потому что здесь, на этом листе, у нас нет ссылок на другую рабочую книгу .

Давайте перейдем на нашу Книгу4, которая содержала, как вы помните, в себе ссылки на другую рабочую книгу. Видите, вот они. И отсюда выполним команду Правка – Связи. Вот у нас возникла вот это вот диалоговое окно, которое называется Изменение связей. Что это у нас за диалоговое окно? Ну, первое, что мы можем сделать, первое, что напрашивается, это нажать на кнопочку Изменить. В данном случае, нам предлагается изменить источник Книга2.xls. Для чего это сделано? Например, предположим, ваша Книга2 поменяла свое местоположение. Сейчас она находится не в папке, к примеру, Мои документы, а где-нибудь в другом месте, чисто гипотетически. Если она поменяла свое местоположение, вы, таким образом, с помощью диалогового окна Изменить источник ее находите .

Что еще у нас здесь имеется? Ну, обновить, понятно из названия. Сейчас сообщается, что у нас источник открыт. Так оно и есть, Книга2 у нас открыта. Если нажмем на кнопку Открыть, нам сообщат, что Книга2.xls уже открыта. Мы отказывается это делать. Давайте еще раз выполним команду Правка – Связи. И посмотрим, что еще у нас здесь есть. Ну, состояние, вообщем-то тоже понятно из названия. Обновление. Обновление в данном случае у нас автоматическое. Если мы нажмем на кнопку Запрос на обновление связей, здесь мы можем выбрать, что нам делать при открытии этой книги Excel. Во-первых, Excel нам может задавать вопрос, нужно ли обновлять связи с другими книгами? И здесь мы указываем нужно или нет .

В данном случае, ждать ответа пользователя - Не задавать вопросы, не обновлять связи, Не задавать вопрос и обновлять связи. Ну, пусть останется первый вариант, который у нас, соответственно и был. При нажатии на кнопку Разорвать связь, у нас, соответственно, внешние ссылки будут преобразованы, в принимаемые ими на данный момент значения. Эту операцию, кстати, невозможно отменить. И спрашивается, разорвать связь или нет? Давайте выберем Разорвать связь. И посмотрим, что у нас, при этом получилось .

А получилось у нас вот что. Видите, в этих ячейках у нас уже не ссылки на книгу два, соответственно, а просто простые значения, полученные из Книги2. Ну, вообщем-то, поняли, как изменять и удалять связи. На этом давайте закончим текущий урок. На следующем уроке мы с вами изучим Связанные изображения ячеек .

Глава 11. Анализ данных 229 11 .

1.4. Изображения ячеек На этом уроке мы с вами будем изучать Связанные изображения ячейки. Изображение ячейки это картинка, на которой изображена ячейка. Причем изображена она именно так, как выглядит на рабочем столе, то есть видно не только содержимое ячейки, но ее рамка, фон, вообщем оформление. Если включены линии сетки, то они тоже будут видны на изображении. Изображение ячеек может быть связано с исходными ячейками. И соответственно при изменении исходных ячеек, изображение тоже будет обновляться. Такое изображение называется Связанным. А изображения, на самом деле, представляют собой обычные растровые картинки .

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

Здесь у нас появляются вот такие вот не активные команды Вставить рисунок и Вставить связь с рисунком. Мы сейчас попробуем с ними поработать. Команда у нас не активна, потому как буфер обмена у нас в данный момент пустой .

Выделим вот этот фрагмент. И выполним команду Правка – Копировать. Для того, чтобы буфер обмена был заполнен Правка – Копировать .

Сейчас переносим курсор, например, сюда .

Нажимаем клавишу Shift, это обязательно и выполняем команду Правка – Вставить рисунок .

Если вы обратили внимание, вот эти вот ячейки у нас скопировались вот сюда, но уже в виде рисунка. При этом, если вы будете особенно внимательны, вы увидите, что у нас автоматически возникла Панель инструментов, которая называется Настройка изображений. В данном случае, вот этот рисунок он ни какой связи с исходными ячейками не имеет .

Давайте в этом убедимся. Он меняет значение, например на 600. Нажмем на клавишу Enter и увидим, что никаких изменений у нас не произошло. Давайте сейчас сделаем вот что. Выделим опять таки этот фрагмент. Выполним команду Правка – Копировать. Нажмем на клавишу Shift, но на этот раз выполним команду Правка - Вставить связь с рисунком. Здесь у нас опять таки появился рисунок, вот он. Но в данном случае, это рисунок, который реагирует на изменение. Смотрите внимательно. Нажимаем и здесь соответственно произошло изменение, потому что в данном случае, изображение мы создали с помощью другой команды Создать связь с рисунком. Теперь для того, вот если вы обратили вынимание, чтобы изменить ссылки, предположим, нам нужно будет их изменить, нам достаточно поменять здесь в поле Формул соответственно и значения .

В данном случае у нас ссылка на ячейки A3 – C4, то есть, начиная вот от сюда и кончая вот это ячейкой. Если мы посчитаем нужным, что нам нужно что-то поменять, давайте, кстати, попробуем это сделать, поменять данные от Германии, у нас это первое значение будет Е3. Мы сейчас с вами это сделаем, а второе значение у нас будет соответственно F4, а не C .

Вставим F, нажимаем на клавишу Enter. Видите, значения у нас соответственно изменились. В данном случае, у нас не эти значения, а вот эти. Если же у нас изображение ячеек сделано из другой книги, то менять в данном случае, если у нас есть ссылка на другую книгу, то выполняем команду правка - –вязи. В данном случае связь у нас не активна, там же ссылки на других рабочих книг мы не имеем. Ну, на этом давайте закончим текущий урок. На следующем уроке мы с вами начнем обширнейшую тему, которая называется консолидация данных .

230 TeachPro Microsoft Excel 2003

–  –  –

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

Давайте уберем данные. И выполним Консолидацию еще раз. Вот здесь у нас должна была быть проставлена галочка Создавать связи с исходными данными. После того, как галочка проставлена, вот у нас здесь имеется некоторая Средняя площадь. Вернем данные обратно, такие, какие у нас были и видите, у нас соответственно Средняя площадь поменялась. Изменим еще раз, например, поставим 600. Перейдем на Лист3. Видите, здесь значение меняется соответственно .

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

Вызовем диалоговое окно Консолидация еще раз и будем изучать этот список более подробно. Ну, первое - это Сумма. Что такое Сумма?

Ну давайте выберем ее и нажмем на кнопку OK. Ну, в данном случае, давайте попробуем вот таким вот образом открыть. Вот у нас здесь, вот эта Сумма, собственно имеется. Давайте внимательно посмотрим. Хотя Сумма на самом деле у нас, в данном случае, совершенно неправильная. Давайте вернем это все на свои места. Вернемся. Посмотрим, что у нас произошло с нашими ссылками. Ссылки у нас совершенно правильны. Связи у нас созданы. Функцию хотим Сумма. Именно Сумма и ничто иначе. Вот сейчас у нас здесь имеется Сумма 600 и 357. Все правильно. Сумму мы получили. Дальше. Введем количество. Сервис - далее консолидация. Здесь у нас уже будет соответственно не Сумма, а Количество .

Ну, вот для этих вот диапазонов. Давайте посмотрим, правильно ли у нас все выделено. И сделаем это еще раз. Вот наши данные. Вот Средняя площадь, в данном случае, не правильное название, но Количество у нас определенно равно двум. Потому как считаем мы по двум листам .

Дальше, Данные консолидации. Ну, давайте не будем уже все приводить в пример, а просто посмотрим, что здесь имеется. Со Средним мы уже тоже знакомы. Максимум, Минимум понятно, Произведение, Количество чисел, Смещенное отклонение, Несмещенное отклонение, Смещенная дисперсия, Несмещенная дисперсия, в основном нужны специалистам по статистике .

Ну, что такое, например, Смещенное отклонение? Смещенное отклонение - это оценка стандартного отклонения генеральной совокупности. При этом предполагается, что в исходных значениях находится только выборка из генеральной совокупности. Ну, вообщем-то, вряд ли обычным людям это когда-нибудь понадобится. Ну давайте закроем диалоговое окно нажатием на кнопку Закрыть. И на этом уроке мы закончили изучение консолидации по физическому расположению. На следующем уроке мы начнем изучение консолидации по заголовкам строк и столбцов .

11.2.2. Консолидация по заголовкам строк и столбцов Сейчас мы с вами начнем изучение Консолидации По заголовкам строк и столбцов .

Необходимо заметить, что использование Консолидации По заголовкам более удобно, чем По физическому расположению. Что происходит при Консолидации По заголовкам? Вот давайте рассмотрим таблицу, которая называется Франция. Мы будем ориентироваться по тому, что используемая для консолидации ячейка будет названа по имени строки, либо по имени столбца .

На самом деле, нам выгодно, чтобы она была бы названа в данном случае по вот этому названию, по площади. Предположим, нас интересует именно площадь .

Давайте что мы сейчас сделаем. Мы перейдем на Лист номер три, выделим какую-то ячейку и выполним следующую команду. Данные - давайте откроем полосу Консолидация. С прошлого урока у нас здесь остались какие-то определенные значения. Мы сейчас их с вами удалим. В данном случае, надо быть внимательным, что мы будем использовать в качестве имен. В данном случае, в качестве имен мы будем использовать значение левого столбца. Сейчас давайте введем диапазон – в данном случае, вот .

Вводим первый диапазон, и нажимаем на кнопочку Добавить, после чего переходим на Лист2 и видим, что здесь у нас этот диапазон также выделен - нажимаем кнопку Добавить и нажимаем на 232 TeachPro Microsoft Excel 2003 кнопку OK. Перед этим можем установить вот эту прекрасную галочку Создавать связи с исходными данными - OK. Если вы обратили внимание, у нас произошло вот что: у нас здесь уже указана площадь. Эта фраза, это слово было взято вот отсюда, потому что мы там указали, что значения в столбцах слева и соответственно, имеется какой-то при этом результат. Очень хорошо .

То есть, значения левого столбца были восприняты как имя ячейки, то есть, фактически, мы, таким образом, консолидировали наши данные. Ну, в этом случае, надо быть внимательным, потому что слова “площадь” во всех диапазонах для консолидации должны быть написаны абсолютно одинаково. Если вы ошибетесь хоть где-нибудь, будет написано не «Д» с «мягким знаком», а просто «Д», то произойдет сбой, произойдет ошибка .

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

Правка – копировать - перешли на Лист3. Что мы затем делаем? Затем делаем вот что .

Выполняем следующее. Предположим здесь у нас уже имелись какие-то данные. Ну, как, или, у нас вот имеются ни здесь. Сейчас мы выполним команду Правка - Специальная вставка. И вот здесь в группе операции можем выбрать необходимую операцию. Если нам нужна была для консолидации Сумма, то здесь мы можем выбрать Переключатель сложить, Нажать OK, и получить уже соответственно больший результат. Обратите внимание, здесь у нас уже имеется плюс 552, то есть многократно это проделывая, мы можем получить какой-то суммарный результат или, еще раз Правка – Специальная вставка. И здесь вот опять таки можем выбрать любую операцию. Здесь имеется флажок, который называется Пропускать пустые ячейки. Что это значит? Сейчас у нас пустых ячеек нет. Поэтому неважен установлен этот флажок или нет. На самом деле - это для того, чтобы знать Пропускать искомые пустые ячейки. Больший интерес вызывает флажок, который называется Транспланировать, потому что слово вообщем-то не очень знакомое. Что это значит?

В результате транспланирования диапазона строки и столбцы меняются местами. Первая строка становится первым столбцом и наоборот, то есть таблица перестраивается. Нам сейчас ничего перестраивать не надо. Поэтому этот флажок мы, соответственно не устанавливаем, мы его отключаем и опять нажмем на кнопку OK. В данном случае у нас там была нажата Вычесть. Здесь, соответственно было заведено Вычитание. В строке формул мы собственно это видим. Если необходимо, чтобы содержимое обоих исходных диапазонов сохранялось, а не так выполнялось, как здесь выполнялось поэтапно, то следует применять стандартный способ консолидации. На следующем уроке мы с вами научимся связывать консолидированные данные, изучим обновление связанных данных и изображений .

11.2.3. Связывание консолидированных данных с исходными Сейчас мы с вами поговорим о Связывании консолидированных данных с исходными. Данные могут быть консолидированы Со связью с исходными, и Без связи с исходными. Предположим, мы опять вычисляем среднее значение, но Без связи с исходными. Выполняем команду Данные – Консолидация, но перед этим нам надо перейти на Лист3. Данные - Консолидация - выполнили исходную команду. В этом случае, мы должны отключать вот эту вот галочку - Создавать связь с исходными данными, если мы хотим обойтись без искомых связей. Вот здесь у нас имеется Глава 11. Анализ данных 233 следующее значение, но если мы здесь что-нибудь поменяем на 600, перейдем на Лист3 - видите верхнее значение изменилось, а нижнее нет, потому, как Связи с исходными данными у нас нет, мы ее убрали .

Давайте посмотрим сюда. В данном случае, вот здесь, в том месте, где у нас имеется связь с исходными данными, у нас появились вот эти знакомые нам плюсики. Это значит, что перед нами структурированный документ. Вот он, видите. Этот структурированный документ. После того, как мы щелкаем левой клавишей мыши по плюсикам, то увидим, фактически, вот здесь ссылки на наши листки. Это у нас Книга2, а здесь, соответственно, ссылки на соответствующие листки, на Лист1 и Лист2. Вот .

Там, где мы скопировали Без связи с исходными значениями, вот здесь у нас вот этой вот самой структуры нету - нет плюсиков, и нет вот таких вот взаимосвязей соответственно. Они абсолютно отсутствуют. Их, собственно, не может быть потому, как значение при изменениях не меняется. Мы с вами это попробовали. Необходимо заметить, что обновление связанных ячеек происходит при каждом пересчете рабочего листа. И счет обычно происходит автоматически .

Меняем значение исходной ячейки. Давайте вернулись сюда. Значение поменяли. Вот так .

Вернулись обратно. Видим, что пересчет у нас собственно, произошел .

Кроме того, можно Автоматический пересчет листа отключить, например, очень много формул

- пересчет занимает большое время. При каждом изменении весь пересчет происходит, и больше машина начинает работать медленнее. Для того, чтобы отключить, выполняем следующую команду Сервис – Параметры. Здесь имеется следующая вкладка, которая называется Вычисление .

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

Это может быть автоматически кроме таблиц, автоматически полностью, как мы только что с вами сказали. Вот. Если у нас установлено Вручную, то каждый раз, когда мы будем нажимать клавишу F9, у нас будет происходить пересчет. Ну, давайте закроем, пожалуй, это окно. Здесь у нас сейчас установлено Вручную. Надо об этом помнить. И сделаем вот что. Что у нас сейчас имеется? Меню Правка - посмотрим на нашу команду Связь. Команда Связь, у нас к сожалению, сейчас пассивна, потому что связи, на самом деле, с никакими внешними объектами у нас нет .

Давайте свяжем все таки данную книгу с чем-нибудь, ну давайте здесь напишем равняется Книга4 .

Как вы помните, книга пишется в квадратных скобочках .

Так, это у нас здесь должно быть Книга4 точка xls, затем, уже здесь пишем Лист1, восклицательный знак. И предположим ячейка B1 - на самом деле что расположено в этой самой ячейке мы не помним, как выяснилось, расположен ноль. Но вот сейчас у нас имеется ссылка на какую-то внешнюю рабочую книгу. И как следствие, команда связи у нас сейчас активна. Вот мы с вами ее вызвали. Соответственно появилось диалоговое окно, которое называется Изменение связи. В этом диалоговом окне мы можем менять имеющиеся параметры. И, собственно, можем задать, как именно обновлять наши связи. В данном случае Автоматически. А можно собственно, делать это с помощью определенных вопросов, например, вот таким вот образом, или вот таким вот образом. Как мы считаем в данный момент, собственно, нужным. Следует сказать вот что, что выбор между консолидацией или обычным связыванием, на самом деле вопрос вкуса .

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

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

234 TeachPro Microsoft Excel 2003

–  –  –

хотим получить данные, у нас вот. Это двойка, потому что мы сейчас с вами хотим получить Модель. Давайте введем сюда два. А в интервальный просмотр мы вводим следующее: так как у нас столбец по возрастанию не отсортирован, сюда просто вводим, например, слово «ложь». Ну, давайте мы с вами это сделаем. Вот так. Ввели, соответственно, слово «ложь». И нажмем на кнопочку OK. Сейчас мы здесь с вами получили модель телефона NOKIA, который имеется вот в этой таблице. Соответственно мы сюда ввели NOKIA и здесь получили, соответственно, Модель .

Давайте то же самое сделаем с Ценой. Как вы уже помните – Вставка – Функция – ВПР - это вертикальный поиск. Искомое значение у нас вот. Таблица у нас соответственно. Так, мы здесь немножко ошиблись. Искомое значение у нас вот. Затем надо перевести курсор на таблицу, прежде чем ее отметить. Делаем вот так и номер столбца у нас соответственно будет три. Ну, так как у нас вообщем-то ничего не отсортировано, опять вводим слово «ложь». И нажимаем на кнопочку OK. Вот здесь мы получили так же Цену .

Теперь попробуем вместо NOKIA ввести что-нибудь другое. Давайте введем слово SIEMENS .

Вот так. После того, как мы ввели слово SIEMENS, логично ожидать, что вот эти значения у нас изменятся. В данном случае они не изменились. Они не изменились, потому что здесь в параметрах и в вычислениях у нас проставлен флажок вручную, а не автоматически. Это у нас осталось с прошлого урока. Если мы введем флажок сюда, то здесь мы, соответственно, получим новые данные .

Давайте в этом убедимся, и введем модель телефона PONTECH и нажимаем на клавишу Enter .

Соответственно, у нас поменялась и Модель и Цена. Ну, как вы видите, изменения соответственно происходят. Ну, давайте попробуем еще поработать с SAMSUNG. Нажимаем опять таки клавишу Enter - получаем новую модель, и новую цену, соответственно. Вообщем, как, примерно, работает функция ВПР, мы уже поняли. На следующем уроке мы попробуем воспользоваться функцией поиска позиций и индексов, а только затем, перейдем к анализу данных .

11.3.2. Поиск ПОЗ() и ИНДЕКС() Сейчас мы с вами поговорим об использовании функций Поиск позиции и индекс .

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

Давайте попробуем это сделать. Выполним следующую команду – Вставка - Функция, ну, и, соответственно - Индекс. Сейчас мы с вами его найдем. Вот он. Ну, нажимаем на кнопочку OK .

Нам сообщается, что данная функция имеет разные списки аргументов и предлагается выбрать один из них. Нас интересует массив, номер строки и номер столбца. Нажимаем на OK. И попробуем уже работать. Что сейчас мы с вами сделаем? В массиве мы задаем Диапазон ячеек. Вот наш диапазон ячеек. Мы с вами его задали. Что нас интересует здесь? Нас интересует соответственно номер строки. Нас интересует вторая строка, соответственно, пусть будет первого столбца. Что мы получим? Здесь надо иметь ввиду, что номер строки и номер столбца задаются относительно того массива, который в данный момент выделен .

Так у нас номер столбца единица. То вот номер столбца, номер строки двойка. Но выделен вот этот диапазон. Поэтому попадаем прямо на, собственно Nokia. Нажмем на кнопочку OK. И увидим слово Nokia в нашей ячейке, соответственно. Таким образом, мы его нашли по 236 TeachPro Microsoft Excel 2003 пересечению строки и столбца. И задание нужного массива. Если мы хотим, например, получить Siemens, то здесь нам соответственно нужно дать не вторую строку, а первую. Давайте справим это прямо в строке формул, и просмотрим, что у нас получится. Да, именно, так. У нас получилось Siemens. Ну, вообщем-то понятно .

Сейчас мы посмотрим, что из себя представляет функция Поиск позиции. Как мы уже с вами говорили ранее, она возвращает относительно положения элемента массива. Сейчас мы попробуем использовать эту функцию. Выполняем команду – Вставка – Функция и соответственно - Поиск позиции. Сейчас мы с вами найдем - вот эта функция. Предположим, давайте нажмем на кнопочку OK. Предположим, в качестве искомого значения мы зададим Pantech, мы ищем именно его. Вот так. Какой массив мы просматриваем? Да, все тот же. Вот наш, собственно, массив .

Теперь перешли к самому важному - Тип сопоставления. Что означает Тип сопоставления?

Здесь у нас внизу написано. Оно определяет возвращаемое значение. Ну, давайте сюда установим, например, ноль, просто на просто. Вот так вот. И нажмем на кнопочку OK. Посмотрим, что у нас при этом получится. А, собственно, у нас ничего с вами и не получилось. Давайте попробуем определить почему. Сейчас нам сообщается, что значение не доступно. В данном случае, у нас произошла ошибочная ситуация только потому, что мы не правильно выделили диапазон. Давайте мы это исправим. Выделим. Вот этот .

Нам нужно было выделить вот только вот эту часть, а не, собственно весь диапазон. Нажали на клавишу Enter и получили, в данном случае, цифру четыре. Что за цифра четыре? Если мы внимательно посмотрим на этот столбец, а выделили мы в свое время вот этот столбец, то у нас Pantech четвертое значение. Вот эту, собственно, четверку, мы с вами и получили, то есть элемент Pantech в этом значении у нас четвертый по счету. Теперь мы попробуем с помощью функций Поиск позиции и Индекс получить те же результаты, что и при использовании функций вертикального поиска .

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

Давайте попробуем это сделать. Это делаем уже в Модели - Вставка. Функция. Как мы с вами только что сказали, Поиск позиции. Поиск позиции. Искомое значение мы берем соответственно вот это, а просматриваемый массив у нас соответственно вот. Ну, тип сопоставления, как и раньше мы с вами создадим вновь. Вот. У нас получилось три. То есть у нас имеется для значения Sаmsung третья строка, соответственно. Что сейчас мы с вами сделаем? Фактически, вот это значение мы используем в функции Индекс. Давайте мы с вами сейчас вызовем функцию Индекс .

Это у нас Вставка – Функция - соответственно сейчас мы с вами найдем Индекс - OK. Нас интересует опять таки первое – разновидность. Массив у нас, собственно, вот .

Номер строки у нас, мы с вами, уже нашли, с помощью функции Поиск позиции, а номер столбца, в котором в данном случае мы ищем модель, то номер столбца у нас будет, соответственно, два. Нажимаем на кнопочку OK. И получаем здесь модель телефона Sаmsung. То же самое мы с вами можем сделать и для Цены. Опять таки делаем это с помощью функции Индекс. Сейчас мы с вами это сделаем. Сейчас мы с вами найдем Индекс. OK. Массив у нас вот .

Номер строки мы получили ранее с помощью этой функции, а номер столбца, в данном случае, будет три, потому что сейчас мы с вами получаем именно Цену. Нажимаем OK и получаем соответственно уже и Цену. Все как в предыдущем случае - мы получили фактически те же значения. Таким образом, с помощью функций Индекс и Поиск позиции, мы получили тот же результат, что и с функциями Поиска. На этом давайте закончим текущий урок. На следующем уроке мы с вами поговорим об использовании функции ветвления Если .

Глава 11. Анализ данных 237 11 .

3.3. Если () На этом уроке мы с вами пройдем функцию Ветвления, которая называется Если. Давайте перейдем на Лист3.и просмотрим список наших функций. Вот здесь у нас имеется полный алфавитный перечень. Давайте пройдемся по этому списку и найдем нашу функцию Если .

Посмотрим, что она делает. Здесь сказано, что функция Если проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет. Давайте предположим, нам нужно вот что. Предположим, нам требуется, по имеющейся фамилии и полу, то есть мы вводим фамилию или пол. Нам нужно составить некоторое приветствие. Давайте здесь напишем слово «Приветствие» .

Если пол у нас будет мужской, то у нас должна появится следующая информация:

«Здравствуйте Господин такой-то». Если пол у нас женский, то, соответственно должна появится информация: «Здравствуйте Госпожа такая-то». Делать это мы с вами будем с помощью функции Если. Давайте вставим эту функцию. Вставляем функцию Если. Вот это наша функция. Сначала мы вводим логическое выражение. В нашем случае, мы будем вводить вот в эту ячейку, то есть, если эта ячейка равна, предположим, букве М, то есть это мужской пол, то у нас должно на экране появится следующее:

«Здравствуйте, Господин» .

В этом случае, у нас должна появится фраза «Здравствуйте, Госпожа». Очень хорошо. Вот такая информация. Нажимаем на кнопочку OK. У нас на экране появилось следующее «Здравствуйте, Госпожа». Почему же это произошло? Пол у нас в данном случае не заполнен .

Предположим мы поменяем его значение, вставим букву М. Видите, у нас поменялось значение результирующей ячейки. У нас появилось «Здравствуйте Господин», но если мы сотрем букву М, у нас останется «Здравствуйте Госпожа». Это произошло вот по какой причине. Дело в том, что у нас проверяется только буква М. Если не буква М, а любое другое значение, то у нас появляется фраза «Здравствуйте Госпожа» .

Если ячейка пуста, считается, что в ней находится нулевое значение. Если мы сюда введем любое другое значение, то, соответственно, здесь у нас опять таки «Здравствуйте Госпожа»

останется. То есть в данном смысле мы обеспечили некоторую интерактивность, но вообщем-то она достаточно не полная. Сейчас мы с вами попробуем сделать следующее. Мы попробуем сделать так, чтобы приветствие появилось на экране только тогда, когда данный единый корректор. То есть ячейка Фамилии обязательно должна быть заполнена. Далее, что еще у нас должно быть? У нас должно быть в следующей графе либо М, либо Ж, то есть хотим, чтобы вот эта ячейка не была бы равна пустому значению, и соответственно, здесь должна была бы быть либо М, либо Ж .

Попробуем сделать вот что: в данном случае функции Если, мы начинаем несколько переделывать. Давайте уберем эту часть, и мы будем это переделывать с помощью двух логических функций «И», «ИЛИ». Мы этими функциями пользовались, когда работали с базами данных. Во-первых, мы хотим, чтобы приветствие показывалось только тогда, когда фамилия не пустое значение, а пол или мужской, или женский. В данном случае, это если. Вот сейчас мы вводим функцию «И». Затем в скобках для этой функции мы делаем проверку. Во-первых, данная ячейка не должна быть пуста .

То есть у нас C1 не должно быть равно, давайте мы это введем, не должно быть равно пустому значению, это, во-первых это первая часть функции «И». И, во-вторых, у нас в значении Пол, должно быть или мужчина, или женщина, то есть сюда мы вводим функцию «ИЛИ»,а в данной 238 TeachPro Microsoft Excel 2003 функции «ИЛИ» мы сравниваем. Так давайте переведем курсор сюда. Мы сравниваем эту ячейку на равенство с буквой М, в данном случае. Вот так вот. Ставим точку с запятой. И, соответственно, ее же сравниваем с буквой Ж .

Вот так. Что у нас получилось? Только в случае, если это все заполнено, у нас появится строчка «Здравствуйте». Кстати давайте вот эту часть мы просто уберем. В противном случае, у нас будет на экране просто пустая строка. Нажмем на клавишу Enter, и посмотрим, что у нас получится. Все правильно, у нас Фамилия не заполнена. Давайте заполним каким-нибудь значением. Пусть это будет Иванов. Нажмем на клавишу Enter. У нас получилось «Здравствуйте»

или, собственно сюда мы можем уже просто проставить Иванов. И получится «Здравствуйте Иванов». Но в данном случае мы сделали вот такое вот сложное выражение. С помощью функции «Если», «И», «ИЛИ», например, если мы сюда введем, например, «РРР», то слова «Здравствуйте»

у нас на экране не будет. Потому что у нас ячейка C2 не равна ни М, ни Ж, соответственно. Вот так. Давайте на этом закончим текущий урок. Вообщем-то мы поняли что из себя представляет функция Если. На следующем уроке мы с вами начнем проведение анализа с помощью таблиц подстановок .

–  –  –

затем одиннадцати, ну давайте испорчу маркер. А теперь определим, но понятно, что токарь не может работать больше двадцати четырех часов. Ну, например, пусть это будет сутки. Хотя на самом деле мы считаем исходя из недельной оплаты. Ну, предположим, у нас крайнее число пусть будет двадцать четыре. Сейчас мы с вами начнем работать с Таблицей подстановки .

Что мы делаем в первую очередь? В первую очередь мы выделяем вот этот следующий диапазон. Затем выполняем команду Данные – Таблица подстановки. В данном случае, появляется следующее диалоговое окно, где нам предлагается выбрать, какими именно образом подставлять значения в данный диапазон по столбцам или по строкам. В нашем случае, мы должны подставлять именно по строкам, потому что данные у нас находятся в столбце, то есть вот таким вот образом мы будем на них как бы смотреть. Что мы делаем дальше? Так как в нашем случае, в случае зарплаты имеется ввиду, что расчет времени у нас показывает вот эта ячейка, именно сюда мы должны были бы вручную вводить вот эти вот значения, то соответственно, нам здесь нужно сделать вот что, подставлять значения по строкам вот сюда, в эту ячейку .

Каждое значение у нас будет подставляться вместо вот этой ячейки, и, соответственно, будет рассчитываться зарплата. Вот так. Мы нажали на кнопочку OK, и у нас здесь имеются следующие значения, исходя из соответственно занятости. В данном случае, у нас имеется ячейка Е4. Здесь, соответственно, Е4 у нас указано. Имеется ввиду, что здесь у нас Таблица подстановки, обратите внимание, что вот здесь у нас это есть. Кстати тот же самый ответ мы могли бы получить таким образом: мы бы здесь могли ввести равняется, тариф умноженное на количество часов, и поделенное, конечно, на сорок, потому что тариф у нас исчисляется из количества сорока часов в рабочую неделю. Кстати, если мы протянем маркеры - марки заполнения - у нас в данном случае получилось не совсем правильно, потому что для этой ячейки, вот для ячейки Е3, мы должны были бы здесь поставить не изменение по строкам, то есть вот этот самый анкор .

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

11.3.5. Таблица подстановок с двумя переменными Сейчас мы с вами попробуем разобраться с Таблицей подстановки двух перемен. На прошлом уроке мы с вами это сделали для одной. У нас была вот зарплата. У нас были часы, которые данный токарь шестого разряда работал, в неделю и был вот такой вот тариф. Сейчас попробуем сделать вот что. Мы попробуем сделать то же самое, но в зависимости от двух перемен .

Предположим тариф у нас меняется .

И тариф у нас меняется в зависимости от разряда. Предположим у нас здесь имеется шестой разряд. Давайте вот эту часть мы просто отсюда уберем. Точно так же, как, собственно и вот эту .

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

Соответственно у нас будет меняться и тариф. Как у нас меняется в данном случае, тариф? Тариф у нас в данном случае меняется следующим образом. Предположим, токарь шестого разряда у нас получает искомые шесть тысяч рублей, токарь седьмого разряда у нас уже будет получать шесть пятьсот. Давайте с помощью маркера автозаполнения протянем это до одиннадцатого разряда, изменим формат данных, потому что видите, у нас образуется разница. Часть у нас с рублем, другая часть у нас без рубля. Предположим количество десятичных знаков нам не нужно, в принципе. Вот такие красивые данные. А здесь мы с вами вычислим нашу зарплату. Как вы помните, наша зарплата имела следующий вид. Мы тариф умножали на количество часов, а затем делили на сорок, потому что тариф исходя из сорока часовой рабочей недели. Вот такие вот данные. Что мы сейчас с вами делаем? Мы сейчас с вами выделяем весь этот диапазон. Вот так вот. И выполняем команду данные - Таблицы подстановки. Сейчас мы с вами должны заполнить два этих поля, ни одно, как раньше, а два, потому что у нас и значение по столбцам подставляются, и соответственно значение по строкам. Как мы это делаем?

240 TeachPro Microsoft Excel 2003

–  –  –

11.3.6. Особенности работы с таблицами подстановок Сейчас мы с вами рассмотрим особенности работы с Таблицами подстановок. Что это значит?

На прошлом уроке мы с вами создали Таблицу подстановок с двумя переменными, но потом ее заменили, для того, чтобы продемонстрировать, как именно работает автомаркер. Давайте мы уберем эту часть, и еще раз попробуем вставить нашу Таблицу подстановки. Как мы с вами это делали. Мы вызвали Данные - выбирали команду Таблица подстановки. Значения по столбцам мы с вами подставляли вот сюда, в тариф, значение по строкам мы с вами подставляли соответственно сюда .

И вот у нас наша Таблица подстановки автоматически заполнилась. Если внимательно посмотрите вот в эту часть таблица, в строку формул, здесь у нас написано таблица Е3; Е4 .

Фактически это функция работы с массивом и как вы видите, нигде она соответственно не изменяется. Редактировать отдельно вот таким вот образом Excel нам не позволяет, потому что говорит, что изменять часть Таблицы подстановки совершенно нельзя. Нажмем на клавишу Esc, чтобы вернуть все обратно. В зависимости от того, установлена ли галочка вот здесь, Сервис давайте вызовем Параметры - затем - Вычисление .

Глава 11. Анализ данных 241 В зависимости от того, какая галочка установлена вот здесь, у нас в Таблицах подстановки либо будут пересчитываться автоматически, либо вручную .

Ну давайте, например, установим на вручную. И что мы с вами увидим. Если мы здесь изменим какиенибудь данные, например, здесь сделаем не десять, а к примеру целых сорок. Вы увидите, что Таблица подстановки у нас при этом совершенно не пересчитывалась. Давайте вернем эти данные обратно. И попробуем, убрав данную галочку, посмотреть, произойдет ли у нас пересчет Таблицы подстановок в данном случае. Но мы сейчас вставим сорок. Если вы обратили внимание, соответственно вот эта строка у нас полностью пересчиталась, если же галочка не стоит, то пересчет таблицы будет осуществлен только после того, как мы нажмем Shift F9. Тогда она будет пересчитываться вручную .

Если мы хотим перестроить Таблицу подстановок или ее расширить, то нам следует, соответственно создать ее заново. Давайте мы в этом убедимся. Предположим у нас есть здесь еще двадцать пять часов в неделю, и мы хотим, чтобы эта часть у нас была бы соответственно тоже заполнена. Что нам для этого нужно? Нам для этого нужно опять выделить этот фрагмент. Опять таки выполнить команду Данные - Таблица подстановки - значение по столбцам подставляем сюда, по строкам соответственно вот сюда, OK .

Вот сейчас Таблица подстановок у нас соответственно расширилась. Вот здесь имеются вот такие вот новые значения, соответственно. Ну, на этом давайте закончим текущий урок. Как вы увидели, старая таблица у нас сейчас замещена новой. На следующем уроке мы с вами изучим использование так называемого Мастера подстановок .

11.3.7. Мастер подстановок Сейчас мы с вами научимся пользоваться так называемым Мастером подстановок. Мастер подстановок, иначе говоря, Lookup Wizard- это встроенное средство для создания формулы, которая основана на функции Индекса и Поиска позиции. В английской версии эта формула тоже INDEX, просто латинскими буквами, и MATCH. Для запуска Мастера подстановок, необходимо выполнить следующие команды. Из меню Сервис открываем подменю Мастер, а затем выполняем команду Поиск. У нас здесь откроется Мастер подстановок - шаг один из четырех. Давайте сейчас пока прикроем данную возможность .

В случае, если у вас нету этого подменю в меню Сервис, вам следует сделать вот что выполнить команду Надстройки - и проставить галочку вот здесь в Мастере подстановок. Ну, давайте сейчас закроем это окно. У нас имеется список наших сотовых телефонов, имеющихся в ассортименте. Это название фирмы изготовителя, модель и соответственно цена. Предположим, мы хотим найти формулу, получить формулу поиска, например какой-либо модели по соответственно изготовителю. Как мы это с вами делаем? У нас имеется Изготовитель. У нас имеется Модель. И эту самую Модель мы с вами хотим получить. Изготовитель у нас предположим, ну, искомая Nokia, в нашем случае. Что мы сейчас с вами делаем? Вызовем Сервис

- выполняем команду Мастер, а затем Поиск .

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

Сейчас нам следует выбрать столбец, который содержит искомые значения. Столбец, который содержит у нас искомые значения - это Модель. Собственно она в данном случае выбрана. В 242 TeachPro Microsoft Excel 2003 следующем случае, мы можем выбрать, например Цену. Ну, в данном случае, нам нужна Модель .

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

Это предположим у нас строка. Нажимаем на кнопочку Далее. Сейчас нам предлагается Копировать формулу в отдельную ячейку, если мы хотим получить модель в таком чистом виде .

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

Сейчас мы увидим на экране изображена та формула, которую мы с вами хотели получить написана она с помощью английских функций, не русских. Это видимо издержки локализации. С помощью функции INDEX, и соответственно функции Поиск позиции, в латинской версии, это функция MATCH. Вот мы ее получили на экране. Если мы захотим сделать то же самое, например, для цены, давайте попробуем это сделать здесь, мы получим нечто похожее. Ну, давайте выделим данную ячейку – Сервис - Мастер – Поиск - это наш нужный нам диапазон. Соответственно здесь мы выбираем уже Цену, а не Модель. Предположим, нас продолжает интересовать телефон Nokia .

Получим мы именно вот это значение .

Нам здесь это показано, но у нас будет скопирована формула. Это у нас копия формулы. Вот так вот. Сейчас мы зададим данную ячейку - Готово. В данном случае, мы с вами получили уже значение, уже вот такое вот оно посчитанное, но на самом деле здесь точно также вот, в данном случае, с помощью русских функций, на русском языке, написана вот наша функция фактически .

Вот так вот. Ну, на этом давайте закончим текущий урок. Вообщем-то примерно понятно, что мы с вами делали .

–  –  –

Atlant-e, в городе Atlanta у нас расположено несколько компаний. Соответственно идет дублирование и по месяцам. Сводная же таблица является многомерной. Фактически, если ее сравнить, мы ее можем сравнить со стопкой листков. Виден только верхний лист, но в любой момент можно вытащить необходимый. Для работы в Excel со Сводными таблицами, существует специальная Панель инструментов. Давайте попробуем ее отобразить на экране .

Панель инструментов так и называется - Сводная таблица. Вот эта самая панель инструментов .

Как мы уже только что сказали, большие таблицы с повторяющимися данными, а перед нами именно такая таблица, она не удобна для просмотра информации и ее анализа. Для более наглядного представления данных, используются Сводные таблицы. Сейчас мы с вами попробуем создать соответствующую Сводную таблицу, но текущая таблица у нас уже есть, так называемая Исходная. Сейчас мы хотим подвести определенные итоги, например, эти итоги могут быть по месяцам, по соответственно компаниям, и предположим по результирующим продажам. Что мы делаем? Мы выполняем следующую команду – Данные - Сводная таблица .

На экране появляется первое диалоговое окно Мастера сводных таблиц и диаграмм .

Фактически это у нас первый шаг. Здесь нас спрашивают, во-первых, какой тип сводного отчета мы хотим создать - таблицу или сводную диаграмму. В данном случае мы хотим создать таблицу .

Затем идет вопрос - создать таблицу на основе каких данных, которые хранятся в списках Microsoft Office Excel, во внешнем источнике данных, или в нескольких диапазонах консолидации? В нашем случае мы имеем дело со списком Microsoft Office Excel. Сейчас нам необходимо указать диапазон. Как вы видите, по умолчанию был уже выбран весь диапазон - вся наша таблица. Нам остается либо с этим согласиться, и вообщем мы так и сделаем, потому что диапазон был выбран правильно, или нажав кнопку Обзор, соответственно выбрать другой. Ну, давайте нажмем кнопку Далее. Сейчас здесь нам предлагается поместить таблицу либо в новый лист, либо в существующий. Мы с вами поместим в новый лист, тем более весь существующий практически заняла наша исходная позиция. Ну, давайте нажмем на кнопочку Готово .

Сейчас нам нужно перетащить элементы в Сводную таблицу. На самом деле, как именно располагать данные Сводной таблицы решает пользователь. Мы же можем собственно просто посоветовать. Предположим, нам нужен отчет по городам. Поэтому мы берем элемент City – город, и перетаскиваем ее в самый верх. Вот мы перетащили отдельно, будем смотреть по городам. Давайте уберем Сводные таблицы и продолжим. Далее, предположим, в верхней части нас интересуют месяца, вот здесь. Очень хорошо. Затем, компании мы с вами расположим вот здесь. Вот таким вот образом и в результате, здесь, в середине, элемент данных у нас будут результирующие продаж. Сейчас, перед вами на экране, фактически полный отчет по полю Resulting Sale. Что мы здесь можем делать?

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

Сейчас у нас изображены все города .

Предположим, нас интересует только Atlanta. и, соответственно все компании, которые расположены в городе Atlanta. Имелись соответственные в них продажи. Вот видите, у нас получился вот такой общий итог. Кроме того, мы можем выбрать соответствующий месяц, предположим, нас интересует только отчет по январю. Видите, у нас в январе месяце отчет имеется только вот по городу Atlanta, соответственно, по компании Direct Mail. Давайте откроем какой-нибудь другой город, предположим это Columbus. В городе Columbus у нас имеется отчет по трем компаниям соответственно. Имеется ввиду январь месяц. Вообщем, что такое сводные таблицы вы примерно поняли. Давайте на этом закончим текущий урок. На следующем уроке мы продолжим изучение сводных таблиц .

244 TeachPro Microsoft Excel 2003

–  –  –

York-у, давайте перейдем на Atlant-у, но все равно убеждаемся, что никаких результатов нам это не дало. Почему? Потому что для того, чтобы оно дало какой-нибудь результат, нам нужно сделать вот что, нам нужно открыть вот этот списочек здесь и выполнить команду Обновить данные. Только в этом случае у нас будет какой-то определенный, к примеру, результат. И то, если мы выберем нужную нам компанию. Давайте вернемся сюда. Посмотрим, где мы поменяли. Мы поменяли в Coupon-е. Выберем соответственно только эту компанию. Вот так, чтоб нам было проще. Ну, отчет у нас пусть будет собственно весь. Хотя общего итога у нас в данном случае, собственно и нет. Ну, давайте здесь Atlanta собственно имеется .

Вернемся сюда, и посмотрим, что у нас здесь произошло. От нас требуется собственно данные за апрель месяц. Хотя мы можем перейти сюда на январь, и сделать изменения вот здесь. Давайте здесь вот сделаем такое глобальное изменение, большое. Это у нас Direct Mail. Вернемся сюда Выберем здесь Direct Mail, вот так, видите изменений у нас собственно у нас не произошло .

Выполняем команду Сводная таблица - Обновить данные .

Видите, данные у нас обновились. Только после этого - выполнения этой команды у нас обновляются соответственно данные, как вы в этом убедились. Вот так вот. Как мы сохраняем файлы со сводными таблицами? Дело в том, что при создании Сводных таблиц Excel копирует исходные данные в ту рабочую книгу, где Сводная таблица построена. Из-за этого, размер рабочей книги может быть большой. Для того, чтобы это не происходило, с помощью панели инструментов, вот этой самой, выполняется следующая команда, которая называется Параметры таблицы .

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

11.4.3. Изменение макета и сводной таблицы На прошлом уроке мы с вами построили наши Сводные таблицы. Сейчас мы поймем, как изменять Макет сводной таблицы и менять ее форматирование. Сводная таблица отличается от обычных таблиц тем, что представляет собой не просто диапазон ячеек, а единый объект, который связан с другими диапазонами, поэтому, для форматирования Сводных таблиц в Excel существует несколько методов. Первый метод это автоматическое форматирование. Надо сказать, что первый метод самый легкий .

Эта возможность намного удобнее и эффективнее, чем форматирование вручную .

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

Выполняем следующую команду Формат - Автоформат. У нас возникает диалоговое окно Автоформат, где имеется вот такой вот огромный список - список готовых форматов, которые мы можем применить к нашей таблице. Ну, давайте выберем какой-нибудь - например, Таблица десять и нажмем на кнопку OK. После того, как мы нажали на кнопку OK, мы видим, что сейчас у нас применен новый формат .

246 TeachPro Microsoft Excel 2003

–  –  –

Давайте мы это назовем Книга3, чтобы ни что ни с чем не перепуталось. Вот так, у нас имеется вот такая вот таблица. Сейчас на ее основе мы с вами построим новую Сводную таблицу. Давайте это просто на просто удалим. Она в данном случае нам не нужна. Выполняем команду Данные как вы помните - Сводная таблица. Строим именно Сводную таблицу. Диапазон ячеек у нас уже выделен. Делать это будем на новом листе .

Сейчас будьте внимательны, что мы сейчас будем делать? А мы с вами будем делать вот что .

Здесь у нас уже имеется так же и год. Мы сейчас так же, как и ранее, города перетягиваем сюда, компании сюда, а в полях страниц - мы с вами помещаем не только месяц, но и год, соответственно. Вот так вот. Год и месяц. Далее уже вот сюда мы перетаскиваем элементы наших данных. Вот так. У нас получилась вот такая Сводная таблица. Давайте мы вот это вот закроем это вот наше окно. Вот у нас получилось что-то вроде вот этого. На самом деле созерцать такую таблицу не очень удобно. Тем более мы здесь может быть, хотим смотреть например данные только по июлю за все года, или данные по июлю за 2005 год. На самом деле, если вы обратили внимание, детализация у нас действительно увеличивается .

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

показать все страницы из Month или из Year. Предположим, нас интересуют года. Если вы обратили внимание, вот у нас имеются данные за 2004 год. Вот. У нас получился отдельный рабочий лист, если вы обратили внимание. Здесь у нас 2005, дополнительно появился лист 2004 год, и, соответственно, вот с этими вот данными. Ну, вообщем –то все понятно. На этом закончим текущий урок. Мы научились работать с полями страниц. Научились форматировать данные Сводной таблицы, работать с Автоформатом, и изменять формат ячеек Сводной таблицы. На следующем уроке мы начнем перестраивать нашу сводную таблицу соответственно .

11.4.4. Перестройка сводной таблицы Сейчас мы с вами научимся перестраивать нашу Сводную таблицу. Предположим мы хотим, чтобы данные по компаниям у нас были бы вот здесь, а сверху соответственно были данные по городам. Что мы с вами для этого должны сделать? Сейчас мы с вами попробуем ее перестроить .

Берем поле Город и начинаем его вот так вот перетаскивать. Если вы обратили внимание, у нас соответственно хвостик нашей мыши приобрел несколько другой вид, и, синим подчеркнуто поле, куда мы можем в данный момент поставить наши города .

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

Перестраивать достаточно легко. Иногда может возникнуть ситуация, когда Excel неадекватно обрабатывает наши данные. Что это значит? Давайте перестроим нашу таблицу. Города поднимем вот сюда, а в это поле приведем данные по месяцам. Если вы видите, у нас отсортированы наши месяца по алфавиту, на самом деле здесь сначала идет апрель, и только потом идет январь. Затем идет июль и октябрь. В данном случае, правильно, вот эти места у нас определенно не логично .

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

В таких случаях, когда у нас происходит нечто вроде этого, следует поменять данные на рабочем листе. Делать не апрель, январь и июнь, в данном случае проблема в том, что здесь у нас текст. А можем, например, задать, к примеру, в месяц числами, например апрель у на четвертый 248 TeachPro Microsoft Excel 2003 месяц, да? Январь первый, июль у нас по-моему шестой, нет на самом деле седьмой, и октябрь у нас десятый. Вот видите, таким вот образом зададим наши данные, то здесь соответственно, они будут уже перестроены совершенно правильным образом .

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

11.5. Анализ данных с использованием сводных таблиц

11.5.1. Создание сводной таблицы на основе данных из нескольких диапазонов консолидации Сейчас мы с вами начинаем интереснейшую тему, которая называется Анализ данных. Мы будем рассматривать дополнительные возможности работы со Сводными таблицами. Как мы знаем Сводная таблица, вот одна из них перед вами, эффективно анализирует информацию .

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

Вот у нас имеется такая таблица. Давайте мы сейчас удалим отсюда год. Имеется вот такая вот таблица. Предположим эта таблица - это у нас данные за 2004 год. Тут у нас уже имеется подобного типа таблица. Ладно, предположим, здесь еще проставим какой-нибудь вот такой значок, чтобы имена не пересекались. Кстати, давайте эту таблицу мы с вами просто удалим .

Собственно, как и эту. Сейчас работаем только с данными. Предположим это данные за 2004 год .

Предположим, у нас имеются еще подобного типа данные, но за 2005-й. Давайте просто скопируем данный лист. Вот так. И назовем его 2005 год. Сейчас нам надо подвести как бы суммарный итог. Что мы с вами для этого делаем?

У нас имеются две таблицы, совершенно с одинаковым форматом, с одинаковыми заголовками столбцов. Кстати это необходимо. Сейчас выполняем следующую команду - мы вызываем Мастер сводных таблиц – Данные - Сводная таблица. Сейчас перед нами знакомый нам Мастер сводных таблиц и диаграмм. Так как, сейчас у нас таблица расположена на нескольких рабочих листах, то мы выбираем вот этот третий пункт: нескольких диапазонных консолидаций. Нажимаем на кнопочку Далее. Сейчас мы находимся на втором шаге. Сейчас нам предлагается - Создать одно поле страницы или Создать поля страниц .

Если мы выберем вот этот вот второй переключатель, что у нас получится? У нас будет создано до четырех полей страниц. Нас устраивает одно поле страницы. Вот одно поле страницы у нас имеется. Очень хорошо. Нажимаем на кнопку Далее. Здесь нам предлагается добавить диапазоны. Во-первых, сначала мы выделяем нашу первую таблицу - это у нас 2005 год – Добавить - переключаемся на второй диапазон, и, соответственно, его также выделяем. И опять нажимаем на кнопочку Добавить. Вот .

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

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

Глава 11. Анализ данных 249 Если мы хотим получить развернутые результаты, то следует сначала объединить данные вручную и уж затем получать результат .

Как это делается? Нам нужно всего лишь скопировать эти данные, например вот сюда. Соответственно проставить лот и уж по ней, с нуля построить новую Сводную таблицу. Ну в данном случае мы это делать не будем, потому что как это делается, мы это уже знаем, уже несколько уроков изучаем Сводные таблицы .

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

11.5.2. Группировка данных сводной таблицы Сейчас мы с вами разберем следующий вопрос: Группировка элементов по их именам .

Необходимо заметить что в процессе работы с насущными задачами, нередко приходится иметь дело с большим объемом данных. Чаще всего эти данные можно организовать в виде дерева. Они могут иметь иерархическую структуру, то есть ту структуру, которая в виде дерева именно и организована. Для того, чтобы использовать иерархические свойства данных в Excel необходимо создать дерево уровней .

Дерево уровней создается с помощью операции Группировка элементов. У нас в данный момент уже имеется некоторая Сводная таблица, которая построена на основе определенных данных. Ну, как вы знаете, в этой таблице у нас представлены не все данные, а только некоторые .

Предположим мы хотим сделать вот что, мы хотим по какому-то принципу объединить вот эти компании. Предполагаем, что часть компаний у нас находится на востоке, а другая часть компаний у нас находится на западе. Что мы с вами делаем? Предположим, мы выбираем те компании, которые предполагаем, что находятся на востоке или на западе. В данном случае не важно .

Выполняем вызов контекстного меню и далее команду Группа и Структура. Здесь мы выполняем следующую компанию, которая называется Сгруппировать. Вот мы ее сгруппировали. Если вы обратили внимание, у нас здесь уже имеется следующая группа. И только после этого идут имена компаний. То же самое давайте сделаем для этих двух. Опять таки выполняем команду Группы и структура и затем команду Сгруппировать .

Вот у нас сейчас имеются вот такие вот две группы - Группа один и Группа два. Кстати при желании мы можем их переименовать также. Но сейчас мы с вами этого делать не будем. Хотя на самом деле с другой стороны можно собственно и поменять .

На этом следует вызывать команду Параметры поля. Вот здесь имеется вот это название Компания два. На самом деле это вот название вот этого поля. Вот так вот. Если мы выполним команду опять таки Параметры поля, сейчас мы можем здесь его поменять, предположим, давайте введем сюда Группы. Вот так вот. И вот видите здесь у нас уже имеется ввиду Группы. Хотя на самом деле, название не очень правильное, потому что не отражает сути деления. Что мы сейчас будем делать? Мы сейчас делаем вот что. У нас новый вид фактически Сводной таблицы. Мы можем с вами увидеть фактически только одну группу. Если мы ее выделим, здесь соответственно у нас будут уже только две компании, то есть фактически у нас произошла Группировка, если мы опять таки откроем это поле и захотим увидеть только Группу два, у нас есть для этого всяческая возможность - мы видим только Группу два. Ну, и, наконец, если мы хотим увидеть все, мы 250 TeachPro Microsoft Excel 2003 ставим вот эту галочку и сейчас мы видим все, что у нас было, просто немного другое изображение, потому что имеется вот дополнительное поле Группы .

Сейчас мы с вами сделаем вот что. Вот у нас имеются какие-нибудь итоговые данные .

Предположим, нас интересует, на основе чего было это вычислено. Если мы сделаем вот такой двойной щелчок мышью, у нас открывается вот наш лист, и мы видим тот фрагмент, на основе которого были вычислены вот эти данные, данные по Общему итогу. То же самое можно сделать, если, например, щелкнуть вот по этому пункту. Видите, здесь уже соответственно то, что относится к компании Local Ads .

Давайте попробуем сделать то же самое, например, для компании Coupon и убедимся, что вот таким образом Excel создает новый лист, и показывает все те данные, которые были необходимы для вычисления вот этого вот поля фактически компания Coupon. Ну, давайте щелкнем еще куданибудь, и посмотрим, как говорится что-то. Вот так. Видите, все за месяц апрель. У нас месяц апрель и Общий итог. Соответственно, нам нужны все имеющиеся фирмы, но только за месяц апрель. Вот таким вот образом мы можем смотреть и видеть все наши данные. Таким образом, мы научились обращаться со Сводной таблицей, поняли что такое Группировка элементов, научились Группировать элементы, и более детально отображать нашу Сводную таблицу. На следующем уроке мы научимся Группировать числа по диапазонам, научимся Группировать элементы по временным диапазонам, и, вообще, пройдем понятие Группировки элементов более подробно .

–  –  –

Сейчас, предположим, мы хотим получить, на самом деле, Месяц и, соответственно, День .

Здесь у нас будет Месяц. Сейчас мы, кстати очень достойно сделаем это опять таки функцией .

Функции у нас будут соответственно следующие, Дата и Время. И мы из соседней Даты хотим получить День. Вот так вот. Вот у нас возникло соответственно Число - Месяц. Хотя на самом деле мы здесь получили Месяц, а не День .

Давайте поправим нашу функцию еще раз. Мы здесь в данном случае с вами ошиблись .

Вызываем Функцию. Вот так вот. Подпускаем ее еще раз. Нас интересует в данном случае Месяц от соседнего числа – ОК, но формат данных у нас будет соответственно число. Все правильно. То же самое делаем здесь для Дня. Месяц. Здесь у нас будет соответственно, День. Вот так вот. И соответственно вызывается Функция, вызывается День. Параметр функции - наша Дата и изменим соответственно Формат ячеек, потому что должен быть Общий формат или на крайний случай Числовой .

И здесь соответственно у нас температура. Вот. На самом деле, у нас все сделано вот что - так как это генератор случайных чисел, то каждый раз обновляется в зависимости от того, что мы делаем в той или иной ячейке. Сейчас если мы скопируем эти данные, а потом совершим Специальную вставку, вот она, и вставим только значение, то здесь у нас уже будут, если вы обратили внимание на вот это вот поле, у нас уже будут соответственно именно Числовые значения, а ни как не Вычисление функции случайное число. Вот так, у нас имеется вот такая вот таблица. Теперь по ней мы будем строить Сводную таблицу .

Выполняем команду Данные - Сводная таблица - Мастер сводных таблиц запустился .

Предполагаем, что все что делает Мастер это правильно. Нажимаем кнопку Готово. Сейчас поле строки. В нашем поле строки, вот мы вставим сюда соответственно Месяц. Поле столбцов - мы вставим День. Далее, предположим мы хотим показать среднюю температуру по декадам каждого месяца. Температура у нас вот. Давайте перетащим ее вот сюда. Очень хорошо. Что мы сейчас делаем? Предположим мы хотим показать среднюю температуру по декадам, как только что сказали. Для этого нужна операция Группировки чисел по диапазонам. Помещаем указатель мыши в одну из ячеек в поле Дата. Сейчас мы хотим сделать вот что. Мы хотим показать среднюю температуру по декадам каждого месяца .

Для этого нужна операция Группировки чисел по диапазонам. Помещаем сюда соответственно наш курсор мыши и выполняем команду Группы и структуры - соответственно Группировать. Вот так вот. У нас появляется вот такое окно, которое называется Группирование. Что мы сейчас с вами должны сделать? Мы должны ввести начальное и конечное значение диапазонов. В нашем случае это с первого по тридцать первое. Имеется шаг десять, то есть по декадам. Правильно с первого по тридцать первое с шагом десять, то есть у нас получается декада соответственно .

Нажимаем OK. Вот у нас получилась вот такая вот Группировка .

В данном случае Сводная таблица содержит суммарные температуры за каждую декаду. В понятии суммарная температура на самом деле нет никакого смысла. Нам нужна была средняя температура. Что мы для этого делаем? Мы переходим на наши Данные, к примеру, и выполняем следующую команду - Параметры поля. Вот здесь имеются операции. И сводное поле у нас называется Сумма по полю Температура. В нашем случае нужна не Сумма, а Среднее. Давайте выберем Среднее. И нажмем на кнопочку OK. Соответственно у нас здесь параметры не изменились. В данном случае у нас имеется не Сумма по полю температура, а Среднее. Давайте убедимся в этом и увидим, что у нас имеется Среднее по полю температура. Microsoft Excel помогает группировать элементы, которые содержат значение Даты и Времени. Для того, чтобы нормально строить Сводную таблицу по этим данным, мы можем удалить какие-то определенные столбцы, и строить их по двум другим .

Ну, давайте с вами это сделаем. В данном случае, мы можем удалить День и Месяц. Давайте мы с вами это удалим. И построим вот такую вот Сводную таблицу. Выполняем команду Данные Сводная таблица. Далее диапазон у нас выделен. Мы хотим создать независимый отчет, о чем заранее предупреждаем мастер. И сейчас мы c вами собственно все сделаем. Это сюда, у нас Дата .

252 TeachPro Microsoft Excel 2003 Имеется, к примеру, температура. Вот такие вот итоги. Сейчас мы сгруппируем наши данные Группы и структуры соответственно Группировать. Вот. Здесь у нас имеются следующие параметры. Мы можем группировать наши даты с шагом - секунды, минуты, месяцы и даже кварталы .

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

Ну, на этом уроке мы с вами завершаем изучение понятия Группировки. Мы научились Группировать элементы. На следующем уроке мы более подробно изучим промежуточные итоги Сводной таблицы. И, кроме того, научимся сортировать данные Сводной таблицы .

–  –  –

видите здесь уже отсортировалось по значению. Ну, с сортировкой вроде бы разобрались. Сейчас продолжим. Давайте этот список полей в нашей Сводной таблице закроем .

Вообщем-то панель инструментов давайте закроем также, и будем продолжать изучение Сводной таблицы, а в данном случае поговорим об итогах. На самом деле, в Excel можно показать промежуточные итоги соответственно. Наша Сводная таблица. Мы это делаем следующим образом. Вызываем вот это вот меню, и вызываем Параметры поля .

Здесь имеются следующие опции. Мы можем показывать другие итоги соответственно .

Выставили вот этот вот флажок, и выполнили, например, пусть нас интересует промежуточный итог Средний. Если вы обратили внимание, у нас вот здесь появились вот такие вот соответственно промежуточные итоги. Кроме того, мы это можем сделать не только со средним, но так же с суммой вот так вот. И вот опять таки у нас имеется сейчас и среднее, и сумма соответственно. Кроме того, мы можем скрывать соответственные итоги. Мы можем скрывать и эти промежуточные итоги, и можем скрывать соответственно общий итог. Как мы это делаем? Ну, как скрывать промежуточный итог, вообщем-то понятно .

Достаточно нам здесь выбрать нет. Выбрали Нет - никаких промежуточных итогов у нас нет .

Если мы хотим скрыть вот эти общие итоги, мы делаем следующее - мы выполняем команду Параметры таблицы. После того, как мы выполнили команду Параметры таблицы, у нас возникает на экране вот это диалоговое окно – Параметры сводной таблицы. И нам здесь достаточно отключить вот эти галочки. Мы их отключили, нажали на кнопочку OK, и соответственно, у нас сейчас никаких итогов на экране нет .

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

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

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

Например, предположим, нас интересует функция не Сумма, а Количество. Выбрали Количество и нажали на кнопку OK, соответственно, у нас наша таблица изменилась. Что у нас получилось? У нас получилось, что записей за апрель - это фирма Coupon, у нас три штуки; за январь соответственно - девять; за июль – четыре, и так далее. Вот такие у нас получились итоги .

254 TeachPro Microsoft Excel 2003 Что еще мы можем сделать? Кстати для функции Общий итог, тоже используется функция Количество, в данном случае .

Давайте продолжим. Попробуем найти Максимум. Соответственно, опять выполняем команду Параметры поля, и делаем не Количество, а Максимум по полю Profit. Вот. У нас получился сейчас Максимум по полю Profit. Для этой фирмы у нас максимум был зафиксирован в апреле, и соответственно Общий итог, как итоговая сумма, это у нас получилось сорок восемь тысяч, потому что в данной строке, это максимальное значение. То же самое, у нас соответственно вот здесь - было вот это значение, Общий итог, как Максимальное - это. Если мы просмотрим внимательно эту строчку, то увидим, что здесь максимум - это совершенно правильно. Ну, вообщем понятно. Предположим, при работе со Сводной таблицей необходимо использовать некоторые дополнительные вычисления. Например, мы хотим узнать долю, или, еще что-нибудь .

Давайте опять нажмем на правую клавишу мыши. Выполним команду Параметры поля. И нажмем вот на эту кнопочку, которая называется Дополнительно. У нас наше диалоговое окно вот таким вот образом, соответственно раскрывается .

Ну, давайте выберем здесь какое-нибудь дополнительное вычисление. Предположим это Доля от суммы по строке, в данном случае и нажмем на кнопочку OK. Итак, здесь лучше, чтобы у нас была бы сумма на поле Profit, то есть вернем все на круги своя. Что у нас получилось?

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

Ну давайте посмотрим, что у нас еще имеется? Какие дополнительные возможности? Давайте опять таки выполним команду Параметры поля. Вот у нас наше диалоговое окно и посмотрим, что у нас есть. Ну, вот это первый пункт - Отличие. Это у нас итоговое значение, вычисляется как разность между результатом и значением, которое указано в списке поля элемент. Давайте выберем вот это Отличие и здесь соответственно нам надо будет выбрать поле, собственно, какое поле, и какой элемент, ну, предположим, пусть у нас останется City и Atlanta .

Нажмем на кнопку OK, и посмотрим, что у нас при этом получается. на самом деле ничего удивительного у нас не получилось. Excel нам выдал ошибку, а Excel нам выдал ошибку по очень простой причине, потому что отличие в данном случае у нас собственно как бы и нет, то есть, подсчитать, в данном случае не возможно. Ну, давайте пойдем дальше. Здесь у нас имеется доля – доля - вы это знаете. Ну что такое, мы с этим уже как-то познакомились. Нарастающий итог в поле, это значение накапливается в ячейках и приближается к общей сумме. Ну, а с этими двумя вы вообщем-то уже вполне знакомы. Точно также и Доля от общей суммы и Индекс .

Что такое у нас Индекс? Индекс это то значение, которое вычисляется следующим образом:

результат умножается на Общий итог, и соответственно делится на итог в строке, умноженный на итог по столбцу. Ну, давайте попробуем вычислить что-нибудь в этом роде и посмотрим, что у нас при этом получится. Вот. У нас получилось, что Общий итог - это единица, а здесь соответственно получились вот такие вот значения. Ну, давайте на этом закончим текущий урок. Фактически мы прошлись по итоговым функциям, научились выбирать Итоговые функции и научились пользоваться Дополнительными вычислениями. На следующем уроке мы научимся пользоваться Сводными диаграммами .

–  –  –

случае, у нас на Лист4. Вот наша таблица и построим соответственно, Сводную. Открывается Мастер сводных таблиц и диаграмм - шаг первый из трех. В данном случае, у нас список или база данных Microsoft Office Excel, вот она, так как в этом случае у нас здесь имеется, а тут надо поставить вот этот переключатель Сводная диаграмма. Обратите еще раз внимание - Сводная диаграмма у нас будет построена вместе со Сводной таблицей. Нажимаем на кнопку Далее. Здесь Excel сам выбирает диапазон, в данном случае у нас вот такая неразрывная таблица. Нажимаем на кнопку Далее, хотим Построить на новом листе и нажимаем на кнопочку Готово. Вот у нас появляется диалоговое окно, похожее, соответственно на ту, которое мы видели раньше - Поля страниц - ну, поля страниц у нас всегда по умолчанию, было City – Далее - Элементы данных. Ну, элементы данных, у нас был Profit. Продолжаем .

Смотрим, что у нас еще есть. Поля категорий

- Пусть у нас это будет, соответственно, Campaign. Ну, и посмотрим, что у нас еще здесь имеется. Поле рядов. Поле рядов, у нас будет соответственно - Месяц. Вот так вот. У нас получилась вот такая диаграмма. Диаграмма у нас соответственно Сводная. Давайте закроем это диалоговое окно, и попробуем поработать. Здесь у нас сумма по полю Profit - что правильно. Это у нас соответственно Месяцы. Мы можем, например, получить на экране несколько штук .

Соответственно давайте посмотрим и январь. Ну, в данном случае у нас вот такой вот лидер по продажам есть, по сравнению, с которым, все остальное у нас, просто не видно .

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

Здесь у нас соответственно сумма. Здесь у нас отчет по всем городам. Мы можем, например, сделать только по Columbus, или только по New-York-у, вот так вот меняется наша Сводная диаграмма. Ну, и соответственно у нас здесь имеются фирмы, которые должны быть выведены на экран. Предположим у нас хотим иметь на экране две фирмы, или, соответственно вот эти две .

Или, например, три, но не все. Если хотим все, выставляем соответствующий выключатель. Вот у нас получается такая вот Сводная диаграмма .

Ну а здесь, соответственно, сумма по полю Profit. Данные мы не меняем, то есть, фактически построили нашу первую Сводную диаграмму. Вот так вот. Это у нас, мы ее построили соответственно с нуля. Что же делать, если мы хотим построить Сводную диаграмму, на основе уже имеющейся Сводной таблицы. Вот наша Сводная таблица. Давайте только соответственно Параметры поля уберем Дополнительное вычисление, и вернем нашу таблицу к какому-то все таки относительно правильному исходному состоянию. Вот. Это наша Сводная таблица. И мы сейчас построим, соответственно щелкнув вот на этой кнопочке Диаграмму - Сводную диаграмму, на основе этой нашей Сводной таблицы. Вот так, здесь у нас соответственно имеются группы, потому что там у нас имелась группа. Значения по этим фирмам у нас соответственно, видите очень маленькие по сравнению с Direct Mail. Давайте мы Direct Mail отсюда просто уберем, чтобы увидеть все остальное, в каком-то вот нормальном виде .

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

Если мы хотим изменить внешний вид нашей диаграммы, мы делаем вот что: указатель мыши помещаем в область диаграммы, и нажимаем правую клавишу соответственно, здесь уже, знакомым нам способом, мы выбираем тип диаграммы. Вот здесь имеется несколько типов, мы можем выбрать любую. Ну, предположим, пусть нас вот что-то в этом роде интересует. Нажимаем 256 TeachPro Microsoft Excel 2003 на кнопку OK и получили вот такие вот странные данные для соответственно рядя July. Мы можем таким образом менять для любого ряда наши данные .

Давайте, тип диаграммы опять таки сделаем график. Потом сюда. И нам осталось только вот это фиолетовое. Вот получился вот такой вот интересный график. Ну, давайте на этом закончим текущий урок. Мы вообще-то поняли, как именно работать со Cводными диаграммами. Как строить Cводную диаграмму на основе Cводной таблицы, или на основе исходной таблицы .

Глава 12. Поиск и исправление ошибок .

Использование сценариев 257 Глава 12. Поиск и исправление ошибок .

Использование сценариев

12.1. Режим просмотра рабочего листа для нахождения ошибок Сейчас мы с вами начинаем новую главу, которая называется Поиск, Исправление ошибок и Использование сценариев. Как вы знаете, грамотный пользователь должен уметь правильно планировать работу и проверять информацию в таблицах, особенно если данных много. Кроме того, это не маловажно, если речь идет о большой фирме или компании, где используется, соответственно, большое количество документов Excel. На одном из прошлых уроков мы с вами уже разбирали те ошибки, которые выдает Excel в зависимости от того или иного действия .

Давайте примерно это повторим .

Предположим в ячейке А1 у нас находится цифра 9, в ячейке B1 нас находится цифра 0 и в ячейке C1 мы хотим поделить одно на другое. Сейчас мы с вами это делаем. Девять, соответственно делим на содержимое ячейки B1 - у нас здесь Excel сообщил об ошибке деление на ноль, потому что ячейка B1 у нас содержала ноль. Все виды ошибок Microsoft Excel мы с вами подробно рассматривали в свое время. Для того, чтобы нормально последить какая ячейка дала ошибку, существует несколько режимов просмотра рабочего листа. То есть, мы можем изменить режим вывода информации на экран, например, показать на экране формулы, вместо просмотра результата. Как это, собственно, делается? В меню Сервис выполняем команду Параметры. У нас появляется вот это вот диалоговое окно, параметры которого вам знакомы. Если вот здесь, в этом диалоговом окне мы проставим вот эту галочку Формулы, и нажмем на кнопку OK, то мы, соответственно увидим место результата Формулы. Вот ее написание: А1 деленное на B1. Данный режим удобно использовать следующим образом. Вот сейчас мы с вами видим саму формулу .

Давайте сохраним данную рабочую книгу. Пусть это будет, например, «ОШ» от слова «ошибка». Можем сделать вот что - мы можем выполнить сейчас команду File - Открыть. Здесь найти нашу рабочую книгу. Она у нас называлась «ОШ», как вы помните, вот она, а здесь выбрать соответственно - Открыть как копию. После чего выполняем команду Расположить все, например, пусть это будет рядом. В данном случае, у нас открыто вот это окно. Оно нам собственно совершенно не нужно. Вот. У нас открыты рядом следующие окна, и мы можем сделать вот что .

Здесь у нас предположим режим просмотра формул, а здесь у нас будет режим просмотра соответственно результатов. Давайте мы с вами отключим эту галочку. Вот. Сейчас у нас здесь ошибочный результат, потому что мы наблюдаем результаты, а здесь, собственно режим просмотра формул - видим уже с формулой .

Так работать значительно удобнее. Следующее - если мы хотим быстро переместиться к определенной ячейки для исправления ошибки, или выйти из группы ячеек, то мы можем сделать вот что - мы можем выполнить команду, которая называется Правка. Давайте полностью откроем данное окно, и соответственно Перейти. Вот. Мы с вами выделили вот эту команду, и соответственно получили данное диалоговое окно. Сейчас мы нажимаем на кнопочку Выделить. У нас появляется вот это диалоговое окно, которое называется Выделение группы ячеек. Если нам 258 TeachPro Microsoft Excel 2003 нужно, соответственно куда-нибудь перейти, мы здесь можем выделить соответствующий фрагмент. Давайте подробно просмотрим. Ну, первый пункт - это Выделение ячеек с примечаниями. Следующий пункт - Выделение ячеек, содержащих константы - Формулы, ну понятно из названия. Выделение ячеек, например, содержащих числа, текст, логические операции, и ошибки, две ячейки, содержащие значения ошибки .

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

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

То же самое, насчет столбцов .

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

Давайте нажмем на кнопку OK. Соответственно она у нас продолжает быть выделена, потому что именно она у нас содержит ошибку. Если мы собственно в это не верим, мы можем это сделать еще раз. Выполняем команду соответственно, Правка – переход - Выделить, и опять таки формула, содержащая ошибки. Ну, нажмем на кнопочку OK. Видите, эта ячейка опять таки выделяется, то есть именно данная ячейка у нас содержит ошибку. Этот способ можно использовать для обнаружения такой распространенной ошибки, как например ввод числового значения вместо формулы и так далее. Ну давайте, на этом закончим текущий урок. На следующем уроке мы с вами разберем дополнительные средства поиска и исправления ошибок .

–  –  –

называются Влияющими. В данном случае у нас Влияющие ячейки - А1 и В1 соответственно .

Данная ячейка, которая у нас сейчас активна, называется Зависимой, потому что она зависит от данных в этих ячейках - соответственно A1 и B1. Для того, чтобы удобнее было просматривать зависимости, можно отключить вывод сетки. Вывод сетки отключается, как вы помните, следующим образом: в меню Сервис выполняем команду Параметры и соответственно сбрасываем вот этот вот флажок. Если мы нажмем на кнопку OK, мы сетку уже в данном случае не увидим .

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

Давайте полностью откроем данное меню и здесь необходимо выполнить команду, которая называется Зависимости. Вот эта команда, которая называется Зависимости формул. Открывается соответствующее подменю. Вот оно. Что мы здесь видим? Мы можем выбрать одну из следующих команд - во-первых, Влияющие ячейки - Эта команда для ячейки, в которой содержатся формулы .

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

Видите, соответственно, мы здесь эти стрелочки видим. Вот они. В данном случае, стрелки идут с A1 и B1 на C1, потому что эти ячейки соответственно влияют. Давайте откроем подменю еще раз, и посмотрим, что у нас здесь есть еще. Влияющие ячейки мы уже выполнили. Зависимые ячейки команда, которая позволит отобразить, на какие ячейки влияет данная активная ячейка. Ну, в данном случае, активная ячейка у нас ни на что не влияет .

Давайте, мы с вами перейдем вот сюда, в данном случае, отключим эти самые зависимости. Отключаются зависимости с помощью вот этой команды Убрать все стрелки, потом перейдем, например, на ячейку A1и выполним команду Зависимые ячейки. Вот так вот. Видите, сейчас у нас получается следующее, что данная ячейка опять таки влияет вот на эту. В данном случае, зависимая ячейка от A1 у нас данная. Дальше. Опять таки отключаем стрелки и выполняем следующую команду Источник ошибки. Вот так вот. Эта команда используется для ячейки, которая содержит ошибку. После ее выполнения, на экране появляются стрелки к ячейкам, которые, собственно, могут вызывать данную ошибку. Ну, данную ошибку у нас на самом деле могут выполнять обе ячейки. Ну, давайте снова выполним команду Убрать все стрелки. Вот так вот. Если вы обратите внимание, рядом вот с этой выделенной ячейкой у нас имеется вот такой вот смарт-теги, в котором мы можем выбрать что мы именно хотим делать - Показать панель аудита формул, Показать все этапы вычисления, и так далее, Изменить в строке формул. Предположим, если мы хотим Показать все этапы вычисления - у нас здесь показывается, что именно у нас было вычислено. Ну это мы с вами пройдем чуть позже .

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

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

260 TeachPro Microsoft Excel 2003 Давайте мы это окно с вами закроем. Еще раз выполним команду, например, Влияющие ячейки

- вот они. Если мы сейчас будем вот так вот щелкать на данных стрелках, мы будем соответственно вот таким вот образом переходить на зависимые ячейки. Вот так вот видите?

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

–  –  –

ячейки у нас соответственно могут быть источником данной ошибки. Вот так вот. Собственно вот таким вот образом, мы можем опять таки убрать все стрелки. С помощью вызова вот этой кнопочки, мы можем Создать примечание, то есть можно ввести примечание к ячейке. Таким вот образом, нажали, и здесь уже можно ввести это самое примечание. Если нам что-то не нравится, мы можем соответственно его удалить. Примечание мы с вами рассмотрим чуть позже .

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

Эта кнопка Отменяет обводку неверных данных соответственно. Следующая кнопочка, это Показать окно контрольного значения. Вот, при нажатии на эту кнопочку открывается вот данное окно - окно контрольного значения. И здесь мы можем динамически оценивать содержимое отдельных ячеек и значений переменных, мы с вами будем это делать, собственно чуть позже .

Поэтому пока давайте это окно закроем. И последняя кнопочка, вот эта вот, это называется Вычислить формулу. Мы нажали на эту кнопку, появилось окно Вычисление формулы .

Мы можем вычислить, например, вот, ну, собственно, никаких изменений у нас не произошло, потому что ячейка В1 содержит ноль, можем сделать Шаг за ходом - соответственно Шаг с выходом, то есть использовать некоторое вложение, ну, сейчас мы с вами это делать не будем, а просто закроем данное окно. Вообщем-то со всеми кнопками Панели инструментов Зависимости, мы с вами ознакомились. На этом давайте закончим текущий урок. На следующем уроке мы продолжим изучение поиска исправления ошибок в Excel .

12.4. Проверка орфографии В Microsoft Excel, подобно многим другим приложениям под Windows, существуют встроенные средства проверки орфографии. С помощью этих средств мы можем проверять текст как в выделенном диапазоне, так и во всем файле .

Проверка осуществляется посредством сравнения слов документа со словарями словарей Windows .

Эти словари используются всеми другими приложениями Microsoft Office, так же кроме того, можно создавать свои собственные словари .

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

Что мы с вами делаем? Во-первых, необходимо выделить диапазон ячеек, которые мы с вами хотим проверить. Предположим мы хотим проверить весь файл. Далее, что мы с вами делаем? Мы делаем вот что: выполняем команду Сервис - Орфография. Перед нами появляется вот такое вот диалоговое окно, которое называется Орфография русским. В данном случае, проверяется согласно русскому языку. Например, в данном случае, ошибка у нас на слове «автозаполнение», написано «аватозаполнение» .

Соответственно, если Excel не предлагает какую-нибудь свою версию ответа, мы должны уже вручную здесь вот таким вот образом подправить и нажать кнопку Заменить. Нас предупреждает, что слово «автозаполнение» не найдено в словаре. Изменить ли его, или нет? Мы меняем, потому что мы знаем, что «автозаполнение» пишется «автозаполнение» и продолжаем. Здесь у нас создание Автошаблона. Автошаблона опять таки нету в словаре Microsoft Excel. Мы можем либо пропустить это слово, если мы уверены, что оно встречается один раз или нажать кнопку Пропустить все, тогда все данные слова, если у нас слово «Автошаблон» используется несколько раз, они будут пропущены .

262 TeachPro Microsoft Excel 2003 Ну, предположим нас интересует Пропустить все. В данном случае, у нас произошло вот что .

Слово «Windows» написано на английском языке, соответственно в русском словаре его нет и быть не может, если мы нажмем на кнопку Параметры, вот, мы здесь увидим вот что. Мы увидим соответственно вот такое вот, диалоговое окно, где у нас имеется Язык словаря. В данном случае, мы проверяем русский текст. У нас должен быть русский язык и соответственно, имеется Пользовательский словарь. Что такое Пользовательский словарь мы с вами узнаем чуть позже .

Кроме того, имеются вот такие вот опции .

С опциями мы познакомимся так же чуть позже. Слово «Windows» - оно вполне нормальное, поэтому нажимаем кнопку Пропустить все и посмотрим, что у нас здесь еще есть. Здесь у нас неправильно написано слово «сетки». Здесь должно быть «графические объекты линии сетки», а написано «сетики». Соответственно, Excel обнаружил, что слово «сетики» отсутствует у него в словаре, и предлагает следующие варианты ответа: «светики», но в данном случае оно для совершенно неприемлемо, «септики», «сетки», «сетник», и так далее. Естественно, нам нужно «сетки». В данном случае мы можем заменить только это вхождение, то есть только в этой строчке, или заменить все неудачные слова на «сетки» .

Ну давайте сделаем Заменить все, может быть мы где-нибудь ошиблись еще раз. ClipArt и WortArt опять таки английское слово, нажимаем Пропустить. Соответственно WortArt Пропустить так же. Пропустить ActiveX. Слово Excel у нас опять таки отсутствует в основном словаре, потому что слово Excel слово вообщем-то английское, но мы нажмем кнопку Пропустить все, потому что слово Excel у нас встречается достаточно часто. Слово Access, Outlook, ftp, Internet, Explorer, Вебузел соответственно данного слова из-за того, что оно новое, вообщем-то в данном словаре отсутствует, Web. Слово «сортировка» у нас было объективно написано неправильно. Давайте мы его сейчас заменим - нажимаем на кнопку Заменить. Автофильтр просто отсутствует в словаре .

Если мы уверены, что слово написано правильно, но в словаре оно отсутствует, мы можем нажать вот эту кнопку Добавить в словарь - после чего уже это слово будет присутствовать в словаре и не будет выдаваться ошибка. Знакомство с Microsoft Query – Microsoft у нас опять таки на английском языке, Query также - лучше всего Пропустить все .

Видите, когда у нас встретился Microsoft, мы нажали на кнопку Пропустить, поэтому дало ошибку в следующий раз. Давайте нажмем Пропустить все. Web как современное заимствование у нас также в словаре отсутствует. Здесь у нас название функции. Мы Пропускаем все, потому что название функции объективно не может быть в словаре. Это следующая функция, опять таки Пропустить все, опять название функции. Слово «консолидирование» мы написали не правильно, поэтому совершенно спокойно нажимаем на кнопку Заменить. Сейчас нам предлагается Продолжить проверку орфографии сначала листа. Слова «автозамена» у нас опять таки нет – Пропустить, «автозавершение» - Пропустить, «автоформат» - Пропустить. Все. Весь лист у нас был проверен. Мы совершенно спокойно можем нажать на кнопочку OK, будучи уверенными, что ошибок на листе, кроме орфографических, у нас нет. Ну, на этом давайте закончим текущий урок .

На следующем уроке мы продолжим работу с Microsoft Excel .

12.5. Орфография. Словари Продолжаем изучение Проверки орфографии. Давайте вызовем сейчас наше диалоговое окно Проверка орфографии еще раз – Сервис - Орфография. Что мы здесь можем сделать? Кстати в нашем тексте достаточно часто встречаются английские слова, мы можем здесь переключить язык словаря на английский, но английских имеется несколько словарей .

Как вы знаете, например, орфография английского английского отличается от орфографии американского английского. Ну, мы сейчас, предположим, выберем английский США и продолжим проверку орфографии. В данном случае, все слова написанные русскими буквами, у нас будут восприниматься, как ошибочные. Вот, сейчас видите, «ввод» у нас стало ошибкой, хотя на самом деле это стандартное слово. Давайте еще раз нажмем на кнопочку Параметры. Сейчас Глава 12. Поиск и исправление ошибок .

Использование сценариев 263 здесь нам сообщается, что язык словаря у нас английский, потому что мы с вами это переделали в английский язык. Пользовательский словарь у нас CUSTOM. DIC - здесь, если мы проставим эту галочку, то параметры для замены, то есть слова для замены у нас будут предлагаться только из основного словаря, то есть, из пользовательского предлагаться не будут .

Мы можем установить вот этот флажок - и будут пропускаться слова, которые состоят из больших букв. В данном случае стоит галочка пропускать слова с цифрами, то есть, если слово у нас имеет еще цифры внутри себя, то они будут просто пропускаться. Если нет, то пропускаться не будут и, соответственно, пропускать адреса Интернета, имена файлов. Если мы нажмем на Параметры автозамены - у нас появится знакомое нам диалоговое окно Автозамена, соответственно. С этим окном вы знакомы. Мы на одном из прошлых уроков его собственно проходит .

Ну, давайте вернемся в наше окно Орфография. С помощью кнопочки Добавить в словарь, у нас открывается пользовательский словарь. В данном случае, так как данное слово у нас написано с помощью кириллицы, то нам сообщается, что слово содержит не допустимые знаки, и не может быть добавлено. Давайте заменим это на русский язык, все таки, и постараемся добавить слово «автоформат» в наш словарь. Нажали на кнопку - слово «автоформат»

добавлено. Точно так же поступили со словами «автозамена», затем «автошаблон» и так далее .

Предположим мы хотим создать новый дополнительный словарь. Что нам для этого нужно сделать? Надо закрыть это диалоговое окно и выполнить команду Сервис - соответственно Параметры. В этом диалоговом окне нам следует перейти на вкладку Орфография, на которой мы сейчас, собственно, и находимся. Здесь нам предлагается добавлять слова в CUSTOM. DIC .

Мы можем дать другое имя словаря, предположим пусть это будет вот такими латинскими буквами nash.DIC, то есть наш словарь и, соответственно, добавляться слова новые будут уже туда. CUSTOM. DIC у нас как бы останется. Давайте в этом убедимся. Орфография - нажмем на Параметры - видите, здесь у нас уже имеется два словаря CUSTOM. DIC и nash.DIC, куда именно добавляются слова. На самом деле это очень удобно. Вы можете сделать на самом деле тематические словари. Например, термины, которые не может распознать Excel, к примеру, финансовые - добавлять в один словарь, термины, посвященные информатике - соответственно, в другой, и так далее. Ну, вообщем-то понятно. Давайте на этом закончим изучение соответственно орфографических возможностей Microsoft Excel. На следующем уроке мы с вами займемся Примечаниями .

12.6. Примечания. Создание Сейчас мы с вами начинаем новую тему, которая называется Примечание. Для любой ячейки рабочего листа можно написать примечание. Оно будет выводиться на экран, когда пользователи будут проводить указателем мыши по ячейке, в которую, собственно, это Примечание и вставлено. В Примечаниях обычно пишут какие-нибудь пояснения к рабочему листу, или соответственно к данным. Если вы хотите передать документ другому человеку, например, начальнику на проверку, то с помощью примечания, ему будет легче понять, что вы делали .

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

Ну, давайте попробуем создать наше новое Примечание. Во-первых, для этого требуется выделить ячейку, для которой мы это самое примечание создаем. Например, вот это - потом 264 TeachPro Microsoft Excel 2003 выполняем команду Вставка. Давайте полностью откроем данное меню и выполним команду Примечание. Вот. У нас открывается вот такой вот желтый квадратик, в котором собственно мы можем это примечание и вписать. Ну, например, давайте напишем здесь что-нибудь, к примеру фразу: «Здесь у нас ошибка», но на самом деле, это понятно и без данного примечания. Но, с другой стороны что-нибудь написать вообщем-то и нужно .

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

Команду, кстати давно нам знакомую – Сервис - Параметры. Здесь, в данном окне, мы переходим на вкладку Вид и, примечаниям у нас посвящены вот эти три переключателя. Как именно отображается наше примечание? Во-первых, если мы выберем, не отображать, то даже индикатор отображаться у нас не будет .

Давайте попробуем это сделать. Вот видите красный треугольничек исчез и собственно, ничего у нас не отображается. Ну, давайте вернем, хотя бы как было. Если мы выберем только индикатор, соответственно будет отображаться красный треугольничек, а само примечание будет обнаруживаться, когда мы будем подводить курсор мыши. Ну, и третий пункт соответственно, у нас остался примечание – индикатор. Если мы выберем вот этот третий пункт, то у нас примечание все время будет на экране, вне зависимости от того, где находится в данный момент курсор мыши. Вот так вот. Вообще-то мы разобрались, как именно создавать примечание. На этом давайте закончим текущий урок. На следующем уроке мы научимся использовать Примечание .

–  –  –

«ошибка», вот так вот. Далее - Область поиска. Открываем. И именно здесь мы открываем пункт Примечание, то есть, где именно искать .

Если мы сейчас нажмем кнопку Найти далее, соответственно у нас выделилась вот эта ячейка, потому что в его примечании у нас имеется слово ошибка. Вот так вот. После того, как, предположим, нашли все примечания с данным словом, хотя у нас на самом деле, даже если мы сейчас нажимаем Найти далее, у нас больше ничего не находится, потому что данного слова у нас больше нигде нет. Во всех остальных примечаниях, у нас просто слово Примечание. Вот. После того, как мы обнаружили все ячейки в данном Примечании, мы можем нажать на кнопочку Закрыть. Данное окно у нас будет просто на просто закрыто. Вот так вот. Что мы еще с вами можем сделать? Мы с вами можем Изменить примечание .

Во-первых, для этого надо выделить ячейку, которая имеет определенное примечание, выполнить команду Вставка. Вот так вот и здесь выполнить команду Изменить примечание, после чего, мы уже можем его спокойно менять, например, написать новое примечание. И соответственно, у нас здесь уже будет соответственно новый текст. Предположим, мы хотим удалить примечание из нашей ячейки. Ну, во-первых, мы можем сделать следующее. Меню Правка - выполнить команду Очистить и здесь в подменю выбрать пункт Примечания. Вот так вот. Соответственно, видите в данной ячейке примечание у нас было удалено, а если мы хотим удалить все ячейки - в Примечании нужно выделить соответственно ячейки, содержащие примечание. Для этого мы выполняем команду Правка – Перейти - Выделить, и здесь соответственно оставляем Примечания. Мы это сделали сначала, а потом выполняем команду Правка - соответственно Очистить примечания .

Тогда будут удалены все примечания одновременно. Ну, на этом давайте закончим работу с Примечаниями. В общем-то, мы разобрали все возможные случае. Что именно нам пригодится для работы с ними. На следующем уроке Мы с вами начнем новую тему, которая называется Проверка результатов с помощью сценариев .

12.8. Проверка результатов с помощью сценария

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

Ощутимую помощь в анализе такого рода задач, могут оказать сценарии EXCEL, то есть сценарии EXCEL, это инструмент, который позволяет моделировать различные физические, экономические, математические и другие задачи .

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

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

Давайте напомним несколько положений. Во-первых, математический маятник состоит из невесомой не растяжной нити, и точки, которые обладают некоторой массой. Предполагается, что 266 TeachPro Microsoft Excel 2003 угол наклона достаточно мал. Воздействие внешней среды пренебрежительно мало. И решаем следующую задачу. Дано: у нас имеется длина нити L. Имеется ускорение свободного падения данной точки земной поверхности. Вы это из школьного курса физики помните, что это у нас ?? .

Имеется масса груза M. Имеется начальное значение угла. Предположим это у нас будет на ноль .

Рассматривая это состояние системы в момент времени T. Что нам нужно найти? Нам нужно найти период колебаний маятника, амплитуду колебаний, собственную частоту, значение дуговой координаты материальной точки, потенциальную кинетическую полную энергию системы в момент времени T. Вообщем найти нужно все что можно .

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

Это, у нас соответственно производная, прибавить два, фактически в степени два, умноженное на X. И соответственно равняется, равное нулю. Именно вот это уравнение движения и имеется. Что такое X? X – это дуговая координата, которая меняется в зависимости от времени. Давайте это тоже изобразим. Вот так вот. Она меняется в зависимости от времени. А K

– это, собственно, частота системы. Причем K, у нас равна корню квадратному из G деленное на L .

Ну, давайте, корень квадратный мы в данном случае покажем следующим образом. Мы все это возьмем в скобочки. Покажем степень. Вот так вот. И соответственно здесь у нас будет единица деленная на два. Вообщем не очень красиво, но это корень квадратный собственно и есть. Общее решение этого уравнения имеет следующий вид, соответственно X равняется X ноль умноженное на косинус K умноженное на T плюс A ноль. Где соответственно X ноль – амплитуда колебаний .

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

Давайте мы здесь это подправим. M – у нас масса груза. A ноль – это у нас соответственно начальный угол. А T у нас соответственно момент времени. Ну ?? давайте все таки заменим на момент. А измеряется соответственно в секундах. Вот у нас получились вот такие вот данные .



Pages:     | 1 | 2 || 4 |


Похожие работы:

«Ученые записки университета имени П.Ф. Лесгафта. – 2018. – № 6 (160). УДК 796.42 СТРУКТУРА СОРЕВНОВАТЕЛЬНОГО РЕЗУЛЬТАТА У ДЕСЯТИБОРЦЕВ РАЗНОЙ КВАЛИФИКАЦИИ Олег Борисович Немцев, доктор педагогических наук, профессор, Наталья Алексеевна Немцева, кандидат педагогических наук, доцент, Адыгейский государственный университет (АГУ), Майкоп; А...»

«Урок русского языка в 9 классе. Тема: Обучение сжатому изложению.Цель урока: Выявить принципиальные различия между подробным и сжатым изложением.Задачи урока: Формирование знаний способов и приёмов сжатия текста. Формирование навыков работы с текстом. Прививать интерес к научным исследованиям. Оборудование: план-конспект урока...»

«Юлия Крейнина Малер и Клее: на пути к разгадке природы динамической формы в музыке и изобразительном искусстве Принято думать, что Энгр упорядочил покой; Я хочу – без пафоса – упорядочить движение. Пауль Клее, сентябрь 1914 г.1 По мнению биографа Клее Хайо Дюхт...»

«Т.И. Печерская, А.А. Пономарева Новосибирский государственный педагогический университет Сюжетная ситуация учитель на кондиции в русской классической литературе Аннотация: В статье рассматриваются типологические черты сюжетной ситуации учитель на кондиции, вошедшей...»

«Марина Тригук Категории определённости : неопределённости и ретроспекции : проспекции в репрезентации образа автора в рассказе Т. Толстой "Милая Шура" Acta Universitatis Lodziensis. Folia Linguistica Rossica 11, 149-157 A C TA U N I V E R S I TAT I S L O D Z I E N S I S FOLIA LINGUISTICA RO...»

«Управление образования Администрации Сергиево-Посадского муниципального района Московской области Муниципальное бюджетное общеобразовательное учреждение "Средняя общеобразовательная школа №16" 141305, Московская область, г. Сергиев Посад, ул. Клубная, д.9 Тел/факс: 8(496)540-47-59, 549-17-02 shatr16@mail.ru Сценарий открытог...»

«Бексяк Марина Владимировна, педагог организатор, НР МОБУ "ПСОШ №2"1. Игры на знакомство. А, ну-ка все вместе! Все участники стоят лицом в круг. Первый, кто начинает игру, называет свое имя, добавляя к нему какой-либо жест. Остальные...»

«МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ Федеральное государственное бюджетное образовательное учреждение высшего образования Калмыцкий государственный университет имени Б.Б. Городовикова Инженерно-технологический факультет "УТВЕРЖДАЮ" Декан инженернотехнологического...»

«УТВЕРЖДАЮ Директор ЭНИН _Завьялов В.М. "_"_2017 г. РАБОЧАЯ ПРОГРАММА ДИСЦИПЛИНЫ БАЗОВАЯ Монтаж, наладка и эксплуатация электрооборудования систем электроснабжения Направление ООП 13.03.02 Электроэнергетика и электротехника Профиль (-и) подготовки Электроснабжение и автоматизация объектов нефтегазовой промы...»

«1. 1.ЦЕЛИ ОСВОЕНИЯ ДИСЦИПЛИНЫ Целью освоения дисциплины "Дирижирование и Хоровой класс" является формирование компетенций, необходимых для практической работы с хоровым коллективом.Задачи: Изучение элементарных основ дирижерской техники, дирижирование хоровыми произведениями без сопровождения и с сопровождением; Развитие навы...»

«1. Цель освоения дисциплины Целью освоения дисциплины "Лексикология" является формирование фундаментальных основ лексической системы современного немецкого языка.Воспитательная цель курса определяется следующими аспектами: подгот...»

«Даугавпилсская 13 средняя школа Конкурссочинений по творчеству Сергея Есенина. Моё любимое стихотворение С.А. Есенина (восприятие, оценка, истолкование) Свободная тема. Ессе. Автор: Лина Кузнецова, 11 класс, Даугавпилсская 13...»

«Муниципальный конкурс на лучшую методическую разработку организации непрерывной образовательной деятельности с детьми дошкольного возраста "Театр в детском саду: речь и игра, творчество и развитие" Номинация "По тропикам знакомых сказок и произведений" Средняя групп...»

«gdz_rabochaya_tetrad_po_russkomu_yazyku_bajkova_1_chast_4_klass.zip Имидж замирения коряжины изредка ниточный и наспех бодро с ним сполагоря зашивать эталон таковой работы. на фосфоритной орхидее недоплачено нисколько невтерпёж безмерных из них — activision blizzard (atvi) electronic arts (ea) take two (ttwo) konami (knm) z...»

«Направление ординаторов 1года обучения на кафедры 31.08.01 Акушерство и гинекология Кафедра акушерства и гинекологии ЛФ 04.09 в 9.00 Ленинский пр,8,корп10,1этаж, кафедра. Кафедра акушерства и гинекологии ПФ 04.09 в 9....»

«Нгуен Тхи Ким Нган Михаил Сергеевич Воскресенский – выдающийся музыкант современности Специальность 17.00.02 – “Музыкальное искусство” Автореферат диссертации на соискание ученой степени кандидата искусствоведения Москва Работа выполнена в ФГБОУ ВО "Московская государственная консерватория имени...»

«Приложение № 1 к приказу УО от "10" сентября 2015г. № 1071 Список состава жюри школьного этапа всероссийской олимпиады школьников в 2015/2016 учебном году № ФИО Должность Предмет п/п МБОУ "Белоколодезянская СОШ имени В.А.Данкова Шебекинского района Белг...»

«. роект с использованием АРТтехнологий новых пластических материалов отечественного производства в многоступенчатой системе образования на этапах среднего профессионального, начального школьного и дошкольного образования Руководитель...»

«НАЧАЛЬНОЕ ОБУЧЕНИЕ ПЛАВАНИЮ В ПРОЦЕССЕ ВОСПИТАНИЯ УЧАЩИХСЯ ОБЩЕОБРАЗОВАТЕЛЬНЫХ ШКОЛ (Белиловец А.Г., слушатель ИППК БГУФК) Человек может быть научен многому. Многому он может научиться сам. Однако обучение может происходить, с разной степенью эффективности, многое зависит от нас учителей! Обучающий обязан...»

«РЕЦЕНЗИИ Ю. И. СЕМЕНОВ НЕВЕЖДА И ШАРЛАТАН В РОЛИ УЧИТЕЛЯ МУДРОСТИ (Гуревич П. С. Введение в философию: Учебное пособие для учащихся 10–11 классов средней школы. М., 1997. 400 с.) Когда ограничиваешься лишь перелистыванием этой книги, то невольно по...»

«1897 года.16. Ф ев р а л я ш МІНРХІЯЯЬИЫД ІФДОНФ^ТИ хіа.Р) а.іщх ВЫХОДЯТЪ пДВА РАЗА ВЪ МСЯЦЪ Р Подписка Р с; Цна годовому Р г-" 5 принимается въ г изданію съ пег -^ 1 И ІЬ ЧИС Л Ъ. ^ редакціи (Ь ресылкой броЬл Епархіальныхъ шюрованному (?), (^...»

«Муниципальное автономное общеобразовательное учреждение "Средняя общеобразовательная школа №160" Тайгинского городского округа Колорадский жук: польза или вред Работа на XVIII Всероссийскую научно-практическую конфе...»

«Муниципальное бюджетное дошкольное образовательное учреждение детский сад комбинированного вида "Алёнушка"Подготовила: воспитатель подготовительной к школе группы компенсирующей направленности ТНР...»

«Вестник прихода храмового комплекса Владимирской Иконы Божией Матери № 1, 2007 г. ВОСКРЕСЕНИЯ ДЕНЬ И просветимся СЛОВО НАСТОЯТЕЛЯ Дорогие прихожане! торжеством, и друг друга Братия и сестры! обымем. Мы с вами вместе прошли путем Великого Поста Христос воскресе из мертвых, через Страстную седмицу к великому празднику – Пасхе смертию смерть п...»







 
2019 www.mash.dobrota.biz - «Бесплатная электронная библиотека - онлайн публикации»

Материалы этого сайта размещены для ознакомления, все права принадлежат их авторам.
Если Вы не согласны с тем, что Ваш материал размещён на этом сайте, пожалуйста, напишите нам, мы в течении 1-2 рабочих дней удалим его.