Microsoft Excel — это повсеместно распространенный и удобный инструмент для работы с электронными таблицами.

Широкие функциональнее возможности делают эту программу второй по популярности после MS Word среди всех офисных программ.

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

Одной из самых удобных функций в этой программе является фильтрация данных. Рассмотрим, как настроить и использовать MS excel фильтры.

Где в Excel фильтры — их виды

Найти фильтры в этой программе легко — нужно открыть главное меню или просто зажать клавиши Ctrl+Shift+L.

Основные функции фильтрации в Excel:

• отбор по цвету: дает возможность отсортировать данные по цвету шрифта или заливки,
• текстовые фильтры в excel: позволяют задать те или иные условия для строк, например: меньше, больше, равно, не равно и другие, а также задать логические условия — и, или,
• числовые фильтры: отсортировывают по числовым условиям, например, ниже среднего, первые 10 и другие,
• ручной: отбор можно выполнять по выбранным самостоятельно критериям.

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

Как использовать расширенный фильтр в Excel — как его настроить

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

ВИДЕО ИНСТРУКЦИЯ

Порядок настройки:

1. Создать таблицу с данными для дальнейшей работы с ней. В ней не должно быть пустых строк.
2. Создать таблицу с условиями отбора.
3. Запустить расширенный фильтр.

Рассмотрим пример настройки.
У нас есть таблица со столбцами Товар, Количество и Цена.

К примеру, нужно отсортировать строки, названия товаров которых начинаются со слова «Гвозди» Под это условие попадают несколько строк.

Таблица с условиями разместится в ячейках А1:А2. Важно указать название столбца, где будет происходить отбор (ячейка А1) и само слово для отбора – Гвозди (ячейка А2).

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

Затем необходимо:

1. выделить любую из ячеек,
2. открыть «Расширенный фильтр» по пути: Данные – Сортировка и фильтр – Дополнительно,
3. проверить, что задано в поле «Исходный диапазон» — туда должна попасть вся таблица с информацией,
4. в «Диапазоне условий» необходимо задать значения ячеек с условием отбора, в данном примере это диапазон А1:А2.

После клика по кнопке «ОК» произойдет отбор нужной информации, и в таблице появятся только строки с нужным словом, в нашем случае это «Гвозди». Номера оставшихся строк окрасятся в синий цвет. Для отмены заданного фильтра достаточно нажать клавиши CTRL+SHIFT+L.

Также легко настроить отбор по строкам, содержащим точно слово «Гвозди» без учета регистра. В диапазоне В1:В2 разместим колонку с новым критерием отбора, не забыв указать заголовок столбца, в котором будет выполняться отсев. В ячейке В2 необходимо указать следующую формулу =»=Гвозди».

• выделить любую из ячеек таблицы,
• открыть «Расширенный фильтр»,
• проверить, что в «Исходный диапазон» попала вся таблица с данными,
• в «Диапазоне условий» указать В1:В2.

После нажатия «ОК» произойдет отсев данных.

Это самые простые примеры работы с фильтрами в excel . В расширенном варианте удобно задавать и другие условия для отбора, например, отсев с параметром «ИЛИ», отсев с параметром «Гвозди» и значением в столбце «Количество» >40.

Информацию в таблице можно фильтровать по столбцам – одному или нескольким. Рассмотрим на примере таблицы со столбцами «Город», «Месяц» и «Продажи».

Пример 1

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

Для получения информации по обратному принципу нужно воспользоваться параметром «ЯА».

Пример 2

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

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

ВИДЕО ИНСТРУКЦИЯ

Почему могут не работать фильтры в Excel

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

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

Решение проблемы:

1. выделить столбец с датами,
2. открыть вкладку Excel в главном меню,
3. выбрать кнопку «Ячейки», в выпадающем списке выбрать параметр «Преобразовать текст в дату».

К популярным ошибкам пользователей при работе с данной программой также стоит отнести:

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

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

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

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

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

ПРИМЕНЕНИЕ ФИЛЬТРА В EXCEL

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

1. Выделите любую ячейку в таблице, например, ячейку A2.

Чтобы фильтрация в Excel работала корректно, лист должен содержать строку заголовка, которая используется для задания имени каждого столбца. В следующем примере данные на листе организованы в виде столбцов с заголовками в строке 1: ID #, Тип, Описание оборудования и т.д.

2. Откройте вкладку Данные , затем нажмите команду Фильтр .

3. В заголовках каждого столбца появятся кнопки со стрелкой.

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

5. Появится меню фильтра.

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

7. Установите флажки для тех типов оборудования, которые необходимо оставить в таблице, затем нажмите OK . В нашем примере мы выберем Ноутбуки и Планшеты , чтобы видеть только эти типы оборудования.

8. Таблица с данными будет отфильтрована, временно скрыв все содержимое, не соответствующее критерию. В нашем примере только ноутбуки и планшеты остались видимыми.

Фильтрацию можно также применить, выбрав команду Сортировка и фильтр на вкладке Главная .

ПРИМЕНЕНИЕ НЕСКОЛЬКИХ ФИЛЬТРОВ В EXCEL

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

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

2. Появится меню фильтра.

3. Установите или снимите флажки с пунктов в зависимости от данных, которые необходимо отфильтровать, затем нажмите OK . Мы снимем выделение со всех пунктов, кроме августа .

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

СНЯТИЕ ФИЛЬТРА В EXCEL

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

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

2. Появится меню фильтра.

3. Выберите пункт Удалить фильтр из столбца... В нашем примере мы удалим фильтр из столбца Отдано на проверку .

4. Фильтр будет удален, а скрытые ранее данные вновь отобразятся на листе Excel.

Чтобы удалить все фильтры в таблице Excel, щелкните команду Фильтр на вкладке Данные .

РАСШИРЕННЫЙ ФИЛЬТР В EXCEL

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

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

ФИЛЬТРАЦИЯ И ПОИСК В EXCEL

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

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

2. Нажмите на кнопку со стрелкой в столбце, который необходимо отфильтровать. В данном примере мы выберем столбец C.

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

ОК .

5. Данные на листе будут отфильтрованы в соответствии с ключевым словом. В нашем примере после фильтрации таблица содержит только оборудование марки Saris .

ИСПОЛЬЗОВАНИЕ РАСШИРЕННЫХ ТЕКСТОВЫХ ФИЛЬТРОВ В EXCEL

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

1. Откройте вкладку Данные , затем нажмите команду Фильтр

2. Нажмите на кнопку со стрелкой в столбце, который необходимо отфильтровать. В нашем примере мы выберем столбец C.

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

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

5. Данные будут отфильтрованы по заданному текстовому фильтру. В нашем случае отражены только позиции из категории Другие , которые не содержат слово "case".

ИСПОЛЬЗОВАНИЕ В EXCEL РАСШИРЕННЫХ ФИЛЬТРОВ ПО ДАТЕ

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

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

2. Нажмите на кнопку со стрелкой в столбце, который необходимо отфильтровать. В этом примере мы выберем столбец D, чтобы увидеть нужные нам даты.

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

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

ИСПОЛЬЗОВАНИЕ РАСШИРЕННЫХ ЧИСЛОВЫХ ФИЛЬТРОВ В EXCEL

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

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

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

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

4. В появившемся диалоговом окне Пользовательский автофильтр введите необходимые числа для каждого из условий, затем нажмите OK . В этом примере мы хотим получить номера, которые больше или равны 3000, но меньше или равны 4000.

5. Данные будут отфильтрованы по заданному числовому фильтру. В нашем случае отображаются только номера в диапазоне от 3000 до 4000.

ГРУППЫ И ПРОМЕЖУТОЧНЫЕ ИТОГИ В EXCEL

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

ГРУППИРОВКА СТРОК И СТОЛБЦОВ В EXCEL

1. Выделите строки или столбцы, которые необходимо сгруппировать. В следующем примере мы выделим столбцы A, B и C.

2. Откройте вкладку Данные на Ленте, затем нажмите команду Группировать .

3. Выделенные строки или столбцы будут сгруппированы. В нашем примере это столбцы A, B и C.

Чтобы разгруппировать данные в Excel, выделите сгруппированные строки или столбцы, а затем щелкните команду Разгруппировать .

КАК СКРЫТЬ И ПОКАЗАТЬ ГРУППЫ

1. Чтобы скрыть группу в Excel, нажмите иконку Скрыть детали (минус).

2. Группа будет скрыта. Чтобы показать скрытую группу, нажмите иконку Показать детали (плюс).

ПОДВЕДЕНИЕ ИТОГОВ В EXCEL

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

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

СОЗДАНИЕ ПРОМЕЖУТОЧНОГО ИТОГА

В следующем примере мы воспользуемся командой Промежуточный итог , чтобы определить сколько заказано футболок каждого размера (S, M, L и XL). В результате рабочий лист Excel обретет структуру в виде групп по каждому размеру футболок, а затем будет подсчитано общее количество футболок в каждой группе.

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

2. Откройте вкладку Данные , затем нажмите команду Промежуточный итог .

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

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

5. В поле Добавить итоги по выберите столбец, в который необходимо вывести итог. В нашем примере это столбец Размер .

6. Если все параметры заданы правильно, нажмите ОК .

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

ПРОСМОТР ГРУПП ПО УРОВНЯМ

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

Хоть в этом примере представлено всего три уровня, Excel позволяет создавать до 8 уровней вложенности.

1. Щелкните нижний уровень, чтобы отобразить минимальное количество информации. Мы выберем уровень 1, который содержит только общее количество заказанных футболок.

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

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

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

УДАЛЕНИЕ ПРОМЕЖУТОЧНЫХ ИТОГОВ В EXCEL

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

1. Откройте вкладку Данные , затем нажмите команду Промежуточный итог .

2. Откроется диалоговое окно Промежуточные итоги . Нажмите Убрать все .

3. Все данные будут разгруппированы, а итоги удалены.

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

ТАБЛИЦЫ В MICROSOFT EXCEL

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

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

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

КАК СДЕЛАТЬ ТАБЛИЦУ В EXCEL

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

2. На вкладке Главная в группе команд Стили нажмите команду Форматировать как таблицу .

3. В раскрывающемся меню выберите стиль таблицы.

4. Появится диалоговое окно, в котором Excel уточняет диапазон будущей таблицы.

5. Если она содержит заголовки, установите опцию Таблица с заголовками , затем нажмите OK .

6. Диапазон ячеек будет преобразован в таблицу в выбранном стиле.

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

ИЗМЕНЕНИЕ ТАБЛИЦ В EXCEL

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

ДОБАВЛЕНИЕ СТРОК И СТОЛБЦОВ

Чтобы внести дополнительные данные в таблицу Excel, необходимо изменить ее размерность, т.е. добавить новые строки или столбцы. Существует два простых способа сделать это:

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

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

ИЗМЕНЕНИЕ СТИЛЯ

2. Затем откройте вкладку Конструктор и найдите группу команд Стили таблиц . Нажмите на иконкуДополнительные параметры , чтобы увидеть все доступные стили.

3. Выберите желаемый стиль.

4. Стиль будет применен к таблице.

ИЗМЕНЕНИЕ ПАРАМЕТРОВ

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

1. Выделите любую ячейку таблицы.

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

3. Таблица изменится. В нашем случае внизу таблицы появилась новая строка с формулой, которая автоматически вычисляет сумму значений в столбце D.

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

УДАЛЕНИЕ ТАБЛИЦЫ В EXCEL

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

1. Выделите любую ячейку таблицы и перейдите на вкладку Конструктор .

2. В группе команд Сервис выберите команду Преобразовать в диапазон .

3. Появится диалоговое окно с подтверждением. Нажмите Да .

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

ДИАГРАММЫ В EXCEL – ОСНОВНЫЕ СВЕДЕНИЯ

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

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

ТИПЫ ДИАГРАММ

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

1. ГИСТОГРАММА

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

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

3. КРУГОВЫЕ ДИАГРАММЫ

Круговые диаграммы подходят для демонстрации пропорций, т.е. части чего-то относительно целого. Каждое значение представлено в виде доли (сектора) от суммы всех значений (круга). Круговая диаграмма строится для одного ряда данных и, как правило, содержит до 5-8 секторов. Такой подход очень полезен, когда нужно сравнить данные друг с другом.

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

4. ЛИНЕЙЧАТАЯ ДИАГРАММА

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

5. ДИАГРАММЫ С ОБЛАСТЯМИ

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

6. ПОВЕРХНОСТНЫЕ ДИАГРАММЫ

Поверхностные диаграммы в Excel позволяют представить информацию в виде 3D перспективы. Лучше всего эти диаграммы подходят для больших объемов данных, чтобы видеть сразу весь спектр информации.

ЭЛЕМЕНТЫ ДИАГРАММ

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

1. Заголовок диаграммы должен четко описывать, что представлено на ней.

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

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

4. Легенда указывает принадлежность каждого ряда к кому-либо или чему-либо. В текущем примере легенда содержит 3 цвета с соответствующими продавцами. Видя легенду достаточно легко определить к какому продавцу относится каждый из столбцов.

5. Горизонтальная ось (также известная как ось X) является горизонтальной частью диаграммы. Горизонтальная ось представляет категории. В данном примере каждый квартал содержит свою группу.

КАК ПОСТРОИТЬ ДИАГРАММУ В EXCEL

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

2. На вкладке Вставка , выберите необходимую диаграмму. В нашем примере мы выберем Гистограмму .

3. В раскрывающемся меню укажите подходящий тип гистограммы.

4. Выбранная диаграмма появится на листе Excel.

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

МАКЕТ, СТИЛЬ И ПРОЧИЕ ПАРАМЕТРЫ ДИАГРАММ

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

МАКЕТ И СТИЛЬ ДИАГРАММ

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

· Excel позволяет добавлять на диаграмму такие элементы, как заголовки, легенды, подписи данных и т.д. Дополнительные элементы помогают облегчить восприятие и повысить информативность. Чтобы добавить элемент, щелкните по команде Добавить элемент диаграммы на вкладке Конструктор , а затем из выпадающего меню выберите тот, что необходим.

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

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

· Excel располагает большим количеством стилей, которые позволяют быстро изменять внешний вид диаграммы. Чтобы воспользоваться стилем, выберите его в группе команд Стили диаграмм .

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

ДРУГИЕ ПАРАМЕТРЫ ДИАГРАММ

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

СМЕНА СТРОК И СТОЛБЦОВ

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

1. Выделите диаграмму, которую необходимо изменить.

2. На вкладке Конструктор нажмите команду Строка/столбец .

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

ИЗМЕНЕНИЕ ТИПА ДИАГРАММЫ В EXCEL

Если Вы обнаружили, что текущая диаграмма не подходит под имеющиеся данные, Вы можете без труда перейти на другой тип. В следующем примере, мы изменим тип диаграммы с Гистограммы на График .

1. На вкладке Конструктор щелкните команду Изменить тип диаграммы .

Изменение типа диаграммы выберите новый тип и макет диаграммы, затем нажмите OK . В нашем примере, мы выберем График .

3. Появится выбранный тип диаграммы. В текущем примере видно, что График нагляднее передает динамику продаж в течение имеющегося периода.

ПЕРЕМЕЩЕНИЕ ДИАГРАММЫ В EXCEL

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

1. Выберите диаграмму, которую необходимо переместить.

2. Откройте вкладку Конструктор , затем нажмите команду Переместить диаграмму .

3. Откроется диалоговое окно Перемещение диаграммы . Выберите необходимое место. В текущем примере мы разместим диаграмму на отдельном листе и присвоим ему имя Продажи книг 2008-2012 .

4. Нажмите OK .

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

СПАРКЛАЙНЫ В MICROSOFT EXCEL

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

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

В некоторых источниках спарклайны называют инфолиниями .

ТИПЫ СПАРКЛАЙНОВ

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

ДЛЯ ЧЕГО ИСПОЛЬЗУЮТСЯ СПАРКЛАЙНЫ?

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

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

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

СОЗДАНИЕ СПАРКЛАЙНОВ В EXCEL

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

1. Выделите ячейки, которые будут служить исходными данными для первого спарклайна. Мы выберем диапазон B2:G2.

2. Перейдите на вкладку Вставка и укажите нужный тип спарклайна. Например, спарклайн-график.

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

4. Спарклайн появится в выбранной ячейке.

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

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

ИЗМЕНЕНИЕ ВНЕШНЕГО ВИДА СПАРКЛАЙНОВ

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

ОТОБРАЖЕНИЕ МАРКЕРОВ

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

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

2. На вкладке Конструктор в группе команд Показать включаем опции Максимальная точка и Минимальная точка .

ИЗМЕНЕНИЕ СТИЛЯ

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

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

3. Выберите необходимый стиль.

4. Внешний вид спарклайнов будет обновлен.

ИЗМЕНЕНИЕ ТИПА

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

2. На вкладке Конструктор выберите желаемый тип спарклайна. Например, Гистограмма .

3. Внешний вид спарклайнов будет обновлен.

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

ИЗМЕНЕНИЕ ДИАПАЗОНА ОТОБРАЖЕНИЯ

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

КАК ИЗМЕНИТЬ ДИАПАЗОН ОТОБРАЖЕНИЯ

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

2. На вкладке Конструктор выберите команду Ось . Появится выпадающее меню.

3. В параметрах для максимального и минимального значений по вертикальной оси включите опциюФиксированное для всех спарклайнов .

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

ОТСЛЕЖИВАНИЕ ИСПРАВЛЕНИЙ В EXCEL

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

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

ОБЩИЕ СВЕДЕНИЯ ОБ ОТСЛЕЖИВАНИИ ИСПРАВЛЕНИЙ В EXCEL

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

Существует ряд исправлений, которые Excel не может отслеживать. Перед использованием данной опции советуем Вам ознакомиться на сайте компании Microsoft со списком изменений, которые не отслеживаются и не выделяются.

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

КАК ВКЛЮЧИТЬ РЕЖИМ ОТСЛЕЖИВАНИЯ ИСПРАВЛЕНИЙ

1. На вкладке Рецензирование нажмите команду Исправления , а затем в выпадающем меню выберите пунктВыделить исправления .

2. В появившемся диалоговом окне Исправления установите флажок Отслеживать исправления . Убедитесь, что выбран пункт Выделять исправления на экране , а затем нажмите OK .

3. Если появится запрос для подтверждения сохранения книги, нажмите ОК .

4. Режим отслеживания исправлений будет включен. Теперь любая отредактированная ячейка будет помечаться цветной границей и треугольником в верхнем левом углу. Если рецензентов несколько, каждому из них будет присвоен свой цвет.

5. Выберите любую отредактированную ячейку, чтобы увидеть перечень внесенных изменений. В следующем примере мы изменили содержимое ячейки E6 с "?" на "Андрей".

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

ВЫНЕСЕНИЕ ВСЕХ ИСПРАВЛЕНИЙ НА ОТДЕЛЬНЫЙ ЛИСТ

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

1. Сохраните рабочую книгу.

2. На вкладке Рецензирование выберите команду Исправления Выделять исправления .

Исправления установите флажок , затем нажмите OK .

4. Исправления будут приводиться на отдельном листе с названием Журнал .

Чтобы удалить Журнал изменений из рабочей книги Excel, можно либо сохранить книгу, либо снять флажокВносить изменения на отдельный лист в диалоговом окне Исправления .

РЕЦЕНЗИРОВАНИЕ ИСПРАВЛЕНИЙ В EXCEL

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

ЧТО НЕОБХОДИМО ДЛЯ РЕЦЕНЗИРОВАНИЯ ИСПРАВЛЕНИЙ

1. Нажмите команду Исправления на вкладке Рецензирование Принять/отклонить исправления .

2. Если появится запрос, нажмите ОК , чтобы сохранить книгу.

3. Убедитесь, что в появившемся диалоговом окне Просмотр исправлений установлен флажок по времени и выбран вариант Еще не просмотрено . Затем нажмите OK .

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

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

КАК ОТКЛЮЧИТЬ РЕЖИМ ОТСЛЕЖИВАНИЯ ИСПРАВЛЕНИЙ

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

1. На вкладке Рецензирование нажмите команду Исправления и в выпадающем меню выберите пунктВыделять исправления .

2. В появившемся диалоговом окне снимите флажок Отслеживать исправления и нажмите OK .

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

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

ПРИМЕЧАНИЯ К ЯЧЕЙКАМ В EXCEL

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

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

КАК СОЗДАТЬ ПРИМЕЧАНИЕ В EXCEL

1. Выделите ячейку, к которой требуется добавить комментарий. В нашем примере мы выбрали ячейку E6.

2. На вкладке Рецензирование нажмите команду Создать примечание .

3. Появится поле для ввода примечаний. Введите текст комментария, затем щелкните в любом месте за пределами поля, чтобы закрыть его.

4. Примечание будет добавлено к ячейке и помечено красным индикатором в верхнем правом углу.

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

КАК ИЗМЕНИТЬ ПРИМЕЧАНИЕ В EXCEL

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

2. На вкладке Рецензирование выберите команду Изменить примечание .

3. Появится поле для ввода комментария. Измените комментарий, а затем щелкните в любом месте за пределами поля, чтобы закрыть его.

КАК ПОКАЗАТЬ ИЛИ СКРЫТЬ ПРИМЕЧАНИЕ В EXCEL

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

2. Все примечания, которые есть в Вашей книге Excel, появятся на экране.

3. Чтобы скрыть все примечания, снова нажмите на эту команду.

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

УДАЛЕНИЕ ПРИМЕЧАНИЙ В EXCEL

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

2. На вкладке Рецензирование в группе Примечания выберите команду Удалить .

3. Примечание будет удалено.

ЗАВЕРШЕНИЕ РАБОТЫ И ЗАЩИТА РАБОЧИХ КНИГ В EXCEL

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

ПРОВЕРКА ОРФОГРАФИИ

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

1. На вкладке Рецензирование в группе Правописание нажмите команду Орфография .

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

3. Когда проверка орфографии будет завершена, появится диалоговое окно. Нажмите OK для завершения.

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

ПРОПУСК ОШИБОК

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

· Пропустить – оставляет слово без изменений.

· Пропустить все – оставляет слово без изменений, а также пропускает его во всех других случаях употребления в рабочей книге.

· Добавить в словарь – добавляет слово в словарь, таким образом, оно больше не будет отмечаться как ошибка. Перед выбором данной опции убедитесь, что слово написано правильно.

ИНСПЕКТОР ДОКУМЕНТОВ

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

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

ПРИНЦИП РАБОТЫ ИНСПЕКТОРА ДОКУМЕНТОВ

1. Откройте вкладку Файл , чтобы перейти к представлению Backstage .

2. В группе Сведения нажмите команду Поиск проблем , а затем в раскрывающемся меню выберите пунктИнспектор документов .

3. Откроется Инспектор документов . В диалоговом окне установите необходимые флажки для выбора типов содержимого, которое нужно проверить, и нажмите кнопку Проверить . В нашем примере мы оставили все пункты.

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

5. По завершению нажмите Закрыть .

ЗАЩИТА РАБОЧЕЙ КНИГИ

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

КАК ЗАЩИТИТЬ КНИГУ

1. Откройте вкладку Файл , чтобы перейти к представлению Backstage .

2. В группе Сведения нажмите команду Защитить книгу .

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

4. Появится напоминание о том, что книга будет помечена как окончательная. Нажмите OK , чтобы сохранить.

5. Появится еще одно напоминание. Нажмите OK .

6. Теперь Ваша рабочая книга помечена как окончательная.

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

УСЛОВНОЕ ФОРМАТИРОВАНИЕ В EXCEL

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

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

ОСНОВНЫЕ СВЕДЕНИЯ ОБ УСЛОВНОМ ФОРМАТИРОВАНИИ

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

СОЗДАНИЕ ПРАВИЛА УСЛОВНОГО ФОРМАТИРОВАНИЯ

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

1. Выделите ячейки, по которым требуется выполнить проверку. В нашем случае это диапазон B2:E9.

2. На вкладке Главная нажмите команду Условное форматирование . Появится выпадающее меню.

3. Выберите необходимое правило условного форматирования. Мы хотим выделить ячейки, значение которыхБольше $4000.

4. Появится диалоговое окно. Введите необходимое значение. В нашем случае это 4000 .

5. Укажите стиль форматирования в раскрывающемся списке. Мы выберем Зеленую заливку и темно-зеленый текст . Затем нажмите OK .

6. Условное форматирование будет применено к выделенным ячейкам. Теперь без особого труда можно увидеть, кто из продавцов выполнил месячный план в $4000.

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

УДАЛЕНИЕ УСЛОВНОГО ФОРМАТИРОВАНИЯ

1. Нажмите команду Условное форматирование . Появится выпадающее меню.

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

3. Условное форматирование будет удалено.

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

ПРЕДУСТАНОВЛЕННЫЕ СТИЛИ УСЛОВНОГО ФОРМАТИРОВАНИЯ

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

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

2. Цветовые шкалы изменяют цвет каждой ячейки, основываясь на их значениях. Каждая цветовая шкала использует двух или трехцветный градиент. Например, в цветовой шкале "Красный-желтый-зеленый" максимальные значения выделены красным, средние – желтым, а минимальные - зеленым.

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

ИСПОЛЬЗОВАНИЕ ПРЕДУСТАНОВЛЕННЫХ СТИЛЕЙ

1. Выделите ячейки для создания правила условного форматирования.

2. Нажмите команду Условное форматирование . Появится выпадающее меню.

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

4. Условное форматирование будет применено к выделенным ячейкам.

ОБЩИЕ СВЕДЕНИЯ О СВОДНЫХ ТАБЛИЦАХ В EXCEL

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

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

ИСПОЛЬЗОВАНИЕ СВОДНЫХ ТАБЛИЦ ДЛЯ ОТВЕТА НА ВОПРОСЫ

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

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

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

СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ

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

2. На вкладке Вставка щелкните команду Сводная таблица .

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

4. На новом листе появится пустая сводная таблица и список полей.

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

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

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

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

Если Вы измените какие-либо данные на рабочем листе источника данных, сводная таблица не обновится автоматически. Чтобы обновить ее вручную, выберите сводную таблицу, перейдите на вкладку Анализ и нажмитеОбновить .

СВЕДЕНИЕ ДАННЫХ, ФИЛЬТРЫ, СРЕЗЫ И СВОДНЫЕ ДИАГРАММЫ

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

СВЕДЕНИЕ ДАННЫХ В EXCEL

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

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

ИЗМЕНЕНИЕ СТРОК

1. Щелкните по любому полю в области Строки и перетащите его за пределы этой области. Поле исчезнет.

2. Перетащите новое поле из списка полей в область Строки Месяц .

3. Сводная таблица будет скорректирована под новые данные. Теперь в ней отображаются общие суммы продаж по месяцам.

ДОБАВЛЕНИЕ СТОЛБЦА

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

Колонны . В нашем примере мы воспользуемся полем Регион .

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

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

ДОБАВЛЕНИЕ ФИЛЬТРА

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

1. Перетащите поле из списка полей в область Фильтры . В данном примере мы воспользуемся полем Продавец .

2. Фильтр появится над сводной таблицей. Щелкните кнопку со стрелкой, а затем установите флажок Выделить несколько элементов .

3. Снимите флажки с тех элементов, которые Вы не хотели бы включать в сводную таблицу. В нашем примере мы снимем флажки с 2-х продавцов, затем нажмем OK .

4. Сводная таблица будет скорректирована с учетом изменений.

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

ДОБАВЛЕНИЕ СРЕЗА

2. На вкладке Анализ щелкните команду Вставить срез .

3. Появится диалоговое окно Вставка срезов . Выберите необходимое поле. В нашем примере мы выберем полеПродавец , а затем нажмем OK .

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

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

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

СВОДНЫЕ ДИАГРАММЫ

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

СОЗДАНИЕ СВОДНОЙ ДИАГРАММЫ

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

1. Выделите любую ячейку сводной таблицы.

2. На вкладке Вставка , щелкните команду Сводная диаграмма .

3. Откроется диалоговое окно Вставка диаграммы . Выберите подходящий вид и макет диаграммы, затем нажмитеOK .

4. Сводная диаграмма появится на листе Excel.

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

10.03.2015 19:34

АНАЛИЗ “ЧТО ЕСЛИ” В EXCEL

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

ПОДБОР ПАРАМЕТРА

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

КАК ИСПОЛЬЗОВАТЬ ПОДБОР ПАРАМЕТРА (ПРИМЕР 1):

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

На изображении ниже видно, что Ваши баллы за первые два задания (тест и письменная работа) составляют 58, 70, 72 и 60. Несмотря на то, что мы не знаем, каким будет балл за последнее задание (тестирование 3), мы можем написать формулу, которая вычислит средний балл сразу за все задания. Все, что нам необходимо, это вычислить среднее арифметическое для всех пяти оценок. Для этого введите выражение =СРЗНАЧ(B2:B6) в ячейку B7. После того как Вы примените Подбор параметра к решению этой задачи, в ячейке B6 отобразится минимальный балл, который необходимо получить, чтобы поступить в учебное заведение.

1. Выберите ячейку, значение которой необходимо получить. Каждый раз при использовании инструмента Подбор параметра , Вам необходимо выбирать ячейку, которая уже содержит формулу или функцию. В нашем случае мы выберем ячейку B7, поскольку она содержит формулу =СРЗНАЧ(B2:B6) .

2. На вкладке Данные выберите команду Анализ "что если" , а затем в выпадающем меню нажмите Подбор параметра .

o Установить в ячейке - ячейка, которая содержит требуемый результат. В нашем случае это ячейка B7 и мы уже выделили ее.

o Значение - требуемый результат, т.е. результат, который должен получиться в ячейке B7. В нашем примере мы введем 70, поскольку нужно набрать минимум 70 баллов, чтобы поступить.

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

4. Выполнив все шаги, нажмите ОК .

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

6. Результат появится в указанной ячейке. В нашем примере Подбор параметра установил, что требуется получить минимум 90 баллов за последнее задание, чтобы пройти дальше.

КАК ИСПОЛЬЗОВАТЬ ПОДБОР ПАРАМЕТРА (ПРИМЕР 2):

Давайте представим, что Вы планируете событие и хотите пригласить такое количество гостей, чтобы не превысить бюджет в $500. Можно воспользоваться Подбором параметра , чтобы вычислить число гостей, которое можно пригласить. В следующем примере ячейка B4 содержит формулу =B1+B2*B3 , которая суммирует общую стоимость аренды помещения и стоимость приема всех гостей (цена за 1 гостя умножается на их количество).

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

2. На вкладке Данные выберите команду Анализ "что если" , а затем в выпадающем меню нажмите Подбор параметра .

3. Появится диалоговое окно с тремя полями:

o Установить в ячейке - ячейка, которая содержит требуемый результат. В нашем примере ячейка B4 уже выделена.

o Значение - требуемый результат. Мы введем 500, поскольку допустимо потратить $500.

o Изменяя значение ячейки - ячейка, куда Excel выведет результат. Мы выделим ячейку B3, поскольку требуется вычислить количество гостей, которое можно пригласить, не превысив бюджет в $500.

4. Выполнив все пункты, нажмите ОК .

5. Диалоговое окно Результат подбора параметра сообщит, удалось ли найти решение. Нажмите OK .

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

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

ДРУГИЕ ТИПЫ АНАЛИЗА "ЧТО ЕСЛИ"

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

· Диспетчер сценариев позволяет подставлять значения сразу в несколько ячеек (до 32). Вы можете создать несколько сценариев, а затем сравнить их, не изменяя значений вручную. В следующем примере мы используем сценарии, чтобы сравнить несколько различных мест для проведения мероприятия.

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

КАК ЗАДАТЬ ПРОСТОЕ ЛОГИЧЕСКОЕ УСЛОВИЕ В EXCEL

В Excel существует множество различных функций, работа которых построена на проверке логических условий. Например, это функции ЕСЛИ, СЧЕТЕСЛИ, СУММЕСЛИ и т.д. Также логические условия можно задавать в обычных формулах, если необходимо получить утвердительный ответ: Да или Нет . К примеру, задавая простые логические условия, Вы можете ответить на такие вопросы:

· 5 больше 8?

· Содержимое ячейки A5 меньше 8?

· А может равно 8?

ОПЕРАТОРЫ СРАВНЕНИЯ В EXCEL

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

КАК ЗАДАТЬ УСЛОВИЕ В EXCEL

Операторы сравнения позволяют задавать условия, которые возвращают логические значения ИСТИНА или ЛОЖЬ. Примеры использования логических условий представлены ниже:

=A1=B1 - Данное условие вернет ИСТИНА, если значения в ячейках A1 и B1 равны, или ЛОЖЬ в противном случае. Задавая такое условие, можно сравнивать текстовые строки без учета регистра. К примеру, сравнивая "ЯНВАРЬ" и "январь" формула возвратит ИСТИНА.

=A1>B1 - Следующая формула возвратит ИСТИНА, если значение ячейки А1 больше, чем в B1. В противном случае формула вернет ЛОЖЬ. Такие сравнения можно задавать и при работе с текстом.

Например, если в ячейке A1 хранится значение "Апельсин", а в B1 – "Арбуз", то формула вернет ЛОЖЬ, поскольку в алфавитном порядке "Арбуз" находится ниже, чем "Апельсин". Чем ниже, тем больше.

=A1<=B1 - Формула вернет ИСТИНА, если значение ячейки A1 меньше или равно значению в ячейке B1. Иначе результатом будет ЛОЖЬ.

=A1<>B1 - Формула вернет ИСТИНА, если значения ячеек A1 и B1 не равны. В противном случае – ЛОЖЬ.

В Excel существуют логические функции ИСТИНА() и ЛОЖЬ() , которые не имеют аргументов. Данные функции существуют в основном для обеспечения совместимости с другими электронными таблицами. Вы можете вводить значения ИСТИНА и ЛОЖЬ прямо в ячейки или формулы, не используя форму записи функции, Excel все прекрасно поймет.

Если Вы уверены, что уже достаточно хорошо освоили эту тему, можете обратиться к статье Используем логические функции Excel для задания сложных условий, чтобы научиться задавать условия, используя различные логические функции, например И() или ИЛИ() .

ÈÑÏÎËÜÇÓÅÌ ËÎÃÈ×ÅÑÊÈÅ ÔÓÍÊÖÈÈ EXCEL ÄËß ÇÀÄÀÍÈß ÑËÎÆÍÛÕ ÓÑËÎÂÈÉ

 ïðîøëîì óðîêå ìû óçíàëè, êàê çàäàâàòü ïðîñòûå ëîãè÷åñêèå óñëîâèÿ â Excel.  ýòîì óðîêå ìû ïðîäîëæèì èçó÷åíèå, íî ïîïðîáóåì óñëîæíèòü çàäà÷ó è âîñïîëüçîâàòüñÿ ëîãè÷åñêèìè ôóíêöèÿìè Excel, ÷òîáû íàó÷èòüñÿ çàäàâàòü áîëåå ñëîæíûå óñëîâèÿ.

Èòàê, â Excel ñóùåñòâóåò 4 ëîãè÷åñêèõ ôóíêöèè, êîòîðûå ïîçâîëÿþò çàäàâàòü ñëîæíûå óñëîâèÿ. Ðàññìîòðèì êàæäóþ èç ýòèõ ôóíêöèé:

ËÎÃÈ×ÅÑÊÀß ÔÓÍÊÖÈß È()

Âîçâðàùàåò ÈÑÒÈÍÀ, åñëè âñå èñïîëüçóåìûå óñëîâèÿ èñòèííû, èëè ËÎÆÜ, åñëè õîòÿ áû îäíî èç íèõ ëîæíîå.

=È(A1>B1; A2<>25)

Äàííàÿ ôîðìóëà ïðîâåðÿåò ñðàçó äâà óñëîâèÿ è âîçâðàùàåò ÈÑÒÈÍÀ, åñëè îáà èç íèõ âûïîëíÿþòñÿ. Â ñëåäóþùåì ïðèìåðå îáà óñëîâèÿ èñòèííû, ïîýòîìó è ôóíêöèÿ âîçâðàùàåò ÈÑÒÈÍÀ:

Ëîãè÷åñêèå ôóíêöèè â Excel

 äàííîì ïðèìåðå ïåðâîå óñëîâèå ëîæíî, à âòîðîå èñòèííî, ïîýòîìó ôóíêöèÿ âåðíóëà ËÎÆÜ:

Ëîãè÷åñêèå ôóíêöèè â Excel

ËÎÃÈ×ÅÑÊÀß ÔÓÍÊÖÈß ÈËÈ()

Âîçâðàùàåò ÈÑÒÈÍÀ, åñëè õîòÿ áû îäíî èç óñëîâèé èñòèííî, èëè ËÎÆÜ, åñëè âñå óñëîâèÿ ëîæíû.

=ÈËÈ(A1>B1; A2>B2; A3>B3)

Äàííàÿ ôîðìóëà ïðîâåðÿåò òðè óñëîâèÿ è âåðíåò ëîãè÷åñêîå ÈÑÒÈÍÀ, åñëè õîòÿ áû îäíî èç íèõ âûïîëíÿåòñÿ.  ïðèìåðå íà ðèñóíêå íèæå ëèøü ïîñëåäíåå óñëîâèå èñòèííî, íî ýòîãî äîñòàòî÷íî, ÷òîáû è ôóíêöèÿ ÈËÈ âåðíóëà çíà÷åíèå ÈÑÒÈÍÀ.

Ëîãè÷åñêèå ôóíêöèè â Excel

Ïîñêîëüêó â ýòîì ïðèìåðå âñå óñëîâèÿ ëîæíû, òî è ôóíêöèÿ âåðíóëà ËÎÆÜ.

Ëîãè÷åñêèå ôóíêöèè â Excel

ËÎÃÈ×ÅÑÊÀß ÔÓÍÊÖÈß ÍÅ()

Ìåíÿåò ëîãè÷åñêîå çíà÷åíèå ñâîåãî àðãóìåíòà íà ïðîòèâîïîëîæíîå. Åñëè àðãóìåíò èìååò çíà÷åíèå ÈÑÒÈÍÀ, ôóíêöèÿ ìåíÿåò åãî íà ËÎÆÜ. Åñëè æå çíà÷åíèå àðãóìåíòà ËÎÆÜ, òî ôóíêöèÿ ìåíÿåò åãî íà ÈÑÒÈÍÓ.

Íàïðèìåð, íà ñëåäóþùåì ðèñóíêå âèäíî, ÷òî ÷èñëî â ÿ÷åéêå A1 áîëüøå, ÷åì â A2. Ñîîòâåòñòâåííî, âûðàæåíèå A1>B1 – èìååò ëîãè÷åñêîå çíà÷åíèå ÈÑÒÈÍÀ. Ïðèìåíèâ ôóíêöèþ ÍÅ â ôîðìóëå, ìû èçìåíèëè åãî íà ïðîòèâîïîëîæíîå.

Ëîãè÷åñêèå ôóíêöèè â Excel

ËÎÃÈ×ÅÑÊÀß ÔÓÍÊÖÈß ÈÑÊËÈËÈ()

Âîçâðàùàåò ëîãè÷åñêîå "èñêëþ÷àþùåå èëè" âñåõ àðãóìåíòîâ. Ôóíêöèÿ âåðíåò ÈÑÒÈÍÀ, åñëè ÷èñëî èñòèííûõ óñëîâèé íå÷åòíîå, è ËÎÆÜ, åñëè ÷èñëî èñòèííûõ óñëîâèé ÷åòíîå. Åñëè âñå àðãóìåíòû ôóíêöèè ÈÑÊËÈËÈ èìåþò çíà÷åíèå ËÎÆÜ, òî è ôóíêöèÿ âîçâðàòèò ËÎÆÜ.

=ÈÑÊËÈËÈ(A1>B1; A2>B2; A3>B3; A4>B4)

 ñëåäóþùåì ïðèìåðå ôîðìóëà âåðíåò ÈÑÒÈÍÀ, ïîñêîëüêó òðè óñëîâèÿ èç ÷åòûðåõ èñòèííû (íå÷åòíîå êîëè÷åñòâî):

Ëîãè÷åñêèå ôóíêöèè â Excel

 äàííîì ïðèìåðå ôîðìóëà âåðíåò ËÎÆÜ, ïîñêîëüêó âñå 4 óñëîâèÿ èñòèííû (÷åòíîå êîëè÷åñòâî):

Ëîãè÷åñêèå ôóíêöèè â Excel

 äàííîì ïðèìåðå ôîðìóëà âåðíåò ËÎÆÜ, ïîñêîëüêó íå îäíî èç óñëîâèé íå âûïîëíÿåòñÿ:

Ëîãè÷åñêèå ôóíêöèè â Excel

 äàííîì óðîêå Âû óçíàëè, êàê çàäàþòñÿ ñëîæíûå óñëîâèÿ â Excel ñ ïîìîùüþ ëîãè÷åñêèõ ôóíêöèé. Ýòèõ çíàíèé äîëæíî õâàòèòü íà áîëüøèíñòâî Âàøèõ çàäà÷. Òàê æå íå ñòîèò çàáûâàòü, ÷òî Âû ìîæåòå âêëàäûâàòü ëîãè÷åñêèå ôóíêöèè äðóã â äðóãà è ïîëó÷àòü åùå áîëåå ñëîæíûå è çàïóòàííûå óñëîâèÿ. Ïðàâäà, Âàì ýòî âðÿä ëè ïîíàäîáèòñÿ.

Äëÿ òåõ, êîãî çàèíòåðåñîâàëà òåìà ëîãè÷åñêèõ ôóíêöèé, ïðåäëàãàþ ïîñåòèòü óðîê Ôóíêöèÿ ÅÑËÈ â Excel íà ïðîñòîì ïðèìåðå. Äóìàþ, ýòî áóäåò ëîãè÷åñêèì ïðîäîëæåíèåì ýòîé ñòàòüè. Âñåãî Âàì äîáðîãî è óñïåõîâ â èçó÷åíèè Excel. ФУНКЦИЯ ЕСЛИ В EXCEL НА ПРОСТОМ ПРИМЕРЕ

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

КОРОТКО О СИНТАКСИСЕ

Функция ЕСЛИ имеет всего три аргумента:

=ЕСЛИ(заданное_условие; значение_если_ИСТИНА; значение_если_ЛОЖЬ)

Первый аргумент – это условие, благодаря которому формула может принимать решения. Условие проверяется в самую первую очередь и способно вернуть всего два значения – ИСТИНА или ЛОЖЬ. Если условие истинно, то формула вернет второй аргумент, в противном случае третий.

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

Обратимся к примеру, приведенному на рисунках ниже. В данном примере функция ЕСЛИ в первую очередь проверят условие A1>25 . Если это так, то формула возвратит текстовую строку "больше 25", в любом другом случае - "меньше или равно 25".

Функция ЕСЛИ является очень гибкой и ее можно применять в различных ситуациях. Рассмотрим еще один пример. В таблице ниже приведены результаты переаттестации сотрудников фирмы:

В столбец C нам необходимо выставить результат экзамена, который должен содержать всего два варианта: Сдал илиНе сдал . Те, кто набрал более 45 баллов – сдали экзамен, остальные нет.

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

2. Введите в нее выражение: =ЕСЛИ(B3>45; "Сдал"; "Не сдал") и нажмите Enter .

3. Данная формула сравнивает значение в ячейке B3 с числом 45, если значение больше 45, то возвращает строку "Сдал", иначе "Не сдал".

4. Скопировав формулу в остальные ячейки таблицы, можно увидеть, что 2 человека из 5 не прошли переаттестацию.

ФУНКЦИЯ ЕСЛИ И НЕСКОЛЬКО УСЛОВИЙ

Функции ЕСЛИ можно вкладывать друг в друга, если необходимо расширить варианты принятия решений в Excel. Например, для рассмотренного ранее случая переаттестации сотрудников, требуется проставить не результат, а оценку из ряда: Отлично, Хорошо и Плохо. Оценка Отлично ставится при количестве баллов более 60, оценка Хорошо при более 45 и оценка Плохо в остальных случаях.

1. Чтобы решить эту задачу, введем в ячейку C3 следующую формулу:

=ЕСЛИ(B3>60;"Отлично";ЕСЛИ(B2>45;"Хорошо";"Плохо")) и нажмем Enter .

2. Данная формула обрабатывает сразу два условия. Сначала проверяется первое условие: B3>60 . Если оно истинно, то формула возвращает значение "Отлично", а остальные условия не обрабатываются. Если первое условие ложно, то функция ЕСЛИ переходит ко второму: B2>45 . Если второе условие истинно, то формула возвращает значение "Хорошо", а если ложно, то "Плохо".

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

Как видите, вместо второго и третьего аргументов функции ЕСЛИ можно подставлять новые функции ЕСЛИ , тем самым расширяя число условий, которое формула может обработать. Таким образом, Вы можете создать нужное количество вложений. Правда есть очевидный недостаток такой конструкции, после 3-5 вложений формула станет нечитаемой и громоздкой, и с ней будет невозможно работать.

В Excel существуют более благородные инструменты для обработки большого количества условий, например,функция ВПР или ПРОСМОТР .

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

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

Видео по фильтрации и сортировке данных в Excel

Виды фильтров в Excel

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

Всего в Экселе есть 2 способа фильтрации: автофильтр и расширенный фильтр. Запустить их можно через панель меню, щелкнув «Данные — Фильтр». С помощью первого варианта осуществляется быстрый отбор необходимой информации с простыми критериями поиска. В режиме автофильтра заглавная строка таблицы в каждой колонке будет содержать кнопку со стрелочкой, кликнув на которую можно указать критерии для отбора. Для каждого столбца можно установить свои настройки. В данном режиме можно установить следующие параметры:

Чтобы удалить фильтр из одной колонки, нужно кликнуть в перечне элементов на пункт «Все». Если нужно отменить его для всей таблицы, необходимо выбрать в панели меню пункты «Данные — Фильтр — Отобразить все». Удаление автофильтра осуществляется так же, как и его запуск.

Расширенный фильтр

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

Второй вариант фильтрации предоставляет пользователю больше возможностей для отбора необходимых данных.Чтобы запустить расширенную опцию, необходимо создать копию заголовков таблицы (т.е. просто скопировать шапку). Это будет диапазон условий. Затем надо заполнить этот диапазон критериями отбора. Но тут важно придерживаться правил: если нужно, чтобы значения отбирались по двум параметрам (к примеру, фамилия студента и оценка), то условия записываются в одну строку; если же критерии будут отбираться в режиме «ИЛИ» (марка машины или объем двигателя), тогда они записываются в разные строки.

Допустим, есть таблица с 2 колонками — наименование товара и количество. Всего товаров 3 — бананы, апельсины, мандарины, а количество — 10, 20 и 15 штук соответственно. После того как будет скопирована шапка, можно создать условие, например, показать товары, количество которых меньше или равно 15. То есть под скопированной шапкой в колонке «Кол-во» надо написать <=15. Затем надо запустить расширенный фильтр, указать исходный диапазон (исходная таблица), диапазон условий (таблица, где указано «кол-во <=15») и нажать «ОК». Исходная таблица изменится: теперь тут будут отображены только бананы (10 штук) и мандарины (15 штук).

Таким образом, принцип работы фильтрации должен быть понятен. Этим способом можно отбирать любые элементы из базы данных, насколько большой она бы ни была. Такая процедура поможет упростить работу пользователя с большими объемами данных.

Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.

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

Автофильтр и расширенный фильтр в Excel

Имеется простая таблица, не отформатированная и не объявленная списком. Включить автоматический фильтр можно через главное меню.

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

Пользоваться автофильтром просто: нужно выделить запись с нужным значением. Например, отобразить поставки в магазин №4. Ставим птичку напротив соответствующего условия фильтрации:

Сразу видим результат:

Особенности работы инструмента:

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

У расширенного фильтра гораздо больше возможностей:

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



Как сделать расширенный фильтр в Excel

Готовый пример – как использовать расширенный фильтр в Excel:

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

Как пользоваться расширенным фильтром в Excel

Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.

Заполняем меню расширенного фильтра:

Получаем таблицу с отобранными по заданному критерию строками:

Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.

Заполняем параметры фильтрации. Нажимаем ОК.

Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» - значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.

Применим инструмент «Расширенный фильтр»:

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

Основные правила:

1. Результат формулы – это критерий отбора.
2. Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
3. Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
4. Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.

Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.

Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).

В таблице остались только те строки, где значения в столбце «Количество» выше среднего.

Чтобы оставить в таблице лишь неповторяющиеся строки, в окне «Расширенного фильтра» поставьте птичку напротив «Только уникальные записи».

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

Рассмотрим, как можно искать информацию в MS Excel, используя фильтры.

Для этого откроем эксель и набросаем в нем небольшую таблицу.

Выделите любую ячейку в строке с заголовками, затем перейдите на вкладку «Данные » и щелкните на кнопке «Фильтр »:

В строке с заголовками нашей таблицы в каждом столбце появятся «стрелки».

Обратите внимание, что если в Вашей таблице нет строки с заголовками, то Excel, автоматически вставит фильтр в первую строку с данными:

Подготовительный этап завершен. Можно приступать к поиску информации.

Основы работы с фильтрами

Применение фильтров к таблице

Щелкните на значке в столбце «Менеджер». Откроется следующее меню:

В данном меню с помощью флажков Вы можете отмечать те элементы, по которым необходимо фильтровать данные.

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

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

Например, Вам нужно отметить только 2 значения из нескольких десятков. Снимать флажок вручную с каждой позиции кроме нужных достаточно затратно по времени. Для ускорения этого процесса снимите флажок с пункта «(Выделить все)». При этом снимутся все остальные флажки. Теперь можно отметить только те пункты, которые Вам нужны.

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

Например, Вам необходимо найти все заказы менеджера Иванова от 18.01.2014.

Для начала щелкните на в столбце «Менеджер» и выберите Иванова.

Теперь щелкните на в столбце «Дата отгрузки», снимите флажок с «(Выделить все)» и выберите 18.01.2014 или введите в строке поиска 18 и нажмите «ОК».

Таблица примет следующий вид:

Аналогичным образом Вы можете продолжить фильтровать данные по столбцам «Описание», «Кол-во» и т.д.

Обратите внимание, что в столбцах, по которым был применен фильтр, значокменяется на .

Таким образом Вы всегда будете знать по каким столбцам происходит фильтрация данных.

Отмена фильтра

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

Если необходимо снять фильтр только с одного столбца, оставив фильтры по другим, то щелкните на значке данного столбца, напр., «Дата отгрузки» и щелкните на пункте «Удалить фильтр с <Название столбца>»:

или

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

До

После

Дополнительные настройки фильтров

В зависимости от типа содержимого столбцов у фильтров появляются дополнительные опции.

Текстовые фильтры

Щелкните на значке столбца «Менеджер», наведите курсор на «Текстовые фильтры», дождитесь появления меню и выберите любой из критериев отбора или пункт «Настраиваемый фильтр…». Появится следующее окно:

• 1. Условия «равно» или «не равно» предполагают, что искомое выражение стопроцентно совпадает с содержанием ячейки. Критерий «равно» оставляет в таблице только те строки, в которых содержится выбранное значение. Соответственно, критерий «не равно» оставляет все значения, кроме выбранного. Для упрощения задачи Вы можете выбрать нужное значение из выпадающего списка:

• 2. Условия «больше» и «меньше» предполагают, что в таблице останутся значения, которые по алфавиту начинаются с более ранней или более поздней буквы. Например, если выбрать значение «Иванов» при опции «больше», то в таблице останутся только те ячейки, которые начинаются на букву «Й»(Картов, Йогуртов и т.п.). А при опции «меньше» - значения на букву «З» (Захаров, Букин).
• 3. Единственное отличие условий «больше или равно» и «меньше или равно» от предыдущего пункта в том, что в фильтр включает и выбранное значение.
• 4. Если необходимо найти все значения, которые начинаются на «Ива» , то используйте условие «начинается с» , а если хотите узнать, сколько в таблице значений, оканчивающихся на «рович» , то выберите опцию «заканчивается на» .
• 5. Соответственно, условия «не начинается с» и «не заканчивается на» предполагают, что Вам не надо отображать в таблице значения, содержащие искомую фразу.
• 6. При выборе условий «содержит» или «не содержит» можно указать любую фразу или сочетание букв, которые необходимо включить или исключить из фильтра. Отличие этого пункта от пунктов 1, 4 и 5, в том, что искомая фраза может находится в любом месте ячейки. Например, задав в качестве фильтра «Ива», в результате получим «Иванов Алексей», «Сергей Иваровский», «кривая» и т.п.

Числовые фильтры

Большинство условий те же самые, что и при текстовых фильтрах. Рассмотрим только новые.

• 1. Условие «между» . При выборе данного условия, в появившемся окне сразу же устанавливаются нужные критерии, что облегчает Вам задачу:

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

Фильтр по дате

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

И еще немного о фильтрах

Есть еще один способ фильтрации данных. Немного преобразуем нашу таблицу:

Как видите, мы ее раскрасили.

Теперь, например, нам необходимо найти все строки с Красоткиным. Щелкните правой кнопкой на ячейке с данным человеком и в появившемся меню выберите пункт «Фильтр». В новом меню есть несколько новых опций. В данном примере нам нужен пункт «Фильтр по значению…» .

Если выбрать условие «Фильтр по цвету…» , то в таблице останутся строки с ячейками того же цвета, что и активная ячейка (желтая заливка).

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

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

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

Ошибки