Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Каф. ИТ Вишневский А.Н. Защита книги Excel Страница 125 из 125
Табличный процессор Excel предназначен для решения задач, которые можно представить в виде таблиц чисел. Он позволяет хранить в табличной форме большое количество исходных данных, результатов и математических связей между ними. При изменении исходных данных, результаты автоматически пересчитываются и заносятся в таблицу. Excel содержит много встроенных функций, использование которых упрощает выполнение математических, статистических и финансовых операций.
Электронные таблицы состоят из столбцов и строк. Столбцы обозначены буквами латинского алфавита, расположенными в заголовочной части таблицы. Строки – цифрами, в первой колонке. В электронной таблице Excel (256) 16 384 столбцов А - XFD и строк (65536 – Excel 2003
1 048 576 строк). Место пересечения столбца и строки называется ячейкой. Каждая ячейка имеет уникальный адрес, состоящий из имени столбца и номера строки, например C4, F5. В электронной таблице можно работать как с отдельными ячейками, так и с группами ячеек, которые образуют блок или диапазон ячеек.
Запуск программы можно осуществить шестью способами:
После запуска программы на экране появляется Рабочая книга, которая является совокупностью Рабочих листов, сохраняемых на диске в одном файле. В каждом файле *.xlsx может размещаться одна книга, а в книге – от 1 до кол, рабочих листов сколько хватит памяти (электронных таблиц). По умолчанию, в каждой книге содержится 3 рабочих листа. Рабочий лист имеет табличную структуру и может состоять из любого числа страниц. Рабочие листы можно удалять, переставлять и добавлять новые. Для перехода от одного листа к другому, в пределах рабочей книги надо щелкнуть по его ярлычку. Ярлычок активного листа всегда выделен цветом, а подпись на нем – полужирным шрифтом.
Создавая новый документ, лучше сразу сохранить его, дать ему нужное имя.
Ячейка, в которой находится курсор, называется Активной (текущей). Ее границы выделены жирным цветом. Каждая ячейка имеет Адрес, состоящий из имени столбца и номера строки, на пересечении которых она образуется. (A3, C15, E9, …)
Блок представляет собой прямоугольную область смежных ячеек. Блок может состоять из одной или нескольких ячеек, строк или столбцов. Адрес блока состоит из координат противоположных углов, разделенных двоеточием (В13:С17, А12:Е20, А:С). Блок можно задать при выполнении различных команд или вводе формул посредством указания координат или выделения на экране.
Для выделения: Столбца или Строки– щелчок по их имени и номеру.
Блока смежных ячеек – A1:D3
Нескольких несмежных блоков – выделить первый блок (или ячейку), нажать Ctrl и удерживая ее выделить следующий блок. Пока клавиша Ctrl нажата, Excel не снимает выделения со всех ранее выбранных блоков.
Рабочего листа – щелчок в левый верхний угол таблицы на пересечении имен столбцов и строк
или Ctrl+A
Для снятия выделения щелчок в любом месте рабочего листа.
Прежде чем произвести какие-либо действия с блоком, его необходимо выделить. Действия по перемещению, копированию, удалению, очистке блока можно производить несколькими способами:
Для перемещения с помощью мыши необходимо выделить ячейку или блок, затем подвести указатель мыши к рамке блока или ячейки (указатель примет форму белой стрелки),
нажать левую кнопку мыши и перетащить блок на новое место. При копировании с помощью мыши необходимо удерживать нажатой клавишу Ctrl.
Для ввода данных необходимо переместится в нужную ячейку, и набрать данные, а затем нажать Enter или клавишу перемещения курсора.
Для ввода одинаковых данных в несколько ячеек одновременно:
Чтобы отредактировать данные после завершения ввода:
Данные в ячейках таблицы относятся к одному из следующих типов:
Окно программы Excel содержит все стандартные элементы, присущие окну приложения Windows и ряд дополнительных.
В верхней части окна находится Кнопка Office и
Панель быстрого доступа
Панель содержит название приложения Microsoft Excel, а так же имя текущего файла и кнопки (команды), которые мы сами размещаем по команде: (Кнопка Office – Параметры Excel – Настройка – Все команды), кнопки Свернуть, Развернуть (восстановить), Свернуть.
Можно воспользоваться кнопкой и выбрать «Другие команды»…
” Через кнопку «Добавить”
Под Панелью быстрого доступа расположены вкладки:
Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование Вид, Разработчик, ….. При щечке по вкладке разворачивается лента с группами инструментов
Каждая группа содержит кнопки, обеспечивающие работу а программе Excel 2007
Под Группами располагается Строка формул:
Ниже строки формул расположено Рабочее поле.
Под Рабочим полем таблицы располагается Полоса листов с ярлычками рабочих листов (лист1, лит2, лист3), кнопка Вставить лист {Shift+F11},
Полоса прокрутки.
Под Полосой листов расположена Строка состояния.
В Строке состояния представлена, как привило, различная справочная и служебная информация.
В ней высвечиваются кнопки: Слева: Готово. При вводе – Ввод.
Среднее выделенных ячеек;
Количество значений в выделенном фрагменте, которые содержат данные, Сумма выделенных ячеек и др.
Окно документа Excel можно разделять на два или четыре подокна и одновременно работать с разными частями одной и той же таблицы.
Для разделения на подокна:
Полоса разделения перемещается с помощью мыши.
Для закрытия подокна используется команда:
Excel, как и Word, является многооконной программой, т.е. позволяет одновременно открывать несколько документов.
Команда Вид – Новое окно.
Контекстное меню – это специальное меню, которое открывается нажатием правой кнопки мыши (М*). В зависимости от элемента, на котором выполнен щелчок, в меню будут представлены команды и функции, используемые для выполнения операций над соответствующем элементом.
Для выбора команды из контекстного меню нужно:
Иногда требуется объединить клетки (по вертикали или горизонтали), например, заголовок разместить по центру таблицы. В этом случае выделяются клетки, которые нужно объединить и:
3 способ: М* по выделенным ячейкам – Формат ячеек - Выравнивание - Объединение ячеек.
“Э
Текст - это набор любых символов. Если тест начинается с числа, то начать ввод необходимо с апострофа ‘ (En-*М клавишу )
Ввод осуществляется в текущую ячейку. После ввода следует нажать на клавишу ввода или любую клавишу управления курсором. По умолчанию текст выравнивается по левому краю. Если текст не помещается в ячейке, то он отображается в соседних ячейках. Если соседняя ячейка занята, текст обрезается по правому краю (но при этом в ячейке он будет сохранен полностью). Если во время ввода данных была допущена ошибка, то она может быть исправлена стиранием неверных символов при помощи клавиши <BackSpace> и набором их заново. Клавишей <Esc> можно отменить ввод данных в ячейку и ввести их заново.
Длинный текст можно разбить в ячейке на несколько строк (4 способа):
Числа в ячейку можно вводить со знаков =, +, - или без них. Если ширина введенного числа больше, чем ширина ячейки, то Excel изображает его в экспоненциальной форме либо вместо числа ставит символы ##### (при этом число в ячейке будет сохранено полностью).
Для ввода дробных чисел используется десятичная запятая или точка в зависимости от настройки Windows. По умолчанию – запятая.
Экспоненциальная форма используется для представления очень маленьких или очень больших чисел. Например, число 13500000 будет записано как 1,35Е+07, что означает 1,35*107. Число 0,000000006 будет представлено как 6Е-09, что означает 6*10-9. Для ввода дробных чисел используется десятичная запятая или точка, в зависимости от настройки (по умолчанию – запятая). Если десятичная дробь будет введено неправильно, то оно будет воспринято, как текст и прижато к левому краю или дата и прижато к правому краю. Формат числа изменяется по команде Формат, Ячейки …, Вкладка Число, где можно установить денежный, процентный и т. д. формат, а также указать число десятичных знаков.
Любой символ в середине числа превращает его в текст.
По умолчанию после фиксации числа в ячейке Excel сдвигает его к правой границе ячейки.
Excel сохраняет и обрабатывает числа с точностью до 15 знаков.
По умолчанию Excel выравнивает числа по правому краю, текст по левому, логические значения и сообщения об ошибках по центру.
Для изменения типа выравнивания:
I способ
II способ: Главная – Выравнивание - кнопка Объединить и поместить в центре.
надо ввести весь заголовок в крайнюю левую ячейку выше таблицы, затем выделить столько ячеек, сколько столбцов содержит таблица и команда:
надо сделать на границах строк или столбцов двойной щелчок мышью (**M).
Команда: Главная-Выравнивание
по горизонтали: по значению
по вертикали: По центру, По верхнему краю
Команда:
Главная - в группе Выравнивание выбрать кнопку Ориентация
Или
Главная-Выравнивание-Ориентация (выбрать нужное направление: *М в позицию нужного направления) - ОК.
Примечание: При изменении формата ячейки следите, чтобы она была активной, но не находилась в режиме редактирования данных (курсор не должен мигать в ячейке). ….вам будет доступна только группа «Шрифт».
Формат позволяет отображать числовые данные в том или ином виде.
Чтобы задать или изменить формат текущей ячейки нужно выполнить команду Главная – Выравнивание – Число и выбрать один из форматов:
и указать ширину (Высоту) в пт. (по умолчанию ширина столбца - 8,43 пт.).
надо ввести знак «=» затем указать ячейку, содержащую данные для расчета, затем ввести знак операции, указать следующую ячейку с необходимыми данными и т.д. После ввода всей формулы надо нажать «Enter»
Команда:
Разметка страницы-Параметры страницы – Страница - Альбомная
Строки вставляются выше, а столбцы левее выделенных ячеек и
столько, сколько их было выделено.
Выполнить команду:
1 способ: М* по ячейке – Вставить
В ДО Добавление ячеек выбрать строку или столбец.
2-ой способ: Главная – Вставить – Вставить строки (столбцы) на лист
Иногда необходимо один и тот же текст вписать на несколько листов, поэтому удобно их объединять:
щёлкнуть по ярлыку первого из них, затем, удерживая клавишу Shift щелкнуть по ярлыку последнего объединяемого листа.
щёлкнуть по ярлыку первого из них, затем, удерживая клавишу Ctrl, щелкайте по ярлыкам всех необходимых листов. На Панели быстрого запуска окна Excel появится надпись: [Группа].
Текст созданный на объединенных листах отобразится на всех после разъединения.
надо вызвать их контекстное меню (листы разъединяются самостоятельно при переходе на другой лист) и выбрать команду Разгруппировать листы.
Щёлкнуть М* по имени столбца
В КМ выбрать СКРЫТЬ
надо использовать соответствующие кнопки вкладки Главная группы Число (Увеличить/Уменьшить разрядность)
Или: Главная – Число – Числовой и выбрать число десятичных знаков.
Используйте функцию Автосуммирования. Для этого курсор установите в итоговую ячейку, нажмите на вкладке Формулы группы Библиотека функций кнопку , проверьте предлагаемый диапазон суммирования (при необходимости растяните бегущий пунктир мышью на суммируемые ячейки) и нажмите Enter.
Команда Другие функции:
А так же выбор кнопки
Выводит ДО Мастер функций – шаг 1 из 2
Формула начинается со знака равенства «=».
С помощью формул можно выполнять вычисления и анализировать данные.
Результат формулы отображается в ячейке, а сама формула в строке формул. Формула может содержать обращения к одной или нескольким функциям, адреса ячеек (блоков), константы, знаки арифметических операций: +, -, /, *, ^, %.
Функции можно вводить:
Если значения в ячейках, на которые есть ссылки в формулах, меняются, то результат изменяется автоматически. Если необходимо произвести аналогичные расчеты в других ячейках столбца или строки, то формулу можно скопировать при помощи заполнения. При этом соответствующие адреса ячеек в формуле будут автоматически изменены (за исключением абсолютных ссылок).
Для заполнения с помощью мыши необходимо навести указатель мыши на небольшой черный квадрат в нижнем правом углу ячейки или блока (маркер заполнения), при этом указатель принимает форму черного плюса, нажать левую кнопку мыши и потянуть вправо или вниз на необходимое количество ячеек.
Для ввода различных последовательностей (месяца, дни недели, года, арифметическая и геометрическая прогрессия, а также нетиповые последовательности, если возможно задать какую либо закономерность) используется функция Автозаполнения. Для этого в смежные ячейки (1-2) необходимо ввести информацию, задающую последовательность, затем выделить их и потянуть за маркер заполнения в нужном направлении на необходимое количество ячеек.
Функция это формула, выполняющая определенный тип вычислений. Значения, которые используются для вычисления функций, называются Аргументами.
Значения, возвращаемые функциями в качестве ответа, называются Результатами.
Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга знаком «;».
В качестве аргументов можно использовать числа, текст, ссылки, логические значения и формулы, которые в свою очередь могут содержать другие функции. Функция, являющаяся аргументом другой функции называется вложенной. Для облегчения работы с встроенными функциями используется Мастер функций.
В ДО Мастера функций (рис. ), в окне Категория надо выбрать пункт 10 недавно использовавшихся, затем в окне Функция выбрать необходимую функцию (если ее нет в этом списке, то открыть категорию Полный алфавитный перечень). Например, для вычисления максимального значения надо выбрать функцию МАКС. Затем нажать кнопку «ОК».
В появившемся окне, в строке Число1 надо ввести диапазон ячеек, среди которых необходимо найти максимальное значение (диапазон ячеек задается через двоеточие) и нажать «ОК».
Для исправления ошибок в клетке нужно выполнить действия:
сделать клетку текущей и выполнить одно из трех действий:
исправить
клавиша Enter или: **М по ячейке,
*М по значку в строке формул,
Нажатием клавиши F2)
При редактировании данных используются клавиши Delete, Backspace, Insert.
Для удаления содержимого текущей клетки нужно нажать клавишу Delete. Однако Delete удаляет не всё.
Поэтому следует выполнить команду: Главная - Очистить
Очистить всё – очищает Форматы, Содержимое, Примечания
В приложении Excel в ячейку можно добавить комментарий, вставив в нее примечание. Текст в примечаниях можно редактировать. Ненужные примечания можно удалить.
Добавление примечания
Приложение Excel автоматически отображает в примечании имя, которое появляется в поле Имя пользователя в группе Глобальные параметры Office в категории Личная настройка диалогового окна Параметры Excel (кнопка Microsoft Office , кнопка Параметры Excel). При необходимости можно изменить имя в поле Имя пользователя. Если имя использовать не нужно, выделите его в примечании, а затем нажмите клавишу DELETE.
Примечание. Параметры Цвет заливки и Цвет текста в группе Шрифт недоступны для текста примечаний. Чтобы изменить цвет текста, щелкните примечание правой кнопкой, а затем выберите команду Формат примечания.
Совет. Маленький красный треугольник в углу ячейки укажет на то, что к этой ячейке прикреплено примечание. При наведении указателя на этот треугольник отображается текст примечания.
Редактирование примечания
Примечание. Если выбрать ячейку, содержащую примечание, вместо команды Создать примечание в группе Примечания будет команда Изменить примечание.
Параметры Цвет заливки и Цвет текста в группе Шрифт недоступны для текста примечаний. Чтобы изменить цвет текста, щелкните примечание правой кнопкой, а затем выберите команду Формат примечания.
Удаление примечания
Excel позволяет быстро заполнить ячейки.
1-ый способ автозаполнения
С одинаковыми данными:
С некоторым шагом:
Таким образом можно заполнять название месяцев года, дней недель.
2-ой способ автозаполнения
1. Выделить ячейку.
2. Команда: Главная – Заполнить – Прогрессия
Пример автозаполнения по столбцам от 1 до 128 шаг 2 геометрическая прогрессия
Процесс заполнения показан на рисунке
В Excel 2007 представлено 17 способов форматирования при использовании кнопки Автоформат. Её необходимо установить на панель быстрого запуска:
Кнопка Office – Параметры Excel – Настройка –
Все команды – Автоформат - Добавить
Пользовательские стили таблицы будут доступны в группе Пользовательские после создания хотя бы одного стиля. Для получения дополнительных сведений о создании пользовательского стиля таблицы см. раздел Создание и удаление пользовательского стиля таблицы.
Появится окно Работа с таблицами с вкладкой Конструктор.
Используйте кнопки со стрелками для прокрутки доступных стилей таблиц.
Если окно программы Excel свернуто, стили таблиц доступны в коллекции Экспресс-стили таблиц в группе Стили таблиц
Пользовательские стили таблицы будут доступны в группе Дополнительно после создания хотя бы одного стиля. Для получения дополнительных сведений о создании пользовательского стиля таблицы см. раздел Создание и удаление пользовательского стиля таблицы.
Созданные пользовательские стили хранятся только в текущей книге и поэтому не будут доступны в других книгах.
В группе Просмотр можно посмотреть то, как будет выглядеть таблица после изменения форматирования.
Все таблицы текущей книги, использующие этот стиль, будут отображаться в формате по умолчанию.
Появится окно Работа с таблицами со вкладкой Конструктор.
Таблица отобразится в формате по умолчанию.
Стиль – Заголовок 1 (кнопка) – Изменить
Для форматирования элементов таблицы выберите параметры стиля таблицы
Появится окно Работа с таблицами со вкладкой Конструктор.
Три способа:
На вкладке Конструктор в группах:
представлены элементы для работы с колонтитулами: Число страниц, Текущая дата и т.д. (см. рис.)
Чтобы создать в Excel базовую диаграмму, которую впоследствии можно изменять и форматировать, сначала введите на лист данные для этой диаграммы. Затем просто выделите эти данные и выберите нужный тип диаграммы на ленте (вкладка Вставка, группа Диаграммы).
Решено построить диаграммы по данным исходной таблицы.
|
Название предприятия |
Размер ссуды, тыс. руб.(Р) |
Дата |
Относительная величина ставки процентов (i) |
Возвращенная сумма, руб. (s) |
Продолжительность периода начисления в годах (n) |
Сумма процентных денег, выплачиваемых за год (Iг) |
|
|
выдачи кредита (d1) |
погашения кредита (d2) |
||||||
|
Aльфа |
100,00р. |
02.мар |
11.дек |
1,8 |
240,05 |
2,52 |
140,05 |
|
Bиктория |
500,00р. |
10.янв |
01.ноя |
2 |
1310,96 |
3,24 |
810,96 |
|
Cмена |
350,00р. |
04.апр |
23.ноя |
1,5 |
685,14 |
1,44 |
335,14 |
|
Экспресс |
700,00р. |
15.апр |
12.дек |
2,5 |
1855,48 |
4,13 |
1155,48 |
|
Eлена |
1000,00р. |
22.фев |
08.окт |
1,7 |
2066,58 |
1,81 |
1066,58 |
|
Итого |
6158,21 |
13,14 |
3508,21 |
Для её наглядности диаграммы необходимо выбирать данные таблицы близкие по численному значению.
Построим гистограмму по стокам и по столбцам.
В гистограмме построенной По строкам – заголовки строк таблицы отобразятся в Легенде,
а заголовки столбцов – по оси Х
В гистограмме построенной По столбцам – заголовки столбцов отобразятся в Легенде,
а заголовки строк – по оси Х
Для построения диаграмм по строкам и по столбцам выбираем следующим данные:
|
Название предприятия |
Размер ссуды, тыс. руб.(Р) |
Сумма процентных денег, выплачиваемых за год (Iг) |
|
Aльфа |
100 |
140,0548 |
|
Bиктория |
500 |
810,9589 |
|
Cмена |
350 |
335,137 |
|
Экспресс |
700 |
1155,479 |
|
Eлена |
1000 |
1066,575 |
Выберем Объёмную гистограмму с группировкой
Получили диаграммы, которые необходимо отформатировать:
Для форматирования использовать вкладки:
И команды соответствующих групп
Команды этих вкладок можно использовать для изменения представления данных на диаграммах.
Например, вкладка Конструктор используется для отображения рядов данных по строкам или по столбцам, внесения изменений в исходные данные, изменения размещения диаграммы, изменения типа диаграммы, сохранения диаграммы в качестве шаблона или выбора предварительно определенных параметров макета и форматирования.
Вкладка Макет используется для изменения таких элементов диаграммы, как заголовки диаграмм и подписи данных, использования инструментов рисования, а также добавления к диаграмме текстовых полей и рисунков.
Вкладка Формат позволяет добавлять заливку цветом, изменять тип линий или использовать специальные эффекты.
Круговая диаграмма строится аналогично, но только по одному ряду данных.
Для оформления таблицы ее необходимо заключить в рамки, для этого используется Вкладка Граница ДО Формат ячеек
Выделить нужный диапазон
1-ый способ: Главная – Выравнивание – Граница
2-ой способ: М* - Формат ячеек – Граница
Сначала выбирается тип лини
Вкладка Заливка
Поле Цвет фона-заливка ячеек цветом
Поле Цвет узора- заливка ячеек цветовым узором
Для выравнивания можно использовать кнопки:
По левому краю, По правому краю, По центру, По ширине.
Формат – Граница рисунка – Толщина
Абсолютная, относительная и смешанная адресация ячеек и блоков
При обращении к ячейке можно использовать описанные раньше способы: В3, А1, С5 и т.д. Такая адресация называется относительной. При ее использовании в формулах Excel запоминает расположение относительно текущей ячейки. Так, если вводится формула =В1+В2 в ячейку В4, то Excel читает формулу как “содержимое ячейки, расположенной тремя рядами выше, прибавить к содержимому ячейки, расположенной двумя рядами выше”.
Если скопировать формулу =В1+В2 из ячейки В4 в С4, Excel также понимает формулу “как содержимое ячейки, расположенной тремя рядами выше, прибавить к содержимому ячейки, расположенной двумя рядами выше”. Таким образом, формула в ячейке С4 примет вид =С1+С2.
Если при копировании формул нужно сохранить ссылку на конкретную ячейку или область, то необходимо воспользоваться абсолютной адресацией. Для ее задания необходимо перед именем столбца и номером строки ввести символ $. Например, $B$4 или $C$2:$F$4 и т.д. (нажимают F4). при копировании адрес ячейки не изменяется.
Смешанная адресация. Символ $ ставится только там, где он необходим. Например, B$4 при копировании изменяется только имя столбца, а строка не изменяется.
$C2. при копировании изменяется только номер строки, а столбец не изменяется.
Тогда при копировании один параметр адреса изменяется, а другой нет.
КОГДА электронную таблицу Excel используют для работы с базами данных её называют списком или базой данных Excel (рис. ) и используют следующую терминологию:
Список (база данных Excel) – электронная таблица, в которой строки (записи) имеют фиксированную структуру, а имена столбцов (полей) – Заголовки занимают верхнюю строку.
Название столбца может занимать только одну ячейку и при работе с таблицей как с базой данных называется именем поля (название заголовка). Все ячейки строки с именами полей образуют область имен полей, которая занимает только одну строку.
Данные всегда располагаются, начиная со следующей строки после области имен полей. Весь блок ячеек с данными называют областью данных.
Рис. Структурные элементы списка (базы данных Excel)
1. Список содержит фиксированное количество полей (столбцов), определяющих структуру записи базы данных (строки).
2. Верхняя строка списка содержит имена полей (названия столбцов).
3. Имя поля может состоять из нескольких слов любого алфавита.
Обязательное требование — размещение в одной ячейке.
Для размещения имени поля списка2* в одной ячейке (Дважды отмечалось!) (рис. ) необходимо:
установить следующие параметры:
Выравнивание:
Отображение:
Для того чтобы электронная таблица воспринималась системой как список, необходимо соблюдать описанные выше правила и перед выполнением операций обработки установить курсор внутри этой таблицы. В этом случае при вводе команды обработки весь список будет выделен темным цветом.
Когда список сформирован неверно или нужно работать с частью области списка, область списка надо выделить вручную с помощью мыши.
Excel предоставляет возможности для работы с базами данных различных форматов, которые при открытии в среде Excel автоматически преобразуются в список. Такое преобразование называют импортом.
Данные в электронную таблицу можно включить не только путем импорта из «чужой» базы данных, но и посредством запросов данных, адресованных тому или иному серверу баз данных. Такие запросы формируются специальной программой MS Query, вызываемой по команде Данные, Внешние данные, Создать запрос. Результат запроса возвращается в электронную таблицу в виде списка.
Для упрощения ввода данных и автоматической проверки их правильности выполнить следующее:
Откроется диалоговое окно Проверка вводимых значений.
На вкладке Параметры указывается тип и интервал значений, которые разрешается вводить. Выбор типа данных вводимых значений в списке Тип данных позволяет определить, какие условия можно накладывать на значения ячеек.
Если для определения допустимых значений требуется ввести формулу, выражение или ссылку на вычисления в другой ячейке, выбрать в списке строку Другой.
Вкладка Сообщение для ввода позволяет создать подсказку пользователю о том, какие данные можно вводить.
На вкладке Сообщения об ошибке указывается сообщение, которое будет появляться на экране при вводе некорректных данных.
Сортировка данных является базовой операцией любой таблицы и выполняется командой Данные - Сортировка и фильтр – Настраиваемая сортировка с установкой необходимых параметров. Целью сортировки является упорядочивание данных. Сортировка осуществляется на том же листе.
В среде Excel 2007 предусмотрены несколько уровней сортировки: Добавить уровень
№ РЕЙСА – ПО ВОЗРАСТАНИЮ, затем число взросл бил по возраст
Выражение «ПО ПЕРВОМУ КЛЮЧУ» означает, что нужно создать свой собственный список, согласно задания (см. Сортировка по спискам).
Особенно важно осуществлять сортировку в списке, так как многие операции группировки данных, которые доступны из меню Данные, можно использовать только после проведения операции сортировки.
класс полёта по убыванию, а внутри полученной группы
дата вылета по возрастанию, стоим бил по возраст
Кнопка Microsoft Office , - Параметры Excel, - Основные, (группе Основные параметры работы с Excel) нажмите кнопку Изменить списки.
В окне Элементы списка набираем:
Кнопка Добавить
Получаем список
ОК!
Выд. любую ячейку табл.
Сортировка и фильтр Настраиваемый список
ОК! В ДО Списки выбираем список: 117, 673,45 – ОК!
Снова появляется ДО Сортировка
ОК!
Получим таблицу
|
№ рейса |
Дата вылета |
Цена билета, руб |
Число взрослых билетов |
Число детских билетов |
Класс полета |
Стоимость билетов, руб. |
|
117 |
01.02.2005 |
15020,00 |
5 |
0 |
Бизнес |
75100 |
|
117 |
20.07.2005 |
12500,00 |
37 |
35 |
Эконом |
900000 |
|
117 |
18.09.2005 |
9146,43 |
7 |
4 |
Эконом |
100610,73 |
|
117 |
16.02.2005 |
8247,14 |
15 |
7 |
Эконом |
181437,08 |
|
673 |
01.02.2005 |
17400,00 |
3 |
0 |
Бизнес |
52200 |
|
673 |
12.03.2005 |
8325,00 |
25 |
0 |
Эконом |
208125 |
|
673 |
16.07.2005 |
7347,86 |
20 |
12 |
Эконом |
235131,52 |
|
45 |
20.07.2005 |
17110,00 |
28 |
5 |
Эконом |
564630 |
|
45 |
12.03.2005 |
14200,00 |
11 |
1 |
Эконом |
170400 |
|
45 |
22.11.2005 |
10945,00 |
4 |
1 |
Бизнес |
54725 |
|
45 |
21.04.2005 |
10045,71 |
14 |
0 |
Бизнес |
140639,94 |
В Excel предусмотрено мощное, удобное и в то же время простое средство для облегчения ввода в базу данных — форма ввода данных.
Для того чтобы воспользоваться формой данных:
На панель быстрого доступа вывести кнопку по команде:
Кнопка Office – Параметры Excel – Настройка – Все команды
Задание 1. Для каждого рейса определить:
общую стоимость билетов и
максимальную цену билета
Всегда вначале следует отсортировать таблицу.
Согласно условия данного задания – “Для каждого рейса” необходимо отсортировать по столбцу № рейса
|
№ рейса |
Дата вылета |
Цена билета, руб |
Число взрослых билетов |
Число детских билетов |
Класс полета |
Стоимость билетов, руб. |
|
45 |
22.11.2005 |
10945,00 |
4 |
1 |
Бизнес |
54725 |
|
45 |
12.03.2005 |
14200,00 |
11 |
1 |
Эконом |
170400 |
|
45 |
21.04.2005 |
10045,71 |
14 |
0 |
Бизнес |
140639,94 |
|
45 |
20.07.2005 |
17110,00 |
28 |
5 |
Эконом |
564630 |
|
117 |
01.02.2005 |
15020,00 |
5 |
0 |
Бизнес |
75100 |
|
117 |
18.09.2005 |
9146,43 |
7 |
4 |
Эконом |
100610,73 |
|
117 |
16.02.2005 |
8247,14 |
15 |
7 |
Эконом |
181437,08 |
|
117 |
20.07.2005 |
12500,00 |
37 |
35 |
Эконом |
900000 |
|
673 |
01.02.2005 |
17400,00 |
3 |
0 |
Бизнес |
52200 |
|
673 |
16.07.2005 |
7347,86 |
20 |
12 |
Эконом |
235131,52 |
|
673 |
12.03.2005 |
8325,00 |
25 |
0 |
Эконом |
208125 |
При каждом изменении в - № рейса
Операция – Сумма
Добавить итоги по – Стоимость билетов, руб.
ОК!
Снова повторим команду
Получили таблицу с промежуточными итогами согласно условия задания.
Задание 2. Для каждой даты найти количество производимых рейсов, число взрослых и детских билетов
|
№ рейса |
Дата вылета |
Цена билета, руб |
Число взрослых билетов |
Число детских билетов |
Класс полета |
Стоимость билетов, руб. |
|
117 |
01.02.2005 |
15020,00 |
5 |
0 |
Бизнес |
75100 |
|
673 |
01.02.2005 |
17400,00 |
3 |
0 |
Бизнес |
52200 |
|
117 |
16.02.2005 |
8247,14 |
15 |
7 |
Эконом |
181437,08 |
|
45 |
12.03.2005 |
14200,00 |
11 |
1 |
Эконом |
170400 |
|
673 |
12.03.2005 |
8325,00 |
25 |
0 |
Эконом |
208125 |
|
45 |
21.04.2005 |
10045,71 |
14 |
0 |
Бизнес |
140639,94 |
|
673 |
16.07.2005 |
7347,86 |
20 |
12 |
Эконом |
235131,52 |
|
45 |
20.07.2005 |
17110,00 |
28 |
5 |
Эконом |
564630 |
|
117 |
20.07.2005 |
12500,00 |
37 |
35 |
Эконом |
900000 |
|
117 |
18.09.2005 |
9146,43 |
7 |
4 |
Эконом |
100610,73 |
|
45 |
22.11.2005 |
10945,00 |
4 |
1 |
Бизнес |
54725 |
А. позволяет произвести фильтрацию данных в списке. А. производит скрытие строк, не удовлетворяющих условию фильтрации.
Выполняется командами: Данные – Фильтр – Числовые фильтры – Настраиваемый фильтр.
Откроется диалоговое окно Пользовательский автофильтр
Рис. . Диалоговое окно Пользовательский автофильтр
|
Способ сравнения |
Описание |
|
равно (equals) |
Проверка на равенство заданному значению |
|
не равно (does not equal) |
Проверка на неравенство заданному значению |
|
больше (is greater than) |
Проверка на то, что значение больше заданного |
|
больше или равно (is greater than or equal to) |
Проверка на то, что значение больше или равно заданному |
|
меньше (is less than) |
Проверка на то, что значение меньше заданного |
|
меньше или равно (is less than or equal to) |
Проверка на то, что значение меньше или равно заданному |
|
начинается с (begins with) |
Проверка на то, что текст начинается с заданного фрагмента |
|
не начинается с [does not begin with) |
Проверка на то, что текст не начинается с заданного фрагмента |
|
заканчивается на (ends with) |
Проверка на то, что текст заканчивается заданным фрагментом |
|
не заканчивается на (does not end with) |
Проверка на то, что текст не заканчивается заданным фрагментом |
|
содержит (contains) |
Проверка на то, что текст содержит заданный фрагмент |
|
не содержит (does riot contain) |
Проверка на то, что текст не содержит заданный фрагмент |
Расширенный фильтр позволяет произвести гораздо более сложный поиск/фильтрацию, чем Форма данных или автофильтр
Основной особенностью, отличающей расширенный фильтр является необходимость во вспомогательном списке, содержащем критерий3*.
1. По условию задания создаётся критерий для поиска (диапазон условий) (рис.). Это делается либо правее исходной табл., либо ниже.
|
Вар 10 |
||||||
|
Ведомость цен на продукцию предр "ЗДОРОВЬЕ" |
||||||
|
Категория |
Назв товара |
Дата выпуска |
Цена сырья, руб. |
Цена упаковки, руб. |
Количество товара |
Стоимость партии, руб. |
|
Сбор |
"Нестарин" |
март |
20,0 |
4,0 |
325 |
7800,0 |
|
Сироп |
"Ягодка" |
май |
25,0 |
5,0 |
841 |
25230,0 |
|
Бальзам |
"Желудочный" |
июль |
30,0 |
6,0 |
1357 |
48852,0 |
|
Сироп |
"Витаминный" |
апрель |
41,0 |
7,0 |
745 |
35760,0 |
|
Бальзам |
"Весенний" |
февраль |
52,0 |
7,0 |
746 |
44014,0 |
|
Сироп |
"Лесовичок" |
март |
31,0 |
7,0 |
524 |
19912,0 |
|
Сироп |
"Сказка" |
февраль |
10,0 |
6,0 |
745 |
11920,0 |
|
Сбор |
"Алиса" |
март |
8,0 |
5,0 |
352 |
4576,0 |
|
Сбор |
"Детский" |
апрель |
10,0 |
5,0 |
215 |
3225,0 |
|
Сбор |
"Золушка" |
март |
12,0 |
2,0 |
451 |
6314,0 |
|
Бальзам |
"Укрепляющий" |
апрель |
14,0 |
7,0 |
216 |
4536,0 |
Порядок выполнения лабораторной работы
Сводная таблица на существующем листе
Задание 1.3. Изменить:
Категорию «Бальзам» на «Травяной настой».
Создаём Сводную таблицу на новый лист
Задание 1.5. Сводная таблица 3:
Страница – Дата выпуска;
Строка – Сбор;
Данные – Количество товара.
Есть 12 файлов почти одинаковых по структуре, из них нужно сделать один сводный, еще что бы и при внесении изменений в один из файлов, это отображалось в сводном.
Это можно сделать с помощью Консолидации данных. Для этого Вам необходимо, что бы все таблицы были сделаны по одному макету, допускается только перестановка местами колонок, которые будут суммироваться, но их названия должны быть идентичны во всех таблицах. Файлы, в которых содержатся исходные таблицы необходимо предварительно открыть.
В новой книге, которая будет содержать консолидированные данные устанавливаем курсор в ячейку А1 и на вкладке Данные в группе Работа с данными выбираем команду Консолидация.
В открывшемся диалоговом окне Консолидация из выпадающего списка Функции выбираем вид вычислений, которые должны проводиться с данными (например, суммирование).
Установив курсор в поле Ссылка на вкладке Вид с помощью кнопки Перейти в другое окно открываем поочередно необходимые файлы и выделяем в них диапазон данных (вместе с шапкой), которые будем консолидировать.
В поле Ссылка появится ссылка на файл и диапазон ячеек. Нажмите кнопку Добавить, для добавления данного диапазона ячеек в Список диапазонов.
После добавления всех необходимых данных установите флажок подписи верхней строки и значение левого столбца. Первый флажок автоматически создаст шапку для консолидированной таблицы, а второй обеспечит выполнение выбранной вами функции по каждому уникальному значению крайнего левого столбца.
Установление флажка в поле Создавать связи с исходными данными позволит обновляться данным автоматически при внесении изменений в исходный документ. Но обновляются данные только в рамках выбранного диапазона, т.е. если вы дополните таблицу новыми значениями, которые не входят в диапазон, выбранный во время консолидации, то в консолидированной таблице они не будут отображаться.
После указания всех данных нажимаем кнопку ОК.
В консолидированной таблице данные группируются по уникальным значениям крайнего левого столбца выбранных вами диапазонов в разрезе выбранных файлов (листов), что дает возможность просмотреть, как итоговую сумму (в нашем примере) так и значения по каждой категории в разрезе магазинов.
Консолидация данных
Под консолидацией в Excel понимается обобщение однородных данных из разных источников. При консолидации на основе значений нескольких ячеек формируется значение результирующей ячейки путем суммирования, нахождения среднего и т. д.
Ячейки, содержащие результат консолидации, могут быть связаны с исходными данными. В таком случае они будут обновляться при изменении исходных ячеек и таким образом отражать текущее положение дел. Однако, если необходимо зафиксировать положение дел только в один момент времени, консолидированные данные можно сделать оторванными от исходных. В таком случае итоговые ячейки не будут зависеть от исходных.
В Excel предусмотрено два основных метода консолидации данных:
Они различаются по способу, которым задается связь исходных ячеек с итоговыми, При консолидации по физическому расположению указываются номера ячеек, в которых содержатся исходные данные. При консолидации по заголовкам используются заголовки строк и столбцов. Консолидируются данные, расположенные в строках и столбцах с одинаковыми названиями.
При консолидации по физическому расположению указывается только местоположение ячеек, содержащих исходные данные. В этом случае во всех исходных диапазонах данные должны быть расположены в одинаковом порядке. Если хотя бы в одном из исходных диапазонов порядок данных изменен, то консолидация по физическому расположению уже даст неверные результаты.
Консолидация по заголовкам более удобна в использовании, чем консолидация по физическому расположению. Дело в том, что в первом случае исходные ячейки идентифицируются не по номеру, а по заголовкам, поэтому в разных исходных листах они могут быть расположены по-разному, т. е. может использоваться разный порядок строк и столбцов. Некоторые исходные листы даже могут содержать столбцы или строки, которые отсутствуют в других листах.
При консолидации по заголовкам происходит следующее. Для каждой ячейки диапазона консолидации определяются названия строки и столбца, в которых она расположена. Далее в каждом исходном диапазоне ищется ячейка, находящаяся в строке и столбце с такими же названиями. Отобранные таким образом ячейки становятся исходными для данной ячейки. Если названия строк не определены, но определены названия столбцов, то сами столбцы консолидируются по имени, а ячейки в столбцах консолидируются по расположению.
Чтобы осуществить консолидацию по заголовкам:
Функция, Ссылка, Список диапазонов, Подписи верхней строки, Значения левого столбца, Создавать связи с исходными данными
Рис.. К1
Связи консолидированных данных и исходных данных уст. если они помещены на разных листах.
Таблица 29.1. Функции консолидации Excel
|
Функция |
Описание |
|
Сумма (Sum) |
Вычисление суммы значений исходных ячеек |
|
Количество значений (Count) |
Вычисление количества непустых исходных ячеек |
|
Среднее (Average) |
Вычисление среднего значения исходных ячеек |
|
Максимум (Мах) |
Определение максимального среди значений исходных ячеек |
|
Минимум (Min) |
Определение минимального среди значений исходных ячеек |
|
Произведение (Product) |
Вычисление произведения значений исходных ячеек |
|
Количество чисел {Count Nums) |
Вычисление количества исходных ячеек, содержащих числа |
|
Смещенное отклонение (StdDev) |
Оценка стандартного отклонения генеральной совокупности в предположении, что в исходных ячейках находится только выборка из генеральной совокупности |
|
Несмещенное отклонение (StdDevp) |
Вычисление стандартного отклонения генеральной совокупности, которая находится в исходных ячейках |
|
Смещенная дисперсия (Var) |
Оценка дисперсии генеральной совокупности по выборке |
|
Несмещенная дисперсия (Varp) |
Вычисление дисперсии генеральной совокупности |
Исходные диапазоны могут быть разных размеров. В этом случае одни ячейки будут иметь больше исходных ячеек, чем другие. Например, если указаны два исходных диапазона B2:D4 и B6:Е8, то диапазон консолидации будет содержать три строки и четыре столбца. Значения первых трех ячеек каждой строки будут сформированы на основе данных из обоих диапазонов, значения последних ячеек каждой строки будут сформированы на основе ячеек столбца E второго диапазона.
Простую консолидацию можно провести без использования команды Консолидация (Consolidate) или формул со ссылками. Если необходимо посчитать сумму, разность, произведение или частное каждой пары ячеек из двух диапазонов, используйте команду меню Правка | Специальная вставка (Edit Paste Special).
Скопируйте один из исходных диапазонов в буфер обмена. Для этого выделите его и выполните команду меню Правка | Копировать (Edit Copy).
Далее:
Необходимо учитывать, что при использовании команды Специальная вставка
(Paste Special) содержимое второго исходного диапазона теряется — оно заменяется на результат. Если необходимо, чтобы содержимое обоих исходных диапазонов сохранилось, используйте другие способы консолидации.
С помощью команды Специальная вставка (Paste Special) можно консолидировать не только значения ячеек, но и формулы, которые хранятся в ячейках. Для этого в окне Специальная вставка (Paste Special) установите переключатель Вставить (Paste) в положение формулы (Formulas). В результате после вставки формулы в каждой из ячеек будут заменены на сумму, разность, произведение или частное формул, содержащихся в исходных ячейках. Например, если одна ячейка содержит формулу =В2^2, а другая — формулу =корень(В2), то в результате получится формула ^В2Л2+КОРЕНЬ(В2) (если переключатель Операция (Operation) в окне Специальная вставка (Paste Special) установлен в положение сложить (Add)).
|
Магазин "Сервис" |
Магазин "Мастер" |
|||||
|
Товар |
Количество |
Стоимость, руб |
Товар |
Количество |
Стоимость, руб |
|
|
Линолеум |
1500 |
420000 |
Линолеум |
1250 |
350000 |
|
|
Паркет |
1000 |
430000 |
Паркет |
900 |
387000 |
|
|
Грунтовка |
750 |
222000 |
Грунтовка |
750 |
222000 |
|
|
Шпатлевка |
149 |
83440 |
Шпатлевка |
200 |
112000 |
|
|
Штукатурка |
440 |
90640 |
Штукатурка |
700 |
144200 |
|
|
Итого: |
1246080 |
Итого: |
1215200 |
|||
|
Магазин "Стройка" |
||||||
|
Товар |
Количество |
Стоимость, руб |
||||
|
Линолеум |
1000 |
280000 |
||||
|
Паркет |
850 |
365500 |
||||
|
Грунтовка |
600 |
177600 |
||||
|
Шпатлевка |
189 |
105840 |
||||
|
Штукатурка |
800 |
164800 |
||||
|
Итого: |
1093740 |
Рис1. Лист MS Excel с таблицами данных о продаже строительных материалов
|
Количество |
Стоимость, руб |
|
|
Линолеум |
3750 |
1050000 |
|
Паркет |
2750 |
1182500 |
|
Грунтовка |
2100 |
621600 |
|
Шпатлевка |
538 |
301280 |
|
Штукатурка |
1940 |
399640 |
|
Итого: |
3555020 |
а). Выделяем ячейку где поместится левый верхний угол таблицы суммарных значений о продаже строительных материалов (рис. 1).
б). Команды: Данные-Консолидация. В ДО Консолидация выбираем: Функция-сумма.
в). В поле Ссылка-Консолидация ссылка через кнопку Добавить вводим диапазоны данных (3 таблицы с данными о продаже строительных материалов).
г). Флажки Создавать связи с исходными данными и значения левого столбца сбрасываем.
д). Устанавливаем флажок: подписи верхней строки
е). ОК!
ж). Снова ДанныеКонсолидация
з). Устанавливаем второй флажок: значения левого столбца
е). ОК!
Команды: Данные-Консолидация – Максимум.
Через кнопку Добавить вводим диапазоны данных (3 таблицы с данными о продаже строительных материалов).
Устанавливаем флажок: подписи верхней строки ОК!
Снова ДанныеКонсолидация. Устанавливаем второй флажок: значения левого столбца. ОК!
Опять ДанныеКонсолидация. Устанавливаем флажок Создавать связи с исходными данными.
|
Количество |
Стоимость, руб |
|
|
Линолеум |
1500 |
420000 |
|
Паркет |
1000 |
430000 |
|
Грунтовка |
750 |
222000 |
|
Шпатлевка |
200 |
112000 |
|
Штукатурка |
800 |
164800 |
|
Итого: |
1246080 |
|
Магазин "Сервис" |
Магазин "Мастер" |
Магазин "Стройка" |
|||
|
Линолеум |
Лин. Синтерос |
Линолеум |
Лин. Таркетт |
Линолеум |
Лин. Ютекс |
|
Шпатлевка |
Шп. KNAUF |
Шпатлевка |
Шп. ПВА |
Шпатлевка |
Шп. базовая |
|
Штукатурка |
Шт. Старатели |
Штукатурка |
Шт. Ротбанд |
Штукатурка |
Гипсовая |
|
Количество |
Кол-во |
Стоимость, руб |
Ст-ть, руб |
Количество |
Кол-во, ед |
|
Товар |
Кол-во, ед. |
Стоим., руб |
|
Линолеум (в ассортименте) |
1250 |
350000,00 |
|
Паркет |
917 |
394166,67 |
|
Грунтовка |
700 |
207200,00 |
|
Шпатлевка (в ассортименте) |
179 |
100426,67 |
|
Штукатурка (в ассортименте) |
647 |
133213,33 |
|
Итого: |
1185006,67 |
В Excel имеются два мощных средства для анализа данных: Подбор параметра и Поиск решения.
Подбор параметра служит для вычисления величины, обеспечивающей определенное значение формулы.
Поиск решения позволяет найти величину, наилучшую среди множества возможных, или решение, оптимальное в некотором смысле при заданных ограничениях.
Здесь рассмотрены следующие темы:
В Excel имеется множество инструментов для решения разнообразных задач. Средства Поиск решения и Подбор параметра предназначены для вычисления входных значений, влияющих на результаты формул.
Подбор Используется для подгонки значения величины, вычисляемой по заданной параметра формуле, путем подбора одного влияющего на эту величину значения
Поиск Используется для поиска оптимального решения некоторой задачи в решения случае, если имеется несколько входных значений, а также набор ограничений на решение. Диспетчер сценариев может запомнить несколько решений, найденных данным средством, и сгенерировать на этой основе отчет
Если известно, какой результат необходимо получить, но не известен аргумент, при котором достигается это решение, пользуйтесь командой Сервис, Подбор параметра. Достаточно указать формулу, ее значение и изменяемую ячейку, влияющую на эту формулу. Путем последовательных итераций Excel найдет ответ.
С помощью этой команды можно определить, например, необходимый для получения заданной прибыли рост производства или объем продаж, обеспечивающий окупаемость.
Изменяемая ячейка должна содержать значение (не формулу) и должна влиять на результат, который требуется получить. Это влияние не обязано быть непосредственным: ячейка может не использоваться в формуле как аргумент. Если описываемым способом ответ получить нельзя, можно попытаться его найти с помощью Диспетчера сценариев, таблицы подстановок или надстройки Поиск решения.
На рис. 1.1 приведен пример простого прогноза объема продаж, расходов и чистой прибыли. Изменяемыми исходными данными являются коэффициенты варьирования объема продаж в строке 16 и относительные затраты в ячейках В18:В20. Коэффициенты варьирования используются для прогноза объема продаж, а величины относительных затрат — для оценки затрат.
Предположим, что необходимо узнать коэффициент варьирования для 1998 года (ячейка D16), который обеспечит в 2000 году чистую прибыль в $3000 (ячейка F12). Воспользуемся для этого средством Подбор параметра.
Если на вкладке Вычисления диалогового окна Параметры установлен флажок Точность как на экране, то параметр, возможно, подобрать не удастся, даже если решение существует. При использовании средства Подбор параметра этот флажок должен быть снят.
Чтобы воспользоваться средством Подбор параметра:
1. Выделите ячейку с формулой, которую вы хотите подогнать под заданное значение. В данном примере — это ячейка F12.
2. Выберите команду Сервис, Подбор параметра. Появится диалоговое окно Подбор параметра (рис. 1.1). В поле Установить в ячейке уже будет находиться ссылка на выделенную на шаге 1 ячейку.
3. В поле Значение введите величину, которую необходимо получить. В данном примере — это 3000.
4. В поле Изменяя значение ячейки введите ссылку на исходную ячейку. Эта ячейка должна влиять на выбранную на шаге 1 формулу. В данном примере исходной ячейкой является D16. Она определяет результаты формул в ячейках строки 5 и тем самым неявно влияет на формулу в ячейке F12.
5. Нажмите кнопку ОК.
Средство Подбор параметра начнет итерационный процесс поиска решения. Каждый шаг этого процесса дает следующее приближение к искомой величине.
6. Если необходимо приостановить или отменить затянувшийся процесс подбора параметра, нажмите в открывшемся диалоговом окне Результат подбора параметра кнопку Пауза или Отмена. После нажатия кнопки Пауза можно выполнять процесс поиска по шагам. Для этого используется кнопка Шаг. Для возобновления автоматического поиска нажмите кнопку Продолжить.
Как уже отмечалось выше, ячейка, выбранная на шаге 4, должна влиять на формулу, указанную в поле Установить в ячейке, и сама не должна содержать формулу. С помощью команды Правка, Перейти, Выделить можно выделить все ячейки, влияющие на заданную.
После того как решение найдено, нажмите кнопку ОК, чтобы заменить значения на рабочем листе новыми значениями, или нажмите кнопку Отмена, чтобы сохранить прежние величины.
Часть I
Процедура подбора параметра в MS Excel используется для подгонки под заданное значение величины, вычисляемой по формуле, введенной в ячейку рабочего листа. В результате данного процесса изменяется значение числа в ячейке, влияющей на результаты вычислений. При этом изменяемая ячейка обязательно должна содержать числовое значение, а не формулу.
Например, в ячейку A2 введено число 5, в ячейку A3 – формула =A1+A2.
Требуется определить значение числа в ячейке A1, при котором результатом вычислений по формуле будет число 25.
Необходимо выполнить действия Данные – Анализ что-если Подбор параметра, затем в появившемся диалоговом окне Подбор параметра указать: адрес ячейки, в которой будет получен результат (A3); планируемое значение результата (25); адрес ячейки с изменяемым значением (A1). После нажатия кнопки <OK> ответ будет получен путем последовательных итераций (приближений). Каждый шаг этого процесса даст приближение к искомой величине.
Если ячейка с изменяемым содержанием не является пустой, находящееся в ней число можно заменить новым рассчитанным значением.
Имеется информация о наращении сумм и выплате процентов в потребительском кредите, выданном банком при простых кредитных ставках:
|
Первоначальная сумма ссуды, руб. |
Срок кредита, лет |
Ставка наращения |
Наращенная сумма долга, руб. |
Число платежей в году |
Величина разового погасительного платежа, руб. |
|
100000 |
5 |
15% |
4 |
Наращенная сумма долга вычисляется по формуле , величина разового погасительного платежа – по формуле , где – первоначальная сумма ссуды; – срок кредита; – ставка наращения; – число платежей в году.
Получаем следующую таблицу:
Установка пароля на открытие книги Ecxel 2007, и его отмена
http://www.infowall.ru/index.php?menu_id=33&art_id=432
Данные, хранимые в документах Excel, очень часто представляют собой конфиденциальную информацию, например, о доходах или налоговых отчислениях компании. Чтобы скрыть ее от посторонних глаз и защитить от утечки, в Excel на открытие книги можно установить пароль.
Установка пароля на книгу в Excel 2007
Введите пароль в соответствующее поле и нажмите «ОК».
Теперь при открытии файла, Excel будет запрашивать пароль и только в случае правильного ответа откроет документ.
Снятие пароля в Excel 2007
Электронные таблицы Excel 2007 1
Основные понятия 1
Элементы окна программы Excel 2007 3
Строка состояния 8
Добавление листа (4 способа): 9
Контекстное меню 9
Объединение клеток 9
Ввод текста 10
Перенос текста 11
Ввод чисел 11
Выравнивание 12
Ввода заголовка к таблице 13
Подгон высоты строки и ширины столбца под текст находящийся в ячейке 13
Изменение выравнивания данных в активной ячейке 13
Изменение направления текста 13
Форматы числовых данных 14
Выделение строки 15
Выделение ячеек 15
Изменение ширины (высоты) столбца (строки 15
Ввод формулы 16
Изменение параметров страницы 16
Вставка строк – столбцов 17
Объединение смежных листов 17
Объединение несмежных листов 17
Разъединение объединенных листов 18
Переименование листа 18
Cкрытие трок и cтолбцов 18
Изменение точности чисел 18
Подсчёт итогов 19
Ввод формул 23
Использование функций 23
Завершение ввода формулы выполняется 3 способами: 23
Копирование не самой формулы, а её результата 25
Редактирование данных 25
Удаление содержимого текущей ячейки 26
Показывать формулы вместо их значений 26
Добавление, редактирование и удаление примечания 27
Изменение формата ссылок на ячейки в формулах Excel 29
Автозаполнение 29
Автозаполнение списка сотрудников 30
Автозаполнение элементами списка Excel 30
Группировка объектов (рис.): 32
Автоматическое форматирование 32
Выбор стиля таблицы при ее создании 33
Применение стиля таблицы к существующей таблице 33
Создание и удаление пользовательского стиля таблицы 34
Создание пользовательского стиля таблицы 34
Удаление пользовательского стиля таблицы 35
Удаление стиля таблицы 35
Изменение параметров форматирования стиля 36
Создание и удаление таблицы Excel 37
Удаление таблицы 37
Создание колонтитулов 38
Построение диаграмм 41
Рисование рамок 49
Поместить рисунок в рамку 51
Способы адресации в Excel 51
Понятие о списке (базе данных Excel) Макарова Практикум (с.151) 52
Проверка вводимых значений 54
Сортировка данных 58
Сортировка по спискам 61
Форма ввода данных 65
Подведение промежуточных итогов 67
Отменить итоги – кнопка Убрать всё 69
Автофильтр 72
Пользовательский автофильтр 73
Способы сравнения в пользовательском автофильтре 74
Расширенный фильтр 74
Для того чтобы применить расширенный фильтр: 74
Расширенный фильтр. Вариант 10. Пример 76
Сводные таблицы 85
Консолидация данных в Excel 2007 98
Консолидация по физическому расположению 101
Консолидация по заголовкам строк и столбцов 101
Консолидация при помощи команды СПЕЦИАЛЬНАЯ ВСТАВКА 103
ЛАБОРАТОРНАЯ РАБОТА № 8 105
Консолидация данных (с пояснениями) 105
Подбор параметра и Поиск решения 112
Средства Excel 112
Использование средства Подбор параметра 112
Поиск единственного решения 113
ЛАБОРАТОРНАЯ РАБОТА № 9 116
(С пояснениями) 116
Подбор параметра и поиск решения в MS Excel 116
Задание 117
Защита книги Excel 121
Оглавление 124
2* имя поля списка – заголовок столбца
3* Это несколько замедляет процесс фильтрации, однако позволяет задавать очень сложные критерии, которые недоступны при использовании формы ввода данных или автофильтра.
1