НАУЧНЫЕ РАБОТЫ

Учебное пособие ''Табличный редактор Microsoft Excel'' (версий 95, 97), КАЗАНСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ, Индейкин В.В., 1999 год.

| |ая |таблица'!D9|таблица'!D1|таблица'!D2|(C$5:C$|(D$5:D$|(E$5:E$|

| |таблица'| |5 |1 |9) |9) |9) |

| |!C15 | | | | | | |

| |ая |таблица'!D1|таблица'!D1|таблица'!D2|(C$5:C$|(D$5:D$|(E$5:E$|

| |таблица'|0 |6 |2 |9) |9) |9) |

| |!C16 | | | | | | |

|0| |) |) |) | | | |

|1| |) |) |) | | | |

Таблица 8

| |F |G |H |

|3|средняя стоимость |

А так будет выглядеть результат этих формул:

Таблица 9

| |Закупочная стоимость |средняя стоимость |

|Frutis |2.00руб |4.00руб |6.25руб |2.04руб|4.07руб|6.04руб |

|SUMP |2.32руб |3.65руб |5.32руб |2.04руб|4.07руб|6.04руб |

|Forum |1.98руб |4.05руб |6.03руб |2.04руб|4.07руб|6.04руб |

|Like |1.86руб |4.66руб |5.99руб |2.04руб|4.07руб|6.04руб |

|UFO |2.05руб |3.98руб |6.60руб |2.04руб|4.07руб|6.04руб |

|максим. |2.32руб |4.66руб |6.60руб | | | |

|миним. |1.86руб |3.65руб |5.32руб | | | |

Обратите внимание: в таблице созданы поля средней стоимости, содержащие

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

прямую на графике.

После этого, легко построить диаграмму анализа закупочных цен:

[pic]

Рис. 33. Итоговая диаграмма

Линии средних цен нужно отформатировать иначе, чем это делает мастер.

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

и сделать пунктирными.

Некоторые специальные возможности

В Excel существуют некоторые интересные возможности позволяющие

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

из таких возможностей.

1 Автоформат

Команда Формат | Автоформат... позволяет быстро оформить готовую

таблицу воспользовавшись готовым Списком форматов.

[pic]

Рис. 34. Автоформат

2 Решения уравнений

Одной из интереснейших особенностей Excel является возможность

нахождения корней уравнений типа f(x)=a. Для этого необходимо в какой-либо

из ячеек ввести предполагаемое (начальное) значение корня уравнения, в

другой ячейке записать само уравнение f(x), выполнить команду Сервис |

Поиск решения.... В окне Поиск решения, в поле Установить целевую ячейку

записать адрес ячейки где записано уравнение f(x), в поле Равной: |

Значению ввести значение a, в поле Изменяя ячейки ввести адрес ячейки где

находится предполагаемое (начальное) значение корня уравнения и нажать

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

(начальное) значение корня уравнения, появится вычисленное значение корня

уравнения с точностью заданной в разделе Параметры....

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

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

Замечание: Начальное значение необходимо подбирать с определенной

осторожностью, если функция, в которой ищутся корни, имеет

различные особенности, так как при поиске решения начальное

значение может находиться в области отрицательных градиентов, в

результате чего «спираль» поиска может быть не сходящейся, а

расходящейся.

[pic]

Рис. 35. Решение уравнения в Excel

3 Создание сценариев

Часто встречается необходимость одним и тем же ячейкам присвоить

разные значения, причем так, чтобы не потерять предыдущие значения. В Excel

это выполняется за счет механизма сценариев, вызов которого производится

через меню Сервис | Сценарии.... Откроется окно в котором будет список всех

имеющихся сценариев (вначале этот список пуст). Нажмите кнопку

"Добавить...", появится окно.

[pic]

Рис. 36. Окно создания сценария

В этом окне надо:

1. Дать имя сценарию;

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

3. Написать комментарии к данному сценарию в поле Примечание

4. Нажать кнопку ОК

[pic]

Рис. 37. Диспетчер сценариев

После этого появится новое окно, где нужно ввести значения для

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

создания нескольких.

Для того, чтобы заполнить ячейки значениями из конкретного сценария,

нужно выбрать его в списке и нажать кнопку Вывести.

4 Отчет по сценариям.

После того, как сценарии созданы, можно создать отчет по этим

сценариям. Для этого используется кнопка Отчет...

В окне Отчет по сценарию укажите: Тип отчета | Структура, а в поле

Ячейки результата - укажите адреса ячеек (разделенные знаком "точка с

запятой") в которых содержатся итоги (например: I13;I19;I25;I31;I38).

После этого будет автоматически создана новая страница с отчетом по

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

5 Создание макроса

Макрос - это запись определенной последовательности действий в Excel.

Такая запись выполняется на языке Visual Basic (или на языке

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

книги "Модуль#", которые являются редакторами этого языка.

Каждая такая запись должна иметь собственное имя. С точки

зрения программирования – это подпрограмма.

Sub сценарии()

' сценарии Макрос

' Макрос записан 15.08.99 (bm) Создание отчета по сценариям

Sheets("Структура сценария").Select

ActiveWindow.SelectedSheets.Delete

Range("A1").Select

ActiveSheet.Scenarios.CreateSummary ReportType:=xlStandardSummary,

ResultCells:=Range("I13,I19,I25,I31,I38")

End Sub

Создавать макросы можно «вручную», или автоматически - заставить

Excel отслеживать все действия и записывать их в соответствующий модуль.

Для этого нужно:

1. Для начала перейдите на ту ячейку, с которой начнется запись.

2. Войти в меню Сервис | Макрос | Начать запись...

3. В окне Запись макроса дать ему имя и в поле Описание записать краткий

комментарий: для чего этот макрос, кнопка ОК С этого момента все

действия записываются в виде инструкций Visual Basic. На экране

появится дополнительная панель инструментов с кнопкой [pic] Остановить

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

закрытия окна!

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

5. Нажать "Остановить запись". Запись действий в модуль прекратиться и

дополнительная панель исчезнет с экрана.

Выполнить макрос можно через меню Макрос | Макросы... - выбрать в

списке имя макроса - кнопкой Выполнить. Макросу также можно назначить

горячую клавишу, или кнопку на панели инструментов (это делается через

диалоговое окно "Настройка", вызов: меню Вид | Панели инструментов |

Настройка...).

6 Создание активной кнопки

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

элементы (элементы аналогичные тем, которые есть на диалоговых окнах) и

"заставить" эти элементы работать. Добавление новых управляющих элементов

на лист производится с помощью панели инструментов "Элементы управления".

(Для включения панели: щелкнуть правой кнопкой мыши на панелях инструментов

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

инструмент "кнопка". Нажать этот инструмент и указать место и размер

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

версии Excel:

1 Excel 95

Появится диалоговое окно "Назначить макрос" в котором нужно указать

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

изменить ее название.

2 Excel 97

Из контекстного меню на новой кнопке выбрать команду Свойства. В поле

Caption заменить стандартное название кнопки на необходимое.

Дважды щелкнуть на кнопке. Откроется окно редактора Visual Basic на

процедуре созданной для данной кнопки. Между строками начала и конца

процедуры дописать команду для запуска созданного ранее макроса:

Private Sub CommandButton1_Click()

Call сценарии

End Sub

"Сценарии" - это то имя макроса, например приведенного выше.

Вернуться в Excel и на панели инструментов Элементы управления отжать

кнопку Конструктор - [pic]. Теперь можно закрыть всю эту панель. Кнопка

готова.

Некоторые особенности (для углубленного изучения)

1 Полезные советы при работе с Excel 95 и Excel 97

1 Создание Web-страницы

Чтобы создать HTML-страницу при помощи Microsoft Excel, нужно:

1. Выделить ячейки, содержащие данные, которые вы хотите поместить на Web-

страницу.

1. Дать команду меню Файл | Сохранить в формате HTML.

1. Выполнить указания мастера Web-страниц.

Если в меню Файл отсутствует команда Сохранить в формате HTML, нужно

установить мастер Web-страниц. Для этого дайте команду меню Сервис |

Надстройки, отметьте “Мастер Web-страниц” и щелкните ОК.

Если в списке настроек отсутствует мастер Web-страниц, добавьте

средства создания Web страниц Office 97 при помощи программы установки

Microsoft Office 97.

2 Использование Web-запросов

Если ваш компьютер имеет доступ к Internet, то вы можете использовать

Web-запросы, чтобы загружать информацию из Web прямо в Excel 97. Дайте

команду Данные | Внешние данные | Выполнить Web-запрос. В диалоговом окне

Выполнить запрос выберите список имеющихся файлов Web-запросов. Выберите

интересующий вас запрос. Дополнительные файлы запросов можно получить при

помощи запроса Get More Web Queries или на

http://www.microsoft.com/officefreestuff/excel.

3 Сохранение файла Microsoft Excel 97 сразу в двух форматах

При помощи Excel 97 можно сохранять рабочие книги в формате, который

подходит для Excel 95, и при этом позволяет пользователям Excel 97

пользоваться всеми возможностями последней версии программы. Для сохранения

рабочей книги в таком формате выполнить следующие действия:

1. Дайте команду меню Файл | Сохранить как.

1. В раскрывающемся списке Тип файла: окна Сохранить как... выберите тип

“Книга Microsoft Excel 97 и 5.0/95”.

1. Щелкните кнопку Сохранить.

Если постоянно приходится сохранять рабочие книги Excel в этом

формате, установить этот тип файлов по умолчанию:

1. Дайте команду меню Сервис | Параметры.

1. Выберите вкладку Переход.

1. В раскрывающемся списке Сохранять файлы Excel как выберите тип “Книга

Microsoft Excel 97 и 5.0/95”.

1. Щелкните ОК.

4 Связь надписи с ячейкой электронной таблицы

Чтобы создать такую связь:

1. На панели инструментов Рисование щелкните кнопку Надпись.

1. Нарисуйте в таблице прямоугольный фрагмент для будущей надписи.

1. Нажмите F2, чтобы поместить курсор в строку формулы.

1. Введите знак равенства (=).

1. Щелкните ячейку, содержащую данные или текст, которые вы хотите

поместить в текстовый блок.

1. Нажмите Enter.

5 Блокировка доступа к личным ячейкам электронной таблицы

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

таблицы Microsoft Excel 97, выполните одну из нижеописанных процедур — в

зависимости от того, необходимо ли заблокировать большую или меньшую часть

ячеек.

Чтобы запретить возможность редактирования большинства ячеек:

1. Выделить ячейки, которые необходимо оставить незаблокированными. Для

выделения ячеек вразброс придерживайте клавишу Ctrl, когда будете

щелкать по ячейкам.

2. Дать команду меню Формат | Ячейки и открыть вкладку Защита.

3. Снить флажок Защищаемая ячейка и щелкнуть ОК.

4. Дать команду меню Сервис | Защита и выбрать Защитить лист. Ввести,

если нужно, пароль и щелкнуть ОК.

Чтобы защитить лишь некоторые ячейки:

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

верхнем углу таблицы или клавиши Ctrl+Shift+пробел.

2. Дать команду меню Формат | Ячейки и выбрать вкладку Защита. Снять

флажок Защищаемая ячейка и щелкнуть ОК.

3. Выделить ячейки, которые нужно заблокировать. Для выделения ячеек

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

4. Дать команду меню Формат | Ячейки и открыть вкладку Защита. Установть

флажок Защищаемая ячейка и щелкнуть ОК.

5. Дать команду меню Сервис | Защита и выбрать Защитить лист. Ввести,

если нужно, пароль и щелкнуть ОК.

6 "Повышение" точности вычислений формул

В следствии того что Excel отображает цифры в ячейках с меньшей

точностью чем их хранит (например 12.343 может отображаться как 12.34),

возможны визуальные эффекты "неточности" расчетов Excel в формулах.

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

этом становится "очевидным" для пользователя.

7 Перевод строки в ячейке таблицы

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

используйте Alt+Enter.

8 Локальное имя (использование одного и того же имени на разных листах)

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

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

глобальными. Использование локальных имен бывает полезным когда в рабочей

книги располагаются листы с одинаковыми формулами (например листы «отчет о

прибылях за июнь», «отчет о прибылях за июль» и т.п.). При этом формулы с

ссылками на локальные имена при копировании будут использовать имена нового

рабочего листа.

9 Отображение чисел с лидирующими нулями

Для этого необходимо установить формат отображения числа в ячейке с

необходимым количеством нулей. Это делается через меню Формат | Ячейки |

Пользовательский.

Замечание - можно ввести число как текст (например '001), но это не одно

и тоже.

10 Использование специальных символов в формате

Некоторые символы в строке формата являются специальными. Для того

чтобы их вывести можно использовать символ обратный слэш '\'.

11 Удалении ячеек рабочего листа без «порчи» ссылок на удаленные ячейки

Если Вы удаляете данные в листе (строки, столбцы), то все формулы

содержащие ссылки на удаленные данные будут скорректированы Excel как

#ссылка#. Этого можно избежать если не использовать операции удаления -

только «очистить», «скопировать» и «вставить».

СпрАвочный материал

1 Функциональные и «горячие» клавиши

Некоторые действия в Microsoft Excel можно выполнить значительно

быстрее, если использовать функциональные клавиши и клавиатурные комбинации

(«горячие» клавиши), ниже приведены их списки.

1 Функциональные клавиши

|F11 |Создать | | | | |

2 «Горячие» клавиши

|Операция |Сочетание клавиш |

|Клавиши для правки содержимого ячеек или строки | |

|формул | |

|Ввести набранные данные в ячейку |ENTER |

|Удалить набранные данные |ESC |

|Повторить последнее действие |F4 или Ctrl+Y |

|Начать новый абзац в текущей ячейке |ALT+ENTER |

|Вставить в ячейку символ табуляции |CTRL+ALT+TAB |

|Удалить выделенные символы или символ слева от |BACKSPACE |

|курсора | |

|Удалить выделенные символы или символ справа от |DELETE |

|курсора | |

|Удалить символы справа от курсора до конца строки |CTRL+DELETE |

|Переместить курсор на один символ вверх, вниз, влево |Клавиши со стрелками|

|или вправо | |

|Переместить курсор в начало строки |HOME |

|Перейти к правке примечания ячейки |SHIFT+F2 |

|Создать имена по тексту ячеек |CTRL+SHIFT+F3 |

|Заполнить вниз |CTRL+В |

|Заполнить вправо |CTRL+К |

|Заполнить выделенные ячейки набранным значением |CTRL+ENTER |

|Ввести данные в ячейку и перейти к ячейке, |ENTER |

|расположенной снизу | |

|Ввести данные в ячейку и перейти к ячейке, |SHIFT+ENTER |

|расположенной сверху | |

|Ввести данные в ячейку и перейти к ячейке, |TAB |

|расположенной справа | |

|Ввести данные в ячейку и перейти к ячейке, |SHIFT+TAB |

|расположенной слева | |

|Начать формулу |= |

|Перейти в режим правки содержимого ячейки |F2 |

|Очистить строку формул после указания ячейки или |BACKSPACE |

|удалить в строке формул символ слева от курсора | |

|Вставить имя в формулу |F3 |

|Присвоить имя |CTRL+F3 |

|Пересчитать все листы во всех открытых книгах |F9 или CTRL+= |

|Пересчитать текущий лист |SHIFT+F9 |

|Выполнить автосуммирование |ALT+ = |

|Ввести текущую дату |CTRL+; |

|Ввести текущее время |CTRL+SHIFT+: |

|Отменить результаты правки ячейки или строки формул |ESC |

|Завершить правку ячейки |ENTER |

|Начать новый абзац |ALT+ENTER |

|Вставить символ табуляции |CTRL+ALT+TAB |

|Скопировать содержимое верхней ячейки в текущую |CTRL+SHIFT+" |

|ячейку или в строку формул | |

|Переключить режимы отображения значения ячейки и |CTRL+`(знак левой |

|формулы ячейки |кавычки) |

|Скопировать формулу верхней ячейки в текущую ячейку |CTRL+' (апостроф) |

|или в строку формул | |

|Ввести набранную формулу в качестве формулы массива |CTRL+SHIFT+ENTER |

|Перейти к шагу 2 мастера функций, после набора в |CTRL+Ф |

|формуле имени функции | |

|Вставить в круглых скобках список аргументов, после |CTRL+SHIFT+Ф |

|набора в формуле имени функции | |

|Отобразить список автоввода |ALT+СТРЕЛКА ВНИЗ |

|Клавиши для форматирования данных | |

|Выполнить команду Стиль (меню Формат) |ALT+' (апостроф) |

|Выполнить команду Ячейки (меню Формат) |CTRL+1 |

|Выполнить форматирование общим числовым форматом |CTRL+SHIFT+~ |

|Выполнить форматирование денежным форматом с двумя |CTRL+SHIFT+$ |

|десятичными знаками после точки (отрицательные числа | |

|отображаются в круглых скобках) | |

|Выполнить форматирование процентным форматом с |CTRL+SHIFT+% |

|отсутствующей дробной частью | |

|Выполнить форматирование научным форматом с двумя |CTRL+SHIFT+^ |

|десятичными знаками после запятой | |

|Выполнить форматирование форматом для дат с полями |CTRL+SHIFT+# |

|дня, месяца и года | |

|Выполнить форматирование форматом для времени с |CTRL+SHIFT+@ |

|полями часов и минут и индексами A.M. или P.M. | |

|Выполнить форматирование форматом с двумя десятичными|CTRL+SHIFT+! |

|знаками после запятой | |

|Вставить рамку структуры |CTRL+SHIFT+& |

|Удалить все границы |CTRL+SHIFT+_ |

|Выполнить или удалить форматирование жирным шрифтом |CTRL+И |

|Выполнить или отменить форматирование курсивом |CTRL+Ш |

|Подчеркнуть текст или удалить линию подчеркивания |CTRL+Г |

|Перечеркнуть текст или удалить линию перечеркивания |CTRL+5 |

|Скрыть строки |CTRL+9 |

|Показать строки |CTRL+SHIFT+( |

|Скрыть столбцы |CTRL+0 (ноль) |

|Показать столбцы |CTRL+SHIFT+) |

2 Некоторые функции в EXCEL

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

определенный интерес и не совсем понятны с первого взгляда. Более полный

список функций см. в Приложении к данному пособию «Функции Microsoft

Excel».

1 Математические функции

1 Тригонометрические функции и функции работающие с углами.

COS() и SIN() - соответствуют своему написанию.

ACOS() и ASIN() - соответственно arccos и arcsin

TAN(), ATAN() - tg и arctg

ATAN2() - arctg для заданных координат x и y.

ГРАДУСЫ() - преобразует радианы в градусы.

РАДИАНЫ() - преобразует градусы в радианы.

ПИ() - (

2 Логарифмы и степенные функции

LN(), LOG10() и LOG() - натуральный, десятичный и логарифм с произвольным

основанием.

EXP() и СТЕПЕНЬ() - в комментариях не нуждаются

COSH(), SINH() и TANH() - гиперболические косинус, синус и тангенс

ACOSH(), ASINH() и ATANH() - гиперболические арккосинус, арксинус и

арктангенс

3 Функции округления

ОКРВЕРХ(), ЧЕТН(), ОКРВНИЗ(), ЦЕЛОЕ(), ОКРУГЛТ(), НЕЧЁТ(), ОТБР(),

ОКРУГЛ(), ОКРУГЛВНИЗ(), ОКРУГЛВВЕРХ(),- функции округления с различными

возможностями.

4 Операции с матрицами

Для операциий над матрицами в Excel применяют следующие средства:

1. Использование массивов

1. Встроенные функции: ТРАНСП, МУМНОЖ, МОПРЕД, МОБР

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

которая является ссылкой в указанных выше операциях. Формулы,

обрабатывающие массив отличаются от обычных следующим:

1. перед вводом формулы маркируется вся область массива;

2. завершается ввод таких формул - нажатием Shift+Ctrl+Enter;

3. формула заключается в фигурные скобки и повторяется во всех клетках

области

5 Функции СУММ() и ЕСЛИ() могут оперировать с массивами

Использование массивов в функциях СУММ() и ЕСЛИ() позволяет

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

имея столбец с датами и соответствующий ему столбец значений можно одной

формулой найти сумму всех чисел в заданном диапазоне дат. Еще один пример.

Кстати также можно использовать функции типа БДСУМ(), но это не всегда

удобно.

2 Как к дате добавлять месяцы

В Excel можно добавлять к дате дни. Если необходимо добавлять

определенное количество месяцев (например получить последовательность 5

янв, 5 фев, 5 мар и т.п.) учитывая число дней в месяце, надо использовать

функцию ДАТА(). Excel сам будет отслеживать число дней в месяце.

Список литературы

1. Колесников Р. Excel 97 (русифицированная версия). - Киев: Издательская

группа BHV, 1997. - 480 с.

1. Руководство пользователя. Microsoft Windows 95. - Neunkirchen: Buhl-Data-

Service Microsoft Corporation, 1995.

1. Рассел Борланд. Эффективная работа с Microsoft Word 97. - СПб: Питер,

1998. - 960 с.

1. Материалам эхоконференции RU.EXCEL (за июль-сентябрь 1997 года)

Collected by Kirienko Andrew, 2:5020/239.21@fidonet

1. Англо-русский словарь по программированию и информатике (с

толкованиями). - М.: Рус. яз., 1990. - 335 с.

1. http://www.microsoft.ru/main.html

1. http://www.microsoft.com/officefreestuff/excel

1. http://nsvrv.ziet.zhitomir.ua/bmu/Sppp/index.html

1. http://members.aol.com/gjlinker/projects/oraxcel

1. http://www.infoart.ru:8000/it/book/russ_red/excel5/content.htm

1. http://www.infoart.ru:8000/it/book/russ_red/excel5/intro.htm

-----------------------

[1] -1KG=> 4;O 1CE30;B5@88 8 ?@8@K.

[2] 'B>1K 2K45;8BL A@07C =5A:>;L:> 480?07>=>2 OG55: - =5>1E>48<> 2K45;8BL

?5@2K9, =060 Обычно для бухгалтерии и применялись табличные редакторы.

[3] Чтобы выделить сразу несколько диапазонов ячеек - необходимо выделить

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

ячеек.

[4] В данном пособие отождествляется понятие список и база данных, для

более простого понимания, на начальных этапах.

Страницы: 1, 2, 3, 4, 5, 6

Приглашения

09.12.2013 - 16.12.2013

Международный конкурс хореографического искусства в рамках Международного фестиваля искусств «РОЖДЕСТВЕНСКАЯ АНДОРРА»

09.12.2013 - 16.12.2013

МЕНЮ

НАУЧНЫЕ РАБОТЫ

Учебное пособие ''Табличный редактор Microsoft Excel'' (версий 95, 97), КАЗАНСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ, Индейкин В.В., 1999 год.

Приглашения

Международный конкурс хореографического искусства в рамках Международного фестиваля искусств «РОЖДЕСТВЕНСКАЯ АНДОРРА»

Международный конкурс хорового искусства в АНДОРРЕ «РОЖДЕСТВЕНСКАЯ АНДОРРА»