Активная ячейка в правом нижнем углу имеет маленький квадратик. Он называется маркер заполнения. С его помощью можно автоматически создавать последовательности с возрастающим шагом.
Например, - в ячейку А1 ввести число 1, в А2 - 2
- выделить эти ячейки
- маркер заполнения переместить вниз до ячейки А20
- отпустить кнопку мыши
в ячейке А3 появится цифра 3 и т.д. в А20 - 20.
Если нужно заполнить диапазон одинаковыми значениями, перемещая маркер заполнения, нажать Ctrl.
Создавая последовательности можно менять шаг: в А1 - 1; в А2 - 3, тогда в А3 будет -5.
Можно создавать последовательности и текстовой информации.
Скрыть данные
Скрыть содержимое таблицы можно через меню Формат, Столбец, Скрыть или установить указатель мыши на разделитель столбца, справа от его заголовка, он примет вид двунаправленной стрелки, перетащить его влево пока столбец не скроется.
Скрыть строки: Формат, Строка, Скрыть.
Открыть: выделить столбцы, примыкающие к скрытому; Формат, Столбец, Отобразить.
Можно скрыть целый лист: Формат, Лист, Скрыть.
Защита данных
В Excel можно также защищать данные от изменений: Меню Сервис, Защита, выбирается.
- защитить лист
- защитить книгу
- по желанию можно ввести пароль ( при вводе пароля учитывается регистр ).
Защищаемые данные нельзя перемещать, редактировать, форматировать, удалять.
Чтобы снять защиту:
Сервис, Защита, Снять защиту листа ( книги ).
Работа с формулами
Одно из преимуществ электронных таблиц - использование формул для произведения вычислений.
Формулы могут выполняться с помощью простых арифметических действий (+, - , *, /, % ,^) .
Можно использовать скобки. Формула должна обязательно начинаться со знака = и включать ссылки на адреса ячеек.
Пример: = ( А1 + В1)/ С1
= А1+ В1 = А1 * В1 = А1% ( если в А1-5, то результат = 0,05 )
= А1- В1 = А1 / В1 = А1 ^ 2 ( возведение в степень, результат = 25 )
Курсор находится в ячейке, куда необходимо поместить результат. После введения формулы, в ячейке появится результат вычисления, формула будет отображена в строке формул.
Для редактирования формулы - клавиша F2.
Чтобы вывести все использованные в таблице формулы ( формульный вид ) используется меню:
- Сервис, Параметры, вкладка Вид, Формулы.
При изменении значений в ячейках, которые использовались в формулах, автоматически будет меняться результат.
Если формула введена не верно, она воспринимается как текст.
Формулы можно копировать в другие ячейки. Если необходимо, чтобы ссылки на ячейки изменялись при копировании формул, то используется относительная адресация, если этого не нужно - используется абсолютная адресация ( $ ).
В формулах можно ссылаться на ячейки других листов: Лист 6 ! А1.
Можно использовать трехмерные ссылки, чтобы ссылаться на диапазон ячеек из нескольких листов: ( Лист1 : Лист 6 ! А1 :А10 )
Также в формулах можно использовать текст. Для этого используется амперсант &, который объединяет текст, заключенный в “.
Объединение текста называется конкатенацией ( сцеплением ).
Например, мы рассчитываем курс валют
в ячейке А1 - находится цена в ПМР ( 59 ), в ячейке А2- курс доллара к ПМР ( 5.9 ),
в ячейке А3 - необходимо рассчитать цену в долларах, и добавить текст, что это у. е.
в ячейку А3 вводится формула: = А1 / А2 & “у.е.” - в результате в А3 - 10 у.е.
При изменении курса, будет меняться значение в А3.
Можно также использовать в формулах конкатенацию для соединения одного текста с другим, вставляя адреса, названия и т.д.
Пример: в ячейке А1 - “ИТ – 102 “
А2 – “ИТ – 103”
В ячейке А4: = “Лучшие студенты в группах” & A1 & “и” & A2
& -используется для объединения текста, ячеек, диапазонов.
Текст - обязательно в “”.
Можно создавать формулы, используя операции сравнения, для сравнения результатов: Эти операции возвращают результат истина или ложь. Для этого используются знаки: =; <; >;< =; > =; < >
Например: формула = А12<15
результат: если в А12 значение < 15, то истина и наоборот ложь.
Если в формуле используется диапазон ячеек, он указывается через : , если несколько диапазонов - они объединяются с помощью запятой ( А1: А5, В1 : В5 ).
Вычисления с массивами
Для выполнения больших объемов вычислений, для замены повторяющихся формул используются массивы. Excel хранит в памяти массив формул как одну формулу, это экономит память.
Массив - это прямоугольный диапазон формул или значений, которые Excel обрабатывает как единую группу. Признак массива - фигурные скобки, в которые заключена формула ( появляется автоматически ).
Пример: Необходимо значение в ячейках с А1 по А300 умножить на соответствующие значения с В1 по В300, результат поместить в С1 по С300.
Можно ввести в С1 формулу = А1 * B1, а затем скопировать ее в диапазон с С2 : C300.
Но, чтобы сэкономить память, введем в С1 формулу = А1 : А300 * B1 : B300 и нажмем комбинацию Shift + Ctrl + Enter.
Произойдет 300 перемножений и результаты занесутся в C1 : C300. В каждой ячейке этого диапазона появится формула { = A1 : A300 * B1 : B300 }
Отдельные ячейки в массиве не подлежат редактированию. Редактируется весь массив: F2, изменить формулу и Shift + Ctrl + Enter.
Использование функций
Помимо арифметических действий в Excel при расчетах можно использовать встроенные функции. В Excel встроено более 200 функций.
Формат записи:
= Имя функции (список аргументов).
Каждая функция имеет свой набор аргументов. Аргументами могут быть числа, ссылки, текст, логические выражения. Некоторые функции могут иметь до 30 аргументов.
Чтобы вызвать список функций, на панели инструментов выбирается Мастер функций Fx.
Открывается диалоговое окно, содержащее:
- категорию функций (классификация)
- имя функций (по каждой категории)
- подсказка о текущей функции (на которой курсор)
Категории функций:
- Последние используются (10 последних использованных функций),
- Все (всё в алфавитном порядке),
- Финансовые,
- Дата и время,
- Математические (пр. Sin, LN, СУММ и так далее ).
- Статистические (дисперсия, корреляция, СРЗНАЧ, МАКС, МИН ) .
- Логические (= Если С1> D1*B1; «УРА!»; «УВЫ»)
- Текстовые (операции с текстом) и др.
Отредактировать формулу - клавиша F2.
Формулы можно копировать, как и другие данные из ячеек:
1) Через меню Правка
2) Через контекстное меню
Сортировка данных
Сортировка используется для выстраивания данных в алфавитном или цифровом порядке по возрастанию или убыванию. Необходимо сначала выделить данные, подлежащие сортировке, затем выбрать меню Данные, Сортировка. Открывается диалоговое окно, в котором задаются параметры сортировки. При любой сортировке пустые ячейки помещаются в конец данных. Можно задавать сортировку кнопками панели инструментов. Необходимо соблюдать осторожность при сортировке ячеек, содержащих формулы (относительные адреса будут меняться, что скажется на результате вычислений).
Фильтрация данных
Используется для выделения части данных и делает видимой часть таблицы. Перед фильтрацией таблицу необходимо выделить, выбрать пункт меню Данные, Фильтр, Автофильтр. В верхней строке таблицы в каждой ячейке появится значок Ñ. Щелкнув по нему, появляется содержимое данного столбца. Выбрав нужное значение, щелкнуть левой кнопкой мыши, Excel оставит только те строки, в которых содержится выбранное значение. Это значение называется критерий фильтра.
Отфильтрованные строки можно выделять другим цветом, менять в них шрифт и эти параметры сохранятся после снятия фильтрации. Отфильтрованные таблицы можно фильтровать повторно по другим критериям. Если необходимо отфильтровать не всю таблицу, то предварительно выделяются те столбцы, в которых необходимо провести фильтрацию. В отфильтрованных участках млжно производить вычисления, используя для этого функцию “Промежуточные итоги”. Отменить фильтрацию можно через меню Данные, Фильтр, убрать флажок Автофильтр.
Можно производить фильтрацию по нескольким критериям. Для этого в раскрывшемся списке столбца выбрать строку «Условие». Откроется диалоговое окно «Пользовательский автофильтр». Для критерия отбора можно использовать знаки >,<, >=, <=, <>, а также операторы И и ИЛИ.
Построение диаграмм
В Excel существует 2 типа диаграмм:
1. внедренные диаграммы - содержатся на том же месте, где таблица, и сохранены в том же файле;
2. диаграммные листы - на отдельном листе и сохранены в отдельном файле.
Оба типа строятся с помощью мастера диаграмм через панель инструментов. Открывается диалоговое окно, которое состоит из нескольких окон.
1 шаг - задается тип диаграммы (предложено 15 типов диаграмм с рисунками).
2 шаг - служит для задания интервала, по которому будет строиться диаграмма. Для удобства лучше предварительно выделить интервал данных в таблице, тогда на этом шаге он будет внесен автоматически.
3 шаг – задание параметров диаграммы.
4 шаг – выбор куда поместить диаграмму.
При изменении данных в таблице, диаграмма будет автоматически меняться.
График можно перемещать по рабочему столу, для этого надо нажать на диаграмме левой кнопкой и не отпуская ее перетащить.
Можно менять размеры графика (так же, как размеры окон).
Для редактирования диаграммы - 2 раза левой кнопкой мыши внутри диаграммы, каждый элемент будет обведен черным квадратом и двойной щелчок по отдельному элементу позволяет его редактирование. Можно редактировать с помощью контекстного меню.
Добавление данных в диаграмму:
1 способ. Выделить в таблице диапазон, который нужно добавить, установить курсор на границе выделенной области, нажать левую кнопку мыши и не отпуская ее переместить этот диапазон в диаграмму. Появится окно “Специальная вставка” (указать параметры, Добавить ячейки как новые ряды.)
2 способ. Выделить данные, скопировать в буфер, переместить курсор на диаграмму и вставить из буфера.
3 способ. Выделить диапазон данных, меню Вставка, Новые данные.
Можно изменить внедренную диаграмму на диаграммный лист и наоборот. Для этого курсор на диаграмме, меню Вставка, Диаграммы, Расположение и указать имя листа.
Диаграмму можно защитить от изменения: меню Сервис, Защита.
Оформление таблицы
На панели инструментов есть кнопка “обрамление” (границы). Нажав ее, появляются типы и виды линий, среди которых выбираются нужные.
Можно выделить нужные диапазоны цветом.
В меню Формат, Автоформат, будет предложено 16 готовых стилей оформления. Можно выбрать готовый стиль. Есть вкладка «Параметры», где можно изменить любой из стилей.
В меню Сервис, Параметры можно задать сетку таблицы, заголовки строк и столбцов.
Сводные таблицы
Они позволяют анализировать данные, группировать и обобщать.
Сводные таблицы используются для:
1. Если существует большое количество однотипных данных, можно создать обобщаемую таблицу.
2. Можно реорганизовать таблицу с помощью перетаскивания полей.
3. Можно строить диаграмму на основании сводных таблиц.
4. Отбор и группировка данных
Для построения сводной таблицы используется мастер сводной таблицы. Он запускается через меню Данные, Сводная таблица, открывается диалоговое окно, состоящее из четырех шагов:
1 - указываем на основе каких данных строить сводную таблицу. (в списке или базе данных, во внешнем источнике или в нескольких диапазонах консолидации). При создании сводной таблицы можно воспользоваться несколькими внешними источниками данных, например базами данных, текстовыми файлами или любыми другими, отличными от книг Microsoft Excel. Кроме того, можно воспользоваться источниками данных в Интернете. При консолидации данных нескольких списков или листов, списки и листы должны иметь одинаковые заголовки строк и столбцов.
2 – указывается диапазон, содержащий данные (лучше сначала выделить таблицу, тогда диапазон будет внесен автоматически). Исходный список или база данных должны иметь заголовки столбцов, так как содержимое ячеек первой строки списка будет использоваться в качестве имен полей.
3 – задается структура сводной таблицы, т.е. как размещать элементы таблицы в столбцах или строках.
4 - где расположить сводную таблицу.
В сводной таблице автоматически создаются общие и промежуточные итоги. Если исходный список содержит общие и промежуточные итоги, их, возможно, потребуется удалить, перед созданием сводной таблицы.
Дата: 2018-12-28, просмотров: 180.
