Книга файл-таблица из рабочих листов
| Вид материала | Книга |
- Урок природоведения на тему: "Планета заболела", 67.45kb.
- Лексикология для высших учебных заведений1[1], 2831.98kb.
- Це сукупність даних однакового типу, 151.29kb.
- Сводная таблица классов условий труда, установленных по результатам аттестации рабочих, 54.05kb.
- Сводная таблица классов условий труда, установленных по результатам аттестации рабочих, 54.21kb.
- Кнопки на боковой панели "Монитора", 327.09kb.
- Книга первая, 2663.55kb.
- Книга «Уши машут ослом. Современное социальное программирование», 2700.45kb.
- В. Л. Большаков книга веков история мира в синхронистической таблице челябинск, 2005, 2756.1kb.
- Идея программы 3 Осистеме fat 4 Структура системы файлов fat 5,6, 155.03kb.
Табличный процессор Excel - 97.
(Excel является частью программного комплекса MS Office).
В нём применяется стандартизированная оболочка, единая для всех приложений Windows, двадцать единообразных кнопок и общая структура диалоговых окон.
Диспетчер файлов такой же, как и в Microsoft Word. Поэтому этот пункт см. в описании по редактору Word.
1. Основные понятия:
Таблицы – один из основных видов изображения информации с помощью столбцов и строк, с которыми необходимо выполнять всевозможную вычислительную работу.
Файл в Excel называется Книгой.
Рабочая книга – файл-таблица из рабочих листов (максимальное их количество равно 16-ти), листов диаграмм и макросов.
Стандартное расширение файла книги – XLS.
Книга состоит из Листов, которые перечислены в нижней строке таблицы.
Максимально в листе можно заполнить 256 столбцов и 16384 строки. Всего 4.194.304 ячейки. Это большой объем.
Сохраняя лист, Вы сохраняете текущее состояние всех листов книги!
Лист состоит из строк и столбцов. Строки обозначаются цифрами, а столбцы – латинскими буквами.
Поле между строками и столбцами – рабочее поле таблицы.
Ячейка - пересечение строки и столбца.
Активная ячейка – та, в которой расположен маркер мыши.
Влияющие ячейки – ссылки, на которые содержит формула в активной ячейке.
Зависимая ячейка – ячейка, формула в которой содержит ссылки на содержание активной ячейки.
Абсолютная ссылка на ячейку производится либо посредством задания ей «имени» или с помощью адреса ячейки - $C$11. При необходимости впереди указывается наименование листа + «:».
Область выделения – выделенный движением мыши без отрывания левой кнопки диапазон ячеек (несколько строк и/или/ столбцов). К ним могут быть применены единые команды. Области выделения могут быть смежными или несмежными.
Сетка – границы вокруг таблицы и между столбцами (строками).
Списки – области, которые содержат данные одного типа.
2. Листы:
Их имена показаны в нижней части экрана. Но показаны не все листы. Слева от ярлычков листов расположены четыре кнопки прокрутки листов.
Переименовать лист можно двойным щелчком мыши на имени листа или вызовом переименования из контекстного меню.
Из контекстном меню аналогично вызывается режим «Удаление». Программа предупреждает, что после удаления лист вернуть уже не удастся! Я рекомендую сначала скопировать содержимое листа в другое место (например, на другой лист); убедиться, что копирование прошло нормально, а затем спокойно удалить лист. Или сначала записать таблицу на винчестер, а затем произвести удаление листа. При обнаружении ошибки изменённую таблицу на диск не записывать (не обновлять).
Другие режимы контекстного меню на листе:
- вставка листа (перед активным листом);
- переместить (скопировать) – выбрать книгу и лист;
- выделить все листы;
- исходный текст – текст макроса (создается отдельно).
Лист можно скрыть при помощи команд: главное меню «Формат» – «Лист» – команда «Скрыть». Показ скрытого листа : «Формат» – «Лист» – «Показать»; выбрать лист из списка; нажать клавишу «ОК».
3. Линейки инструментов:
Линейки инструментов со своими кнопками позволяют быстрее выполнять режимы работы. Обеспечивается показ линеек инструментов на экране через меню “Вид”, пункт “Панели инструментов”.
Обычно, по умолчанию, установлены две основные панели:
- стандартная;
- форматирование.
При необходимости можно поместить на экран другие панели. Но соответственно уменьшится и окно работы с таблицей.
Подробное описание основных кнопок находится в лекции по Windows.
4. Поля управления данными таблицы
(расположены над и под полем таблицы):.
4.1. Поле ячейки.
Поле ячейки содержит имя активной ячейки.
Можно ввести в это поле имя ячейки, к которой нужно переместиться (например, АА45) и нажать кнопку
4.2. Строка формул.
Строка формул расположена над таблицей справа от поля ячейки и предназначена для ввода и корректировки формул и текста. Показывает формулу, относящуюся к активной ячейке. С текстом можно производить стандартные операции: вырезание, копирование и вставка текста.
Имейте в виду, что если текст находится в нескольких ячейках, то его содержимое является принадлежностью 1-й ячейки. Не пугайтесь этого явления!
4.3. Строка состояния.
Флаг на её наличие устанавливается из меню “Вид”, пункт “Строка состояния”. Находится в нижней части текущего листа. Содержит информацию о текущем режиме работы и краткое пояснение выполняемых действий.
4.4. Калькулятор.
Окно калькулятора находится посередине строки состояния.
Калькулятор позволяет показывать вычисления на основе активной ячейки или по выбранным ячейкам. Он помогает произвести предварительные вычисления перед тем, как иным способом записать их результаты в другие ячейки таблицы.
Если слева от поля калькулятора щелкнуть правой кнопкой мыши, то можно изменить режим расчетов (среднее; количество значений; количество чисел; максимальное; минимальное; сумма). Выбранный вариант обозначается знаком «v».
5. Управление режимами с помощью комбинаций клавиш:
Выделение всей таблицы – щелчок в верхнем левом углу или
Выделение строки -
Выделение столбца -
Выделение полей в столбце -
Перемещение в начало текста к первой ячейке листа -
Перемещение к первой ячейке текущего столбца -
Перемещение в конец текста к последней заполненной ячейке -
Перемещение к последней ячейке столбца -
Кнопка
Перемещение на следующий лист -
.
Перемещение на предыдущий лист -
.
Вырезать выбранный фрагмент -
Копирование выбранного фрагмента -
Вставка вырезанного (скопированного) фрагмента -
6. Ввод и редактирование информации:
6.1. Ввод:
Ввод данных осуществляется в активную ячейку.
Символы появляются в ячейке и строке состояния.
Завершение ввода – нажатием клавиши
При вводе длинного текста в ячейку он автоматически распространяется на соседние ячейки справа.
Какой бы разделитель при вводе Даты между Днем, Месяцем и Годом мы не вводили, он интерпретируется Excel в виде символа “.”.
Время вводится и высвечивается с разделителем в виде символа “:”.
6.2. Редактирование:
Переход в режим редактирования ячейки осуществляется двумя путями:
- два щелчка левой кнопкой мыши на выбранной ячейке;
- нажатием кнопки
.
Завершение редактирования только нажатием клавиши
6.3. Групповой ввод данных:
Очень удобный стиль ввода данных!
Выделить группу ячеек вместе с заглавиями. Должна быть выделены под заглавием все строки списка, включая последнюю строку, иначе добавление новых записей будет запрещено! Д.б. заполнена хотя бы одна ячейка выделенного списка. Иначе программа выдает сообщение с необходимостью давать дополнительные ответы.
Выбрать пункт главного меню «Данные» – команда «Форма».
Теперь построчно, начиная с первой строки, будут предлагаться ячейки на ввод. Нажать клавишу «Добавить». Появятся пустые поля. Перемещение вниз с поля на поле – клавиша
Завершение ввода в строку – клавиша
6.4. Исправление ошибок при вводе и редактировании:
Пока мы не завершили ввод изменений в активной ячейке кнопкой
- кнопка в виде красного креста в строке «Формул» отменяет все изменения в поле и закрывает режим редактирования;
- сочетание кнопок
+ - возвращает изменение одного символа;
- использование стандартного варианта с нажатием кнопки отмены действий (стрелка, изогнутая влево).
- использование стандартного варианта с нажатием кнопки отмены действий (стрелка, изогнутая влево).
6.5. Удаление:
Удаление осуществляется нажатием кнопки
При вызове режима удаления из контекстного меню у программы появятся вопросы о стиле перемещения остального текста справа и ниже удаляемого участка.
Чаще всего удобнее дать команду «Очистить содержимое ячеек».
6.6. Очистка выбранного диапазона через меню:
Пункт главного меню «Правка» – команда «Очистить».
В дополнительном меню надо выбрать один из вариантов:
- всё;
- форматы;
- содержимое;
- примечание.
Этот режим используется, когда нужно вместе с данными ячеек очистить их форматы и другие определения ячеек.
6.7. Автозаполнение.
При вводе информации, если Редактор находит совпадения по нескольким буквам с ранее введенными словами в данной таблице, он предлагает этот вариант. Его можно принять или продолжать вводить дальше. Тогда предлагаемый текст исчезнет.
6.8. Выбор из списка.
По мере ввода данных в столбец таблицы, Редактор запоминает их варианты и создает автоматически справочник - список. Очень удобно им пользоваться! Особенно при большом объеме таблицы! Нужно нажать правую кнопку мыши, из контекстного меню выбрать пункт «Выбрать из списка» и на нужном поле щелкнуть мышью. Оно появится в выбранной ячейке.
При нахождении в запомненной информации оригинального варианта, похожего на вводимый текст, Excel его предлагает. Но при вводе любого отличающегося символа предлагаемых вариант исчезает. Чтобы откорректировать часть отличающегося текста нужно тут же (до нажатия клавиши
Создание нового списка : меню “Сервис” – пункт “Параметры” – «Списки»; нажать на кнопку «Добавить» и построчно ввести список. или «Имена» - «Вставка» - «Имя» – «Присвоить» (создание списка на базе полей таблицы). Там же можно откорректировать существующие списки.
6.9. Поиск текста в таблице:
Меню “Правка” – пункт “Найти” (бинокль) и ввести нужное слово.
7. Оформление ячеек таблицы (формат ячеек):
Изменение ширины столбца осуществляется буксировкой правой грани обозначения столбца. В этом случае двунаправленная стрелка должна быть перечеркнута двойной чертой.
При выделении группы столбцов буксировка правой грани правого столбца изменяет ширину всех столбцов.
Аналогично можно изменить высоту строки (строк). И даже спрятать строку.
Большинство действий выполняются в режиме форматирования - меню «Формат», пункт «Ячейки» или из контекстного меню (чаще всего0.
Основной функцией является формат «Число» – вид представления данных. Можно установить «Общий» вид и тогда поле в ячейке сможет иметь и числовой и текстовый формат. Остальные форматы выбираются и присваиваются при мере необходимости.
В формате «Число» для случая с отрицательными числами можно выбрать формат для таких чисел (например, со знаком минус слева и красным цветом). Это удобно при расчетах, где отрицательные числа желательно выделить.
Если отметить флажок на поле «Разделитель разрядов», то тысячи будут разделяться пробелами. Это удобно при больших числах, как, например, наши недавние миллионные зарплаты!
Если в поле после изменения формата появляются решетки, не пугайтесь! Значит, нужно расширить столбец, так как отформатированные данные реагируют на размеры ячеек!
В формате «Денежная единица» при установке флажка «Денежная единица» после числа будет писаться символ «$». Это удобно только для тех, кто получает зарплату в СКВ! Шутка…
В формате «Дата» для работы лучше выбирать краткий формат даты с числами. А при выводе на печать использовать более подробные варианты с расшифровкой названий месяцев и т.д.
Аналогично используются функции «Выравнивание», «Шрифт» и другие.
Перенос текста осуществляется в окне «Выравнивание» – установить флажок на команде «Переносить по словам». Слово автоматически перенесется, если оно не помещается в ячейку. При этом строка или столбец расширится. Слово можно отредактировать с помощью символа «-», если нам не нравится автоматический перенос.
Здесь же можно изменить пространственную ориентацию текста (например, расположить его вертикально).
В формате «Шрифт» помимо возможностей, представляемых кнопками панелей инструментов, существуют дополнительные возможности по Подчеркиванию текста. А также есть некоторые эффекты. Но эти режимы редко используются…
В формате «Рамка» к каждой стороне рамки можно подобрать тип линии и цвет. Сначала нужно выбрать вариант рамки (вокруг и т.д.).
В формате «Защита» можно защитить выбранные ячейки от редактирования. Но перед этим нужно защитить весь лист. Пароль вводить не обязательно.
Копирование формата данных осуществляется с главной панели инструментов – кнопка «Метелка».
8. Объединение ячеек:
Необходимо выделить ячейки, которые хотите объединить, и нажать кнопку в виде буквы «а» со стрелками по бокам. Ячейки будут объединены, а текст выставлен по центру общей ячейки.
Объединяются данные из левого верхнего угла.
Объединение всех ячеек в столбце или строке производится через меню «Формат», пункт «Ячейки», флажок «Объединение».
Чтобы снять объединение : меню «Формат», пункт «Ячейки». Снять флажок «Объединение».
9. Ввод формул:
(Основной режим работы с таблицами !)
Каждая формула должна начинаться со знака «=».
Формула может содержать: числа, знаки арифметических операций, скобки, адреса ячеек, функции.
При нажатии кнопки
При наличии нескольких математических операций в одной формуле, Excel выполняет каждую операцию в порядке слева направо, руководствуясь приоритетом операций, принятым в математике.
Поля с формулами для удобства лучше выделять другим цветом. Эти поля потом можно защитить от изменений.
Пробелы в формуле применять нельзя.
В адресах ячеек используются только латинские буквы !
Чтобы поместить в формулу адрес ячейки, нужно по ней щелкнуть кнопкой мыши.
Чтобы ввести диапазон ячеек, нужно отбуксировать курсор мыши по диагонали. Обращайте внимание на сообщения об ошибках в циклических ссылках (расчеты с блоками данных).
При вводе текстовых значений, их нужно заключать в кавычки (“ ”).
Адрес ячейки называется Ссылкой.
Ссылка на ячейку другого листа называется Внешней. Её формат - =Имя листа!адрес ячейки.
Создание адреса внешней ссылки с помощью копирования:
- скопировать ячейку с первого листа в буфер обмена;
- на следующем листе в ячейке - месте вставки:
- контекстное меню;
- специальная вставка:
- признак «Все»;
- кнопка «Вставить ссылку».
Ссылка будет на фиксированную ячейку.
Показ зависимостей между ячейками:
Пункт главного меню «Сервис» – подпункт «Зависимости» – команда «Влияющие ячейки». Синими точками обозначены ячейки, откуда берутся данные. Стрелка направлена в сторону итоговой ячейки.
Чтобы снять показ ячеек, с том же списке выбрать команду «Убрать все стрелки».
Для проверки большого числа зависимостей там же нужно выбрать команду «Панель зависимостей»:
- 1-я кнопка – влияющие ячейки (каждый уровень с новым нажатием);
- 2-я кнопка – снимает показ уровней по очереди;
- 3-я кнопка – показывает на какие ячейки влияет данная ячейка;
- 4-я кнопка – отменяет действия 3-ей;
- 5-я кнопка – удаляет все стрелки.
Блок ячеек изображается в синей рамке. Стрелка одна – из первой ячейки блока в сторону результирующей.
Показ формул в ячейках:
Иногда нужно в ячейке видеть не данные, а формулу. Этот режим вызывается из пункта главного меню «параметры» – подменю «Вид». На строке «Формулы» установить флажок.
Работа с текстовыми значениями:
Основным символом, применяемым при операциях с текстом, является « & » - конкатенация – объединение полей. Текстовые наименования заключаются в двойные кавычки (“”). При применении имен полей двойные кавычки не нужны.
При выборе после знака «=» имени переменной, Excel показывает результат. Нужно нажать клавишу
Пример слияния строк при участии имен данных – стр_1 & “ ” & ячейка с числом & стр_2.
Функции Excel (мастер функций):
Но в Excel имеется еще множество других функций: арифметических, логических, финансовых и т.д. Свыше 200.
Они вызываются с помощью «Мастера функций» – кнопка «fx» на стандартной панели инструментов:
- строки – для выбора ячеек или ввода аргументов функций. Можно заносить ячейки выделением ячейки или блока ячеек.
- поле «Значение» – результат работы функции.
Функции могут включать друг друга. В Excel можно использовать до 7 уровней вложенности функций!
Форматы функций Excel:
Основная функция – СУММ(аргумент1,аргумент2,…) – суммирует аргументы. Содержит до 30-ти аргументов. Мастер функций сначала показывает два поля для ввода. При переходе на второе появляется третье и т.д.
Функции Excel по их видам в Мастере функций:
а) несколько недавно использующихся:
Чаще всего используем до 10 функций. Они находятся в этом перечне, и их из него брать наиболее удобно.
б) полный алфавитный перечень:
Если мы не знаем, к какому разделу относится искомая функция.
Но в общем списке ее искать не очень удобно.
в) финансовые функции:
Чаще всего используются в бухгалтерских расчетах.
г) математические функции:
Функция «Округление». Округл(Ячейка;количество знаков после запятой для округления).
Функция «Степень» - возведение в указанную степень. Степень (Ячейка; показатель степени).
Функция «Abs» - абсолютное значение числа. Abs(Ячейка).
д) статистические функции:
Функция «Ранг» - ранг числа в списке чисел. Ранг (Число; ссылка; порядок). Строковые поля не учитываются. Порядок: 0 – ложь – по убыванию; > 0 – истина – по возрастанию.
е) дата и время:
В операциях с датой (например, выделение в дате дней, месяцев и годов).
ж) текстовые функции:
Рубль(ячейка с числом; десятичные цифры) – для перевода числа с строку. Десятичные цифры – если параметр = 0, число будет в целом формате.
Фиксированный(ячейка с числом; десятичные цифры; без запятых) – преобразует число в текстовый формат. Десятичные цифры – если параметр = 0, число будет в целом формате; для процентов = 2. Параметр «без запятых» можно не указывать.
з) логические функции:
Если (логическое выражение; значение если истина; значение если ложь) – проверка выражения и выдача сообщений по условию (например, если С3 > С2, то истина – больше, ложь – меньше). Excel введенное слово автоматически берет в двойные кавычки, т.к. это текст. Истина и Ложь могу быть ячейками, именами и встроенными условиями.
Автосуммирование.
Нажимаем на знак «» (Автосуммирование) на ячейке, где хотим получить сумму.
Выделяем диапазон ячеек, которые надо просуммировать, и получаем результат, аналогичный выполнению функции «СУММ».
10. Размножение содержимого ячеек:
Размножение ячеек позволяет создать ряд данных – ряд значений, отличающихся на определенный шаг (числа, дни, недели и т.д.) или обладающих общими признаками.
Рамка выделенного блока имеет в нижнем правом углу специальный элемент – манипулятор размножения. Этот элемент можно буксировать в любом направлении.
Он выглядит в виде знака «+».
В зависимости от содержимого выделенного блока будет разным результат размножения:
- выделена одна ячейка с цифрой или символом – он скопируется на другие ячейки;
- два блока с цифрами 1 и 3 – далее будут идти по нарастающей 5, 7, 9 и т.д.;
- в ячейке месяц “январь” – далее будут остальные Месяцы. Также размножаются дни недели. Это касается названий для которых имеются заранее созданные списки;
- при наличии в ячейке даты – ниже появятся следующие даты.
С помощью клавиши и с цифры в ячейке можно создавать последовательность цифр с шагом равным единице.
При вводе полей вида «Счет 1» и «Счет 2» и их одновременном выделении Excel автоматически нарастит номера счетов при использовании манипулятора размножения.
Размножение содержимого ячеек через меню:
Полный спектр для размножения представляет пункт меню «Правка» – команда «Заполнить» -- подпункт «Прогрессия» после выбора ячейки или группы ячеек.
Есть несколько вариантов для обеспечения группировки:
а) направление прогрессии:
по строкам;
- по столбцам.
б) вид прогрессии:
- арифметическая;
- геометрическая;
- даты;
- Автозаполнение (программ сама предложит вариант).
в) отсчет дат (при выборе в пункте «б» варианта «Дата»:
- день;
- рабочий день (без учета праздничных, хотя и не совсем правильно);
- месяц;
- год.
Можно ввести определенный шаг для прогрессии и предельное значение (при вводе дат – поле типа «Дата»).
Необходимо учитывать, что бесконечную прогрессию Excel строить не станет (например, размножение в арифметической прогрессии цифры 1 без определения количества ячеек).
Режим «Автозаполнение» - Excel сама анализирует состав активной ячейки и строит прогрессию.
11. Простейшие операции над блоками:
Выделение блока – щелчок мышью, далее тянуть до нужного места и отпустить кнопку мыши. Область таблицы будет выделена.
Перемещение блока – буксировка блока за обрамляющую его рамку на новое место.
Копирование блока - аналогично, но с заранее нажатой клавишей
Эта технология называется Drag & Drop.
При выделении нескольких строк можно вставить такое количество пустых строк при выборе в контекстном меню команды «Вставить». Вставленные строки будут иметь ширину предыдущей строки перед выделенным участком.
Аналогично можно произвести вставку нескольких столбцов.
12. Работа со столбцами и строками:
12.1. Вставка столбцов и строк:
Вставка столбца перед выделенным столбцом (нажатие кнопкой мыши на букве столбца), контекстное меню и пункт «Добавить ячейки». После этого рядом появится новый столбец.
Вставка строки перед выделенной строкой (нажатие кнопкой мыши на номере строки), контекстное меню и пункт «Добавить ячейки». После этого вверху появится новая строка.
12.2. Скрытие столбца или строки:
Выделить строку или столбец (см. выше). Далее – меню «Формат» – пункт «Строка» или «Столбец» – подпункт «Скрыть».
Чтобы снова отобразить, т.к. это не всегда получается, выделить весь лист, затем повторить выбор меню, как при «скрытии», но выбрать подпункт «Отображение».
13. Присвоение имен ячейкам и блокам:
Имя ячейки вводится для выбранной ячейки или блока ячеек в поле имени ячейки после щелчка мыши.
Нажатием стрелки можно выбрать имя для использования в формулах.
Имена должны быть понятными и содержательными!
В сложном имени вместо запрещенных пробелов можно использовать знак «_»!.
Так же имя поля не должно начинаться с цифры и знака операции.
Не желательно использовать имена, похожие на имена ячеек.
Имена можно вызвать из списка в любом листе рабочей книги.
Работа с именами :
Необходимо вызвать пункт главного меню «Вставка» – подпункт «Имя»:
- «Присвоить» – показывает полный список имен;
- в поле «Формулы» – адрес ячейки;
- можно ввести новое имя;
- можно удалить выбранное имя;
- кнопка «Добавить», если нужно добавить подряд несколько имен;
- «Вставить» – присвоение интервалу существующего имени:
- кнопка «Все имена» помещает все имена в выбранном столбце. При редактировании поля +
в нем вместо адреса появляется значение.
- «Создать» – при выделении столбцов или строк с именами эти имена будут присвоены ячейкам или блокам. Если предлагается два варианта подставки имен столбцов или строк в имя переменной оставлять выбор по смыслу (в строках – слева, в столбцах – вверху);
- «Применить» – выделение имени щелчком мыши. Аналогично – отмена. «ОК» - вместо адреса ячейки в формулы вставит имя ячейки или блока!
- «Создать» – при выделении столбцов или строк с именами эти имена будут присвоены ячейкам или блокам. Если предлагается два варианта подставки имен столбцов или строк в имя переменной оставлять выбор по смыслу (в строках – слева, в столбцах – вверху);
Желательно все имена хранить в отдельном листе таблицы! Это относится и к строковым именам полей! В этот лист можно перенести и аргументы, участвующие в расчетах.
Потом этот лист можно скрыть для удобства при многопользовательском режиме работы!
14. Работа с блоком данных:
а) добавление строк в блок:
- выделить строку внутри блока;
- из контекстного меню вызвать команду «Добавить»;
- Excel подскажет, что нужно произвести сдвижку вниз.
Расчет в функции (например, суммирование) в этом случае будет проведено с учетом новой строки! Аналогичная ситуация и я именем блока данных!
При этом данные в левой части листа, если таковые были, не будут переставлены.
б) удаление строк из блока:
- выделить строку внутри блока;
- из контекстного меню вызвать команду «Удалить»;
- Excel подскажет, что нужно произвести сдвиг вверх.
15. Группировка и Суммирование данных.
15.1. Промежуточные итоги:
Сначала выбранный список надо отсортировать: меню «Данные» – пункт «Сортировка». Можно ввести сортировку по трем полям. Должен быть выделен весь лист или нужные столбцы – для правильной сортировки! Автофильтр и закрепление областей обязательно должны быть сняты, т.к. они не пересортируются !!! Поэтому желательно подсчитывать Промежуточные итоги на другом листе, а лучше всего и в другой книге !!!
Затем:
- выделяем столбцы для сортировки и счета;
- лишние столбцы желательно удалить, чтобы не отвлекали от просмотра результатов;
- выбрать меню «Данные» – пункт «Итоги». В нем «Промежуточные итоги»;
- указать поле, по которому собираем итоги (это первое поле в сортировке);
- вид арифметической операции (чаще всего, количество или сумма);
- в пункте «Дополнительные итоги»:
- для суммирования - цифровое поле, по которому производим суммирование (или другую операцию с этим полем);
- для подсчета количества – только поле сортировки;
- указать «галочкой» работу по подписям (1-я строка выбранного блока, тогда она не выделяется). Иначе проблемы с выбором столбцов.
Будет показано суммирование по нескольким уровням. Чтобы оставить суммирование только по основному полю, нужно закрыть данные второго уровня (щелчок мыши на подчиненном уровне). Обратно возвращаем нажатием на кнопке 3-го уровня.
Чтобы печатать итоги на разных страницах, нужно включить кнопку «Конец страницы между группами».
Чтобы сделать здесь же Промежуточные итоги по второму полю, нужно :
- чтобы оно было вторым в сортировке;
- после 1-го указать итоги по нему.
15.2. Сводные таблицы:
Сводные таблицы создаются из списка или БД. Вызываются из меню «Данные» – пункт «Сводная таблица». Ее лучше строить на отдельном листе.На 1-м шаге выделяем всю область исходной таблицы.На 2-м шаге перетаскиваем поля в строки и столбцы (их может быть несколько). В область «Данные» помещаем поля для суммирования (их может быть несколько).Поле «Страница» (без поля в строке) позволяет дать только суммарные данные, а отдельно можно получить информацию из ниспадающего меню.
Два щелчка на поле сводной таблицы – его редактирование. Модно скрыть часть элементов (например, некоторые месяцы).
Для связи с исходной таблицей после изменения данных нужно в меню «Данные» нажать кнопку «Обновить данные».
Если перенести поле из строки в поле «Страница» получим общие итоги и выбор из списка.
4-й шаг: не помешает включение всех флажков!
На поле строк можно получить детали, через контекстное меню выбрав дополнительные реквизиты.
15.3. Консолидация:
Связь между данными, расположенными в разных местах книг и листах. Но таблицы должны быть строго идентичны!
Вызов консолидации – меню «Данные» – пункт «Консолидация».
Функция – сумма и т.д. (по смыслу).
В список диапазонов (на каждом листе):
- выбрать диапазон на листе;
- нажать кнопку «Добавить».
Все флажки должны быть включены, главное, на поле «Создавать связи с исходными данными».
Окончание: кнопка «ОК».
«+/-» - раскрывает (сворачивает) выбранную строку.
1, 2 и т.д. слева вверху – раскрывают и сворачивают уровни целиком.
При очистке листа предыдущие диапазоны сохраняются!
Редактировать форму этой таблицы нельзя! Если не устраивает, нужно создавать по новой!!!
Можно добавить новый уровень вложенности (например, компьютеры):
- подуровни д.б. раскрыты;
- пустую строку добавить под последней строкой диапазона (между категориями), учитывая, что подуровни расположены сверху ;
- в столбец ввести название (например, компьютеры);
- выделить диапазон, не включая новую строку;
- меню «Данные» – «Структура» - «Сгруппировать».
Станет три номера (1,2,3) вверху слева. 2 – группировка по типу ПК.
В новую строку авто суммированием вставляем итоговые суммы.
Можно сгруппировать столбцы, получив столбец с общим итогом. Вверху тоже появится 2 группировки (1 и 2).
Черная линия показывает размер диапазона.
Аналогично можно разгруппировать данные. Можно также удалить всю структуру для выделенных данных
Итоговые формулы д.б. ниже и справа от данных.
Можно также использовать команду «Автоформат».
15.4. Поиск решения:
Режим “Дополнения”
Может дать % зависимых изменений.
Можно ввести ограничения по количественным полям.
Меню «Сервис» - пункт «Поиск решения».
Целевая ячейка – которая используется в качестве критерия оптимизации.
Изменяя ячейки (например, С4-С9), которые нужно пересчитать.
$C$11 – описание ячейки.
Сохраняем найденное решение – новые данные в таблицу.
Можно составить отчет.
Без создания отчета при нажатии на кнопку «Восстановить исходные значения» найденные значения будут удалены.
Чтобы использовать найденную модель и дальше, надо ее записать, как Сценарий.
16. Диаграммы:
В Excel 9 типов плоских диаграмм и 6 типов объемных диаграмм. Всего они содержат 102 формата!
Удобно пользоваться Мастером диаграмм с Панели инструментов. Его работу можно прекратить на любом этапе, при этом вид диаграммы будет выбран Excel по умолчанию. Мастер строит диаграмму на том же листе. Чтобы построить диаграмму на другом листе выбрать из главного меню пункт «Вставка» – подпункт «Диаграмма» – команда «На новом листе».
Активной диаграмма становится после щелчка на ее области. Удаляется активная диаграмма нажатием клавиши
Диаграмма не привязана к конкретной ячейке.
Ее можно перемещать обычным методом Drag & Drop.
Размер диаграммы изменяем с помощью точек по границах диаграммы (угловые точки растягивают в обе стороны).
Форматирование диаграммы вызывается из контекстного меню:
- вид:
- с флажками и скругленная – получается очень изящная форма;
- заливка:
- флажок «Прозрачная» – через диаграмму будет виден текст в ячейках. Такую диаграмму можно перемещать только за рамку. Это не очень удобно!;
- после окончания работы установить флажок «Не перемещать и не изменять размеры»;
- можно запретить вывод диаграммы на печать (если она предназначена только для просмотра);
- защищаемая ячейка (защита диаграммы) – она считается объектом.
При построении Графика функции нужно использовать вид диаграмм: «Точечный» с соединением непрерывной линией.
Удобно при Форматировании выбрать тип маркера – «.». А его размер = 2.
Для отслеживания тенденций развития лучше использовать тип 10 «Графиков».
Один из графиков можно сделать «С областями», как эталонный.
Чтобы отформатировать одну из Осей, нужно встать на нее курсором мыши и выбрать из контекстного меню режим «Формат». Можно изменить границы показа данных по оси и другие параметры. Аналогично форматируются и другие элементы диаграммы.
При изменении числовых параметров, формирующих график с большим разбросом сумм, желательно установить признак «Авто» на следующих характеристиках оси:
- минимум;
- максимум;
- цена основного деления;
- цена промежуточного деления;
- пересечение оси «Х» с осью «Y».
Если названия по оси координат не все высвечиваются, значит нужно увеличить размеры области диаграмы или уменьшить шрифт оси диаграммы.
Для показа зависимостей итоговых сумм по месяцам удобно использовать вид диаграмм – «Гистограмма» - объемное изображение. А среди них для простого просмотра достаточно выбрать Простую гистограмму (левая диаграмма во втором ряду).
Добавление дополнительного ряда в диаграмму производится с помощью Drag & Drop.
Двойным щелчком на Диаграмме вызывается область ее инструментов.
Можно форматировать каждый элемент легенды, включая вид линии и название. Также можно выбрать для диаграмм разный вид маркеров.
Можно отредактировать каждый график, вызвав на нем контекстное меню. Можно изменить и вид диаграммы. Плоские и объемные диаграммы смешивать нельзя!
При форматировании ряда:
- включен признак «Значения элементов» - показываем в точках пересечения осей исходные величины;
- включен признак «Название категории» – появятся наименования с оси Х.
Эти 2 признака удобно комбинировать на соседних графиках.
Раздел «Интервал изменения значений» – можно выбрать планки погрешностей по оси «Y» для задач по физике.
Раздел «Применить» показывает все разновидности диаграмм на данном участке. Можно их изменять для выбранной группы.
Пункт «Порядок рядов» – можно переставить порядок в соответствии со смыслом (но в своей группе диаграмм).
Потянув за верхнюю точку гистограммы можно её увеличить или уменьшить. При этом изменятся и данные в таблице, что может быть достаточно вредным эффектом !
Для построения диаграммы:
1. Необходимо выделить два и более столбцов. Для начала удобнее пользоваться двумя столбцами. Если они расположены в разных местах таблицы, для не первых столбцов выбор осуществлять с помощью, не отрываемого нажатия, кнопки «Ctrl».
2. При переносе в другую таблицу Диаграмма остается привязанной к таблице-хозяину !
3. Если в дальнейшем исходные параметры могут наращиваться (по осям «Х» и «Y»), желательно заранее выделить эти пустые области, чтобы потом не менять параметры исходных данных.
17. Разделение листа на независимые части:
Узкий ползунок в правом верхнем углу вертикальной области прокрутки и горизонтальной прокрутки при его перемещении вниз (и аналогично, влево) делит экран на две горизонтальные, независимые части со своими полосами прокрутки и на две вертикальные части. При обоюдном перемещении на листе будут четыре независимые части. Иногда это может быть удобным! Возврат к единому листу производится в обратном порядке.
18. Скрытие сетки таблицы:
Чтобы оформленная таблица была на чистом листе (как при печати) можно убрать линии сетки: меню «Сервис» – пункт «Параметры» - окно «Вид». Снять флажок на поле «Сетка». Но в таблицах, с которыми мы постоянно работаем это не очень удобно!
19. Представления (диспетчер видов):
!!! При попытках в листах книги производить Сортировки и другие сложные операции над данными необходимо иметь в виду, что при отсутствии надежной структуризации, Вы имеете опасность снова не получить основную форму после проведения сортировки !
Чтобы избежать подобных неудобств, есть несколько путей:
- если, к примеру, сортируете в том же листе, перед записью изменений проверьте: получите ли Вы основной несортированный вид листа;
- ещё проще вынести этот лист в отдельную книгу и там отсортировать. Хотя тогда придется все время повторять сортировку ;
- или промежуточные итоги. Но при добавлении данных это все равно придется делать;
- еще можно создать Вид по каждому из вариантов создания: автофильтров, сортировок и т.д.
В версии Microsoft Office 6.0 выбор из меню «Вид», пункт «Диспетчер видов».
В версии Microsoft Office 7.0 под Windows-98 это : меню “Вид”, пункт “Представления”.
Но сначала необходимо придать листу надлежащий вид, включая установки Параметров страниц. Затем открыть вышеуказанное меню и нажать кнопку «Добавить».
Далее нужно ввести имя Представления, а за ним в скобках желательно написать имя листа, чтобы не было путаницы. Затем при вызове Представления нажимать на кнопку «Применить» или два щелчка мыши на строке Представления. Затем лучше книгу закрыть для сохранения изменений.
20. Присвоение пароля:
Выбрать в меню “Файл” пункт “Сохранить как”.
Нажать на кнопку “Параметры” и ввести пароль на пункте “Пароль при открытии файла”.
Можно также дать пароль на ввод записи, но при единоличном пользовании таблицей это неудобно.
Ввести пароль повторно и сохранить файл.
Далее Вас Excel будет запрашивать Пароль при открытии данной таблицы.
21. Статистика.
Меню «Сервис» - пункт «Статистика».
Для отмеченного блока или всего текста можно узнать:
- количество станиц;
- количество слов;
- количество символов:
- с пробелами;
- без пробелов;
- количество абзацев.
22. Предварительный просмотр.
Нажатие иконки «Лист с лупой». Можно изменять масштаб. Выход с помощью кнопки «Закрыть». В режиме просмотра можно редактировать. Но не желательно…
23. Вставка объектов в таблицу.
Документ Word можно вставить через меню «Вставка», пункт «Объект», выбрав из списка совместимых систем – редактор Word.
Карта:
Выбирается через меню «Вставка», пункт «Карта» или через иконку в виде глобуса.
Есть режимы показа карты: Европы, России, стран мира. Украины нет.
Карту можно использовать вместе с данными из таблиц.
Связанные объекты:
Обеспечение связи таблицы с базами данных Access или текстом Word.
Но при этом нужно сохранить внесенные изменения.
24. Многодокументный интерфейс:
Открыть нужные книги в меню «Файл», пункт «Открыть».
На экране будет помещена последняя открытая книга.
Перечень книг показан в меню «Окно».
Для осуществления копирования необходимо выделить блок ячеек в одном месте и указать, что его необходимо скопировать. Затем в меню «Окно» выбрать вторую книгу. В нем указать место, куда надо вставить копию текста и осуществить вставку (см. выше).
Можно открыть несколько окон одновременно на одном экране. Меню «Окно», пункт «Расположить». Вам будет предложено четыре варианта. Я предпочитаю работать одновременно с двумя листами в режиме «сверху вниз», так как окна листов приобретают сокращенный вид и в большом количестве окон сложно производить копирование и другие операции над таблицами.
26. Макросы:
Макрос – записанная под определенным именем, последовательность действий.
Создание макроса : меню «Сервис» - пункт «Запись макроса» – команда «Начать запись». Ввести имя и описание макроса. Правила создания имен макросов аналогичны присвоению имен ячейкам (см. выше)! Обязательно писать подробные комментарии!
- пункт «параметры»:
- флажок на поле «Сочетание клавиш» – вводим букву в пару к кнопке
. Теперь совместным нажатием этих двух кнопок макрос будет вызываться в таблице;
- флажок «Пункт в меню «Сервис» - появится новый пункт меню внизу списка меню «Сервис». Название меню можно скопировать из описания макроса.
- выполняем действия для записи в макрос, пока не нажмем кнопку «Стоп» (черный квадрат, а вверху стрелка).
- флажок «Пункт в меню «Сервис» - появится новый пункт меню внизу списка меню «Сервис». Название меню можно скопировать из описания макроса.
Перед началом записи макроса, работающего с группой ячеек установить флажок на строке «Относительные ссылки», на потом начать запись макроса.
Запуск и выбор существующего Макроса: меню «Сервис» – «Макросы» - выбрать макрос и нажать кнопку «Выполнить» или два щелчка (
Далее ввести имя для нового Макроса и выбрать нужную книгу (*.XLS).
Существующий Макрос выбрать из списка.
В Макросе пункт «Изменить» - для корректировки текста Макроса. Работа в VBA (см. отдельную лекцию).
При редактировании макроса:
- после щелчка мышью в нужном месте пункт «Отметить позицию»;
- затем продолжить запись макроса с пункта «Записать с отметки».
Запуск Макроса : выбрать Макрос и пункт «Выполнить». Он сворачивается в значок. Из иконки запустить.
Возвращение в таблицу Excel :
- меню “Файл” – «Закрыть» и выбрать “Возврат в таблицу”;
- нажатием кнопок “Alt” + “Q”;
- на вопрос «Прервать макрос?» ответить «Да».
Запуск макроса из кнопки:
- кнопку берем с панели «Рисование». Excel предложит подобрать макрос;
- редактирование названия кнопки через пункт «копирование» контекстного меню.
Примеры:
- Solvsamp.xls – пример применения стиля «Поиск решения»;
- Samples.xls – примеры по VB IDE
- Funcs.xls – перечень всех функций (по-русски и по-английски);
- Xl8garly.xls – показ формы видов диаграмм.