В экселе отфильтровать столбец по ключевому слову. Используем автофильтр в 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!

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

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

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

Например, в предложенной ниже таблице следует отсортировать сотрудников по алфавиту. Становимся в любую ячейку столбца «Имя», и жмем на кнопку «Сортировка и фильтр». Чтобы имена упорядочить по алфавиту, из появившегося списка выбираем пункт «Сортировка от А до Я».

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

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

Список перестраивается в обратном порядке.

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

Настраиваемая сортировка

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

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

После этого, открывается окно настроек сортировки. Если в вашей таблице есть заголовки, то обратите внимание, чтобы в данном окне обязательно стояла галочка около параметра «Мои данные содержат заголовки».

В поле «Столбец» указываем наименование столбца, по которому будет выполняться сортировка. В нашем случае, это столбец «Имя». В поле «Сортировка» указывается, по какому именно типу контента будет производиться сортировка. Существует четыре варианта:

• Значения;
• Цвет ячейки;
• Цвет шрифта;
• Значок ячейки.

Но, в подавляющем большинстве случаев, используется пункт «Значения». Он и выставлен по умолчанию. В нашем случае, мы тоже будем использовать именно этот пункт.

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

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

Появляется ещё один набор полей, который следует заполнить уже для сортировки по другому столбцу. В нашем случае, по столбцу «Дата». Так как в данных ячеек установлен формат даты, то в поле «Порядок» мы устанавливаем значения не «От А до Я», а «От старых к новым», или «От новых к старым».

Таким же образом, в этом окне можно настроить, при необходимости, и сортировку по другим столбцам в порядке приоритета. Когда все настройки выполнены, жмем на кнопку «OK».

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

Но, это ещё не все возможности настраиваемой сортировки. При желании, в этом окне можно настроить сортировку не по столбцам, а по строкам. Для этого, кликаем по кнопке «Параметры».

В открывшемся окне параметров сортировки, переводим переключатель из позиции «Строки диапазона» в позицию «Столбцы диапазона». Жмем на кнопку «OK».

Теперь, по аналогии с предыдущим примером, можно вписывать данные для сортировки. Вводим данные, и жмем на кнопку «OK».

Как видим, после этого, столбцы поменялись местами, согласно введенным параметрам.

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

Фильтр

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

Чтобы воспользоваться данной функцией, становимся на любую ячейку в таблице (а желательно в шапке), опять жмем на кнопку «Сортировка и фильтр» в блоке инструментов «Редактирование». Но, на этот раз в появившемся меню выбираем пункт «Фильтр». Можно также вместо этих действий просто нажать сочетание клавиш Ctrl+Shift+L.

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

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

Когда процедура выполнена, жмем на кнопку «OK».

Как видим, в таблице остались только строки с именем работника Николаева.

Усложним задачу, и оставим в таблице только данные, которые относятся к Николаеву за III квартал 2016 года. Для этого, кликаем по значку в ячейке «Дата». В открывшемся списке, снимаем галочки с месяцев «Май», «Июнь» и «Октябрь», так как они не относятся к третьему кварталу, и жмем на кнопку «OK».

Как видим, остались только нужные нам данные.

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

Если же вы хотите сбросить фильтр в целом по таблице, тогда нужно нажать кнопку «Сортировка и фильтр» на ленте, и выбрать пункт «Очистить».

Если нужно полностью удалить фильтр, то, как и при его запуске, в этом же меню следует выбрать пункт «Фильтр», или набрать сочетание клавиш на клавиатуре Ctrl+Shift+L.

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

Умная таблица

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

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

После этого, открывается диалоговое окно, в котором можно изменить координаты таблицы. Но, если вы ранее выделили область правильно, то больше ничего делать не нужно. Главное, обратите внимание, чтобы около параметра «Таблица с заголовками» стояла галочка. Далее, просто нажать на кнопку «OK».

Если вы решили воспользоваться вторым способом, тогда тоже нужно выделить всю область таблицы, но на этот раз перейти во вкладку «Вставка». Находясь тут, на ленте в блоке инструментов «Таблицы», следует нажать на кнопку «Таблица».

После этого, как и в прошлый раз, откроется окно, где можно скорректировать координаты размещения таблицы. Жмем на кнопку «OK».

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

При нажатии на этот значок, будут доступны все те же функции, что и при запуске фильтра стандартным способом через кнопку «Сортировка и фильтр».

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

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

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

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

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

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

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

Верхняя таблица – результат фильтрации. Нижняя табличка с условиями дана для наглядности рядом.



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

Чтобы отменить действие расширенного фильтра, поставим курсор в любом месте таблицы и нажмем сочетание клавиш Ctrl + Shift + L или «Данные» - «Сортировка и фильтр» - «Очистить».

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

В таблицу условий внесем критерии. Например, такие:

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

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

Excel воспринимает знак «=» как сигнал: сейчас пользователь задаст формулу. Чтобы программа работала корректно, в строке формул должна быть запись вида: ="=Набор обл.6 кл."

После использования «Расширенного фильтра»:

Теперь отфильтруем исходную таблицу по условию «ИЛИ» для разных столбцов. Оператор «ИЛИ» есть и в инструменте «Автофильтр». Но там его можно использовать в рамках одного столбца.

В табличку условий введем критерии отбора: ="=Набор обл.6 кл." (в столбец «Название») и ="

Обратите внимание: критерии необходимо записать под соответствующими заголовками в РАЗНЫХ строках.

Результат отбора:

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

Отбор строки с максимальной задолженностью: =МАКС(Таблица1[Задолженность]).

Таким образом мы получаем результаты как после выполнения несколько фильтров на одном листе Excel.

Как сделать несколько фильтров в Excel?

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

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

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

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

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

Как сделать фильтр в Excel по строкам?

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

Приводим примеры строковых критериев расширенного фильтра в Excel:

Чтобы привести пример как работает фильтр по строкам в Excel, создадим табличку.

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

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

Шаг 1: Создание таблицы с условиями отбора

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

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

Шаг 2: Запуск расширенного фильтра

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

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

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

Как добавить

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

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

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

Если Вас интересует вопрос, как сделать таблицу в Эксель , перейдите по ссылке и прочтите статью по данной теме.

Как работает

Теперь давайте рассмотрим, как работает фильтр в Эксель. Для примера воспользуемся следующими данными. У нас есть три столбца: «Название продукта» , «Категория» и «Цена» , к ним будем применять различные фильтры.

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

Например, оставим в «Категории» только фрукты. Снимаем галочку в поле «овощ» и нажимаем «ОК» .

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

Как удалить

Если Вам нужно удалить фильтр данных в Excel, нажмите в ячейке на соответствующий значок и выберите из меню «Удалить фильтр с (название столбца)» .

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

Использование фильтра

Числовой

Применим «Числовой…» к столбцу «Цена» . Кликаем на кнопку в верхней ячейке и выбираем соответствующий пункт из меню. Из выпадающего списка можно выбрать условие, которое нужно применить к данным. Например, отобразим все товары, цена которых ниже «25» . Выбираем «меньше».

В соответствующем поле вписываем нужное значение. Для фильтрации можно применять несколько условий, используя логическое «И» и «ИЛИ» . При использовании «И» – должны соблюдаться оба условия, при использовании «ИЛИ» – одно из заданных. Например, можно задать: «меньше» – «25» – «И» – «больше» – «55» . Таким образом, мы исключим товары, цена которых находится в диапазоне от 25 до 55.

В примере у меня получилось так. Здесь отображены все данные с «Ценой» ниже 25.

Текстовый

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

Оставим в таблице продукты, которые начинаются с «ка» . В следующем окне, в поле пишем: «ка*» . Нажимаем «ОК» .

«*» в слове, заменяет последовательность знаков. Например, если задать условие «содержит» – «с*л» , останутся слова: стол, стул, сокол и так далее. «?» заменит любой знак. Например, «б?тон» – батон, бутон, бетон. Если нужно оставить слова, состоящие из 5 букв, напишите «?????» .

Вот так я оставила нужные «Названия продуктов» .

По цвету ячейки

Фильтр можно настроить по цвету текста или по цвету ячейки.

Сделаем «Фильтр по цвету» ячейки для столбика «Название продукта» . Кликаем по кнопочке со стрелкой и выбираем из меню одноименный пункт. Выберем красный цвет.

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

По цвету текста

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

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