1 Copyright © 2005 In. Bev – All

1 Copyright © 2005 In. Bev – All rights reserved. BR I GHT project November 26 th, 2010 Excel Review by Marina Perepechenova, BRIGHT, Logistic Specialist

2 Month Results & Outlook template v 5. 1. ppt. Agenda Сводные Таблицы Выпадающиие списки Условное форматирование Формулы массива Горячие клавиши Excel. Основные функции Excel

3 Copyright © 2005 In. Bev – All rights reserved. Сводная таблица (Pivot Table) представляет собой интерактивную таблицу, с помощью которой можно быстро объединять и анализировать большие объемы данных. Большой объем разнородных данных затрудняет анализ с использованием простых средств (фильтры, сортировки, промежуточные итоги и т. п. ). Сводные таблицы справляются с такими задачами без особых проблем. Сводные таблицы позволяют быстро производить необходимые выборки из исходной таблицы и обрабатывать данные. Сводная таблица имеет 4 области: область строк; область столбцов; область страниц; область данных. В областях таблицы находятся поля. Поле — категория (группа) данных, извлекаемых из одного столбца исходной таблицы. Название поля извлекается из верхней ячейки столбца исходной таблицы. Поля состоят из элементов. Элементы представляют собой записи из столбцов исходных данных. В областях строк, столбцов и страниц находятся поля, для элементов которых производятся вычисления над элементами полей, помещенных в область данных. Обычно область данных содержит определенные числа, которые суммируются функцией СУММ. Если в сводной таблице содержится несколько полей строк, то поле, название которого расположено в левом верхнем углу области строк, является внешним , а остальные поля — внутренними. Если в сводной таблице содержится несколько полей столбцов, то поле, название которого расположено в левом верхнем углу области столбцов, является внешним, а остальные поля — внутренними. Если таблица содержит более одного поля в области данных, для доступа ко всем полям данных отображается одна кнопка Данные. Microsoft Exce l Workshe e t

4 Copyright © 2005 In. Bev – All rights reserved. Создание Сводных Таблиц: Шаг 1. Откуда данные и что надо на выходе? Ставим активную ячейку в таблицу с данными (в любое место списка) и жмем в меню Данные — Сводная таблица (Data — Pivot. Table and Pivot. Chart. Report). Запускается трехшаговый Мастер сводных таблиц. На этом шаге необходимо выбрать откуда будут взяты данные для сводной таблицы. В нашем с Вами случае думать нечего — «в списке или базе данных Microsoft Excel». Но. В принципе, данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel «понимает» практически все существующие типы баз данных, поэтому с совместимостью больших проблем скорее всего не будет. Вариант «в нескольких диапазонах консолидации» применяется, когда список, по которому строится сводная таблица, разбит на несколько подтаблиц, и их надо сначала объединить (консолидировать) в одно целое. Четвертый вариант «в другой сводной таблице. . . » нужен только для того, чтобы строить несколько различных отчетов по одному списку и не загружать при этом список в оперативную память каждый раз. Вид отчета — на Ваш вкус — только таблица или таблица сразу с диаграммой.

5 Copyright © 2005 In. Bev – All rights reserved Шаг 2. Выделите исходные данные, если нужно На втором шаге необходимо выделить диапазон с данными, но, скорее всего, даже этой простой операции делать не придется — как правило Excel делает это сам. Шаг 3. Куда поместить сводную таблицу ? На третьем последнем шаге нужно только выбрать местоположение для будущей сводной таблицы. Лучше для этого выбирать отдельный лист — тогда нет риска что сводная таблица «перехлестнется» с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку Готово и переходим к самому интересному — этапу конструирования нашего отчета. Создание Сводных Таблиц:

6 Copyright © 2005 In. Bev – All rights reserved Работа с макетом То, что Вы увидите, нажав кнопку Готово называется макет (layout) сводной таблицы: Работать с ним несложно — надо перетаскивать мышью названия столбцов (полей) из окна Списка полей сводной таблицы в области строк , столбцов , страниц и данных макета. Единственная тонкость — делайте это поточнее, не промахнитесь! Поехали. . .

7 Copyright © 2005 In. Bev – All rights reserved

8 Copyright © 2005 In. Bev – All rights reserved. В процессе перетаскивания сводная таблица у Вас на глазах начнет менять вид, отображая те данные, которые Вам необходимы. Перебросив все пять нужных нам полей из списка, Вы должны получить практически готовый отчет. Останется его только достойно отформатировать:

9 Copyright © 2005 In. Bev – All rights reserved Не так уж это все и сложно, не правда ли? P. S. Единственный недостаток сводных таблиц — отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить (Refresh).

10 Copyright © 2005 In. Bev – All rights reserved. Настройка вычислений в сводных таблицах Допустим, у нас есть построенная сводная таблица с результатами анализа продаж по месяцам для разных городов Нам хочется слегка изменить ее внешний вид, чтобы она отображала нужные вам данные более наглядно, а не просто вываливала кучу чисел на экран. Что для этого можно сделать?

11 Copyright © 2005 In. Bev – All rights reserved. Другие функции расчета вместо суммы Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры поля (Field Settings) или в версии Excel 2007 – Параметры полей значений , то откроется очень полезное окно, используя которое можно задать кучу интересных настроек:

12 Copyright © 2005 In. Bev – All rights reserved. В частности, можно легко изменить функцию расчета поля на среднее, минимум, максимум и т. д. Например, если поменять в нашей сводной таблице сумму на количество, то мы увидим не суммарную выручку, а количество сделок по каждому товару: По умолчанию, для числовых данных Excel всегда автоматически выбирает суммирование, а для нечисловых (даже если из тысячи ячеек с числами попадется хотя бы одна пустая или с текстом или с числом в текстовом формате) – функцию подсчета количества значений (Count).

13 Copyright © 2005 In. Bev – All rights reserved. Если же захочется увидеть в одной сводной таблице сразу и среднее, и сумму, и количество, т. е. несколько функций расчета для одного и того же поля, то смело забрасывайте мышкой в область данных нужное вам поле несколько раз подряд, чтобы получилось что-то похожее: … а потом задавайте разные функции для каждого из полей, щелкая по очереди по ним мышью и выбирая команду Параметры поля (Field settings) , чтобы в итоге получить желаемое:

14 Copyright © 2005 In. Bev – All rights reserved. Долевые проценты Если в этом же окне Параметры поля нажать кнопку Дополнительно (Options) или перейти на вкладку Дополнительные вычисления (в Excel 2007), то окно развернется, и станет доступен выпадающий список Дополнительные вычисления (Show data as)

15 Copyright © 2005 In. Bev – All rights reserved. В этом списке, например, можно выбрать варианты Доля от суммы по строке (% of row) , Доля от суммы по столбцу (% of column) или Доля от общей суммы (% of total) , чтобы автоматически подсчитать проценты для каждого товара или города. Вот так, например, будет выглядеть наша сводная таблица с включенной функцией Доля от суммы по столбцу :

16 Copyright © 2005 In. Bev – All rights reserved. Динамика продаж Если в выпадающем списке Дополнительные вычисления (Show data as) выбрать вариант Отличие (Difference) , а в нижних окнах Поле (Base field) и Элемент (Base item) выбрать Месяц и Назад (в родной англоязычной версии вместо этого странного слова было более понятное Previous, т. е. предыдущий): . . . то получим сводную таблицу, в которой показаны отличия продаж каждого следующего месяца от предыдущего, т. е. – динамика продаж:

17 Copyright © 2005 In. Bev – All rights reserved. А если заменить Отличие (Difference) на Приведенное отличие (% of difference) и добавить условное форматирование для выделения отрицательных значений красным цветом — то получим то же самое, но не в рублях, а в процентах:

18 Copyright © 2005 In. Bev – All rights reserved. Заполнение пустых ячеек в списке Для фильтрации , сортировки, подведения итогов или создания сводных таблиц нужен непрерывный список, т. е. таблица без разрывов (пустых строк и ячеек — по возможности). Таким образом часто возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, т. е. . . ИЗ СДЕЛАТЬ

19 Copyright © 2005 In. Bev – All rights reserved Все очень просто: Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A 2: A 12). Идем в меню Правка — Перейти — Выделить (Edit — Go. To — Special) и в появившемся окне выбираем Выделить пустые ячейки :

20 Copyright © 2005 In. Bev – All rights reserved. Не снимая выделения вводим в первую ячейку знак равно и щелкаем по предыдущей ячейке (т. е. создаем ссылку на предыдущую ячейку, другими словами): И, наконец, чтобы ввести эту формулу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter. И все! Просто и красиво.

21 Copyright © 2005 In. Bev – All rights reserved. Выпадающий список в ячейке Способ 1. Примитивный Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш Alt+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка.

22 Copyright © 2005 In. Bev – All rights reserved. Способ 2. Стандартный Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров). Выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define) и введите имя (можно любое, но обязательно без пробелов!) для выделенного диапазона (например Товары ). Нажмите ОК. Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню Данные — Проверка (Data — Validation). На первой вкладке Параметры из выпадающего списка Тип данных выберите вариант Список и введите в строчку Источник знак равно и имя диапазона (т. е. =Товары ). Нажмите ОК. Все! Наслаждайтесь!

23 Copyright © 2005 In. Bev – All rights reserved. Выпадающий список с данными из другого файла Создать простой выпадающий список в ячейке с данными из этого же файла — легкая задача. Все усложняется, когда данные для списка находятся в другом файле. Для решения этой проблемы можно использовать функцию ДВССЫЛ (INDIRECT) , чтобы сформировать правильную ссылку на внешний файл. Например, если необходимо поместить в список содержимое ячеек А 1: А 10 из файла Товары. xls , нужно в поле Источник ввести следующую конструкцию: =ДВССЫЛ(«[Товары. xls]Список!$A$1: $A$10»)Ñæ àòàÿ ZIP-ïàïê à Функция ДВССЫЛ преобразует текстовую строку аргумента в реальный адрес, используемый для ссылки на данные. Обратите внимание, что имя файла заключается в квадратные скобки, а восклицательный знак служит разделителем имени листа и адреса диапазона ячеек. Если файл с исходными данными для списка лежит в другой папке необходимо указать полный путь к файлу, например, следующим образом: =ДВССЫЛ(«‘C: \TEMP\[Товары. xls]Список’! $A$1: $A$10») В данном случае не забудьте заключить в апострофы полный путь к файлу и имя листа. P. S. Минус всей этой системы только один — выпадающий список будет корректно работать только в том случае, если файл Товары. xls открыт

24 Copyright © 2005 In. Bev – All rights reserved. Связанные выпадающие списки Функция ДВССЫЛ (INDIRECT) Этот фокус основан на применении функции ДВССЫЛ (INDIRECT) , которая умеет делать одну простую вещь — преобразовывать содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. То есть, если в ячейке лежит текст «А 1», то функция выдаст в результате ссылку на ячейку А 1. Если в ячейке лежит слово «Маша», то функция выдаст ссылку на именованный диапазон с именем Маша и т. д. Такой, своего рода, «перевод стрелок» ; ) Возьмем, например, вот такой список моделей автомобилей Toyota, Ford и Nissan: C: \Documents and Settings\marina. perepechenova\My Documents\Тренинг Excel\Linked_Dropdowns. xls

25 Copyright © 2005 In. Bev – All rights reserved. Выделим весь список моделей Тойоты (с ячейки А 2 и вниз до конца списка) и дадим этому диапазону имя Toyota в меню Вставка — Имя — Присвоить (Insert — Name — Define). Затем повторим то же самое со списками Форд и Ниссан, задав соответственно имена диапазонам Ford и Nissan. При задании имен помните о том, что имена диапазонов в Excel не должны содержать пробелов, знаков препинания и начинаться обязательно с буквы. Поэтому если бы в одной из марок автомобилей присутствовал бы пробел (например Ssang Yong), то его пришлось бы заменить в ячейке и в имени диапазона на нижнее подчеркивание (т. е. Ssang_Yong). Теперь создадим первый выпадающий список для выбора марки автомобиля. Выделите пустую ячейку и откройте меню Данные — Проверка (Data — Validation) , затем из выпадающего списка Тип данных выберите вариант Список и в поле Источник — выделите ячейки с названиями марок (желтые ячейки в нашем примере). После нажатия на ОК первый выпадающий список готов: Теперь создадим второй выпадающий список, в котором будут отображаться модели выбранной в первом списке марки. Точно так же, как в предыдущем случае, выделите пустую ячейку и откройте меню Данные — Проверка — далее Список. В поле Источник нужно будет ввести вот такую формулу: =ДВССЫЛ(F 3) где F 3 — адрес ячейки с первым выпадающим списком — замените на свой. Все. После нажатия на ОК содержимое второго списка будет выбираться по имени диапазона, выбранного в первом списке. Минусы такого способа: В качестве вторичных (зависимых) диапазонов не могут выступать динамические диапазоны задаваемые формулами типа СМЕЩ (OFFSET). Для первичного (независимого) списка их использовать можно, а вот вторичный список должен быть определен жестко, без формул. Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т. е. если в нем есть текст с пробелами, то придется их заменять на подчеркивания и т. д. Надо руками создавать много именованных диапазонов.

26 Copyright © 2005 In. Bev – All rights reserved. Постановка задачи Итак, имеем две таблицы — таблицу заказов и прайс-лист : Microsoft Exce l Workshe e t Задача — подставить цены из прайс-листа в таблицу заказов автоматически, ориентируясь на название товара с тем, чтобы потом можно было посчитать стоимость. Использование функции ВПР (VLOOKUP) для подстановки значений

27 Copyright © 2005 In. Bev – All rights reserved. Решение В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP). Эта функция ищет заданное значение (в нашем примере это слово «Яблоки») в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб. ) Схематически работу этой функции можно представить так:

28 Copyright © 2005 In. Bev – All rights reserved. Для простоты дальнейшего использования функции сразу сделайте одну вещь — дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме «шапки» (G 2: H 19), выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define) и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист. Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D 3) и откройте мастер функции (меню Вставка — Функция ). В категории Ссылки и массивы найдите функцию ВПР и нажмите ОК. Появится окно ввода аргументов для функции:

29 Copyright © 2005 In. Bev – All rights reserved. Заполняем их по очереди: Искомое значение — то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае — слово «Яблоки» из ячейки B 3. Таблица — таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя «Прайс» данное ранее. Номер_столбца — порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2. Интервальный_просмотр — в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА: Если введено значение ЛОЖЬ (False) , то фактически это означает, что разрешен поиск только точного соответствия , т. е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, «Кокос»), то она выдаст ошибку #Н/Д (нет данных). Если введено значение ИСТИНА (True) , то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия , т. е. в случае с «кокосом» функция попытается найти товар с наименованием, которое максимально похоже на «кокос» и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле, поэтому для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением являются не текстовые, а числовые искомые значения, например, при расчете Ступенчатых скидок. Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.

30 Copyright © 2005 In. Bev – All rights reserved. P. S. 1 Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если: Включен точный поиск (аргумент Интервальный просмотр=0 ) и искомого наименования нет в Таблице. Включен приблизительный поиск ( Интервальный просмотр=1 ), но Таблица , в которой происходит поиск не отсортирована по возрастанию наименований. Формат ячейки, откуда берется искомое значение наименования (например B 3 в нашем случае) и формат ячеек первого столбца (F 3: F 19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т. п. ) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так: =ВПР(ТЕКСТ(B 3); прайс; ЛОЖЬ) Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т. п. ). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления: =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B 3)); прайс; ЛОЖЬ)

31 Copyright © 2005 In. Bev – All rights reserved. Функция ЕНД (ISNA) проверяет — не возникла ли ошибка #Н/Д как результат работы ВПР и если да, то выводит пустую строку ( «» ) или ноль, а если нет — то выводит результат работы ВПР. В Excel 2007 для подавления сообщения об ошибке можно воспользоваться новой функцией ЕСЛИОШИБКА (IFERROR). P. S. 2 Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться вот такой конструкцией:

32 Copyright © 2005 In. Bev – All rights reserved. Функция ЕСЛИ (IF) ЕСЛИ — очень интересная функция, позволяющая вывести в ячейку одно значение, если заданное пользователем условие выполняется и другое — если условие не выполняется. Функция имеет три аргумента: • логическое выражение, задающее условие (logical_test); • значение, выводимое в случае выполнения условия (value_if_true); • значение, выводимое в случае невыполнения условия (value_if_false). Рассмотрим жизненный пример. Имеем следующую таблицу: Задача — автоматически рассчитать стоимость товара с учетом того, что если количество превысило 5 шт. , то магазин дает скидку 10%.

33 Copyright © 2005 In. Bev – All rights reserved. Решение — использовать для расчета функцию ЕСЛИ со следующими параметрами: То есть, если количество превышает 5, то человек платит не полную стоимость (B 2*C 2), а только 90% от нее (B 2*C 2*0, 9).

34 Copyright © 2005 In. Bev – All rights reserved. Вложенные ЕСЛИ Сама по себе одна функция ЕСЛИ может проверить только одно условие. Поэтому, в случае, когда необходимо проверить сразу несколько условий, приходится вкладывать одну функцию ЕСЛИ в другую. Выглядит это примерно следующим образом: В данном примере проверяется скорость движения автомобиля. Если она больше 110, то выводится предупреждение «Слишком быстро!». В противном случае проверяется — не слишком ли медленно едет водитель, и если нет, то выводится сообщение «Все правильно!» Excel разрешает вкладывать функции ЕСЛИ друг в друга до 7 раз включительно. Хотя вид такой формулы скорее всего будет вызывать легкую икоту.

35 Copyright © 2005 In. Bev – All rights reserved. ЕСЛИ + ИЛИ (IF, AND, OR) Функции И и ИЛИ из категории Логические способны заметно улучшить наглядность и понятность сложных логических проверок. Предыдущий пример с проверкой скорости можно было бы гораздо компактнее и красивее реализовать, например, вот так:

36 Copyright © 2005 In. Bev – All rights reserved. Функции СЧЁТЕСЛИ и СУММЕСЛИ (COUNTIF, SUMIF) Эти функции надо искать не в категории Логические , а в категориях Статистические и Математические , соответственно (или в полном алфавитном перечне). СЧЁТЕСЛИ — подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию, а СУММЕСЛИ — суммирует их значения: Причем в качестве условий (критериев отбора) для этих функций можно использовать не просто текст, как в приведенном выше примере (слова «капуста» или «киви»), а и более сложные конструкции: СЧЁТЕСЛИ(B 7: B 31; «к*») — подсчитает количество сделок с товарами, начинающимися с буквы «к» СЧЁТЕСЛИ(B 7: B 31; «? ? «) — подсчитает количество сделок с товарами, наименование которых состоит из 4 -х букв СЧЁТЕСЛИ(C 7: C 31; «>50») — подсчитает количество крупных сделок с объемом партии больше 50 СЧЁТЕСЛИ(D 7: D 31; «>10. 2004») — подсчитает количество сделок, совершенных позже 10 октября 2004 года

37 Copyright © 2005 In. Bev – All rights reserved. Условное форматирование Самая простая логика. Если содержимое ячейки больше (меньше, равно, не равно и т. д. ) определенного значения, то — срабатывает определенное форматирование для этой ячейки (заливка нужным цветом, цвет и начертание шрифта, границы и т. д. ) Выделите ячейки, которые должны автоматически менять свой цвет и выберите в меню Формат — Условное форматирование (Format — Conditional formatting). В открывшемся окне можно задать условия и, нажав затем кнопку Формат , параметры форматирования ячейки, если условие выполняется:

38 Copyright © 2005 In. Bev – All rights reserved. Условное форматирование с формулами Можно усложнить критерии проверки условного форматирования, если проверять не значение, а формулу. В этом случае Вы можете проверять одни ячейки, а форматировать — другие. Вот так, например, можно выделить цветом все ячейки со значениями больше среднего:

39 Copyright © 2005 In. Bev – All rights reserved. Динамическая выборка из списка функциями ИНДЕКС и ПОИСКПО Как использовать функцию ВПР (VLOOKUP) для поиска и выборки нужных значений из списка мы недавно разбирали. Если же вы знакомы с ВПР, то — вдогон — стоит разобраться с похожими функциями: ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) , владение которыми весьма облегчит жизнь любому опытному пользователю Excel. Гляньте на следующий пример: Необходимо определить регион поставки по артикулу товара, набранному в ячейку C 16. Задача решается при помощи двух функций: =ИНДЕКС(A 1: G 13; ПОИСКПОЗ(C 16; D 1: D 13; 0); 2) Функция ПОИСКПОЗ ищет в столбце D 1: D 13 значение артикула из ячейки C 16. Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т. е. фактически номер строки, где найден требуемыый артикул. Функция ИНДЕКС выбирает из диапазона A 1: G 13 значение, находящееся на пересечении заданной строки (номер строки с артикулом выдает функция ПОИСКПОЗ ) и столбца (нам нужен регион, т. е. второй столбец).

40 Copyright © 2005 In. Bev – All rights reserved. Сравнение двух диапазонов данных Типовая задача, возникающая — рано или поздно — перед каждым пользователем Excel — сравнить между собой два диапазона с данными и найти различия между ними. Рассмотрим несколько способов решения этой проблемы: Способ 1. Логический (примитивный) Имеем два столбца с данными. Необходимо быстро определить — в каких именно ячейках есть различия. Как самый простой вариант — используем функцию ЕСЛИ : C: \Documents and Settings\marina. perepechenova\My Documents\Тренинг Excel\Difference. xls

41 Copyright © 2005 In. Bev – All rights reserved. Способ 2. Выделением (нестандартный) Выделите оба столбца и выберите в меню Правка — Перейти — Выделить — Отличия по строкам Excel выделит ячейки, отличающиеся содержанием (по строкам). Способ 3. Условным форматированием (красивый) Можно включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и откройте меню Формат — Условное форматирование (Format — Conditional formatting). Выберите тип условия Формула и введите:

42 Copyright © 2005 In. Bev – All rights reserved. Затем нажмите на кнопку Формат и задайте цвет заливки на вкладке Вид.

43 Copyright © 2005 In. Bev – All rights reserved. Способ 4. Совпадают или нет? Если предположить самый простой вариант, когда нам нужно просто выяснить, совпадают два списка или нет, то можно воспользоваться формулой массива: = СУММ ( ЕСЛИ (A 2: A 20=B 2: B 20; 0; 1)) =SUMM(IF(A 2: A 20=B 2: B 20, 0, 1)) Эта формула выводит количество несовпадений в двух списках, т. е. ноль — если списки идентичны и любое число >0, если в них есть различия. Формулу надо вводить как формулу массива, т. е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter. Способ 5. Проверка вхождения элементов одного списка в другой Если списки не абсолютно идентичны (элементы идут в разном порядке) и надо определить — какие элементы из первого списка встречаются во втором, а какие — нет, то проще всего использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические :

44 Copyright © 2005 In. Bev – All rights reserved. Быстрое склеивание текста из нескольких ячеек Способ 1 (простой). Функция СЦЕПИТЬ В категории Текстовые есть функция СЦЕПИТЬ (CONCATENATE) , которая соединяет содержимое нескольких ячеек (до 255) в одно целое, позволяя комбинировать их с произвольным текстом. Например, вот такой вариант применения этой функции даст надпись, которую я все свое детство видел на заборе около дома:

45 Copyright © 2005 In. Bev – All rights reserved. Способ 2 (красивый). Символ для склеивания текста (&) Для суммирования содержимого нескольких ячеек используют знак плюс » + «, а для склеивания содержимого ячеек используют знак » & » (расположен на большинстве клавиатур на цифре «7»). При его использовании небходимо помнить, что: Этот символ надо ставить в каждой точке соединения, т. е. на всех «стыках» текстовых строк также, как вы ставите несколько плюсов при сложении нескольких чисел (2+8+6+4+8) Если нужно приклеить произвольный текст (даже если это всего лишь точка или пробел, не говоря уж о целом слове), то этот текст надо заключать в кавычки. В предыдущем примере с функцией СЦЕПИТЬ о кавычках заботится сам Excel — в этом же случае их надо ставить вручную. Вот, например, как можно собрать ФИО в одну ячейку из трех с добавлением пробелов: Если сочетать это с функцией извлечения из текста первых букв — ЛЕВСИМВ (LEFT) , то можно получить фамилию с инициалами одной формулой:

46 Copyright © 2005 In. Bev – All rights reserved. Итак, имеем столбец с данными, которые надо разделить на несколько отдельных столбцов. Самые распространенные жизненные примеры: ФИО в одном столбце (а надо в трех отдельных, чтобы сортировать по имени) полное описание товара в одном столбце (а надо отдельный столбец под фирму-изготовителя, отдельный — под модель и т. д. ). Поехали. . . Выделите ячейки, которые будем делить и выберите в меню Данные — Текст по столбцам (Data — Text to columns). Появится окно Мастера текстов : Автоматическое разбиение одного столбца с данными на несколько

47 Copyright © 2005 In. Bev – All rights reserved. На первом шаге Мастера выбираем формат нашего текста. Или это текст, в котором какой-либо символ отделяет друг от друга содержимое наших будущих отдельных столбцов ( с разделителями ) или в тексте с помощью пробелов имитируются столбцы одинаковой ширины ( фиксированная ширина ). На втором шаге Мастера , если мы выбрали формат с разделителями (как в нашем примере) — необходимо указать какой именно символ является разделителем:

48 Copyright © 2005 In. Bev – All rights reserved. И, наконец, на третьем шаге для каждого из получившихся столбцов, выделяя их предварительно в окне Мастера, необходимо выбрать формат: общий — оставит данные как есть — подходит в большинстве случаев дата — необходимо выбирать для столбцов с датами, причем формат даты (день-месяц-год, месяц-день-год и т. д. ) уточняется в выпадающем списке текстовый — для чисто текстовой информации: Осталось нажать кнопку Готово , утвердительно ответить на вопрос о замене конечных ячеек, который выдаст Excel и насладиться результатом:

49 Copyright © 2005 In. Bev – All rights reserved. Использование формул массивов в Excel Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов: C: \Documents and Settings\marina. perepechenova\My Documents\Тренинг Excel\Array. Formulas. xls Формулы массива в Excel — это специальные формулы для обработки данных из таких массивов. Формулы массива делятся на две категории — те, что возвращают одно значение и те, что дают на выходе целый набор (массив) значений. Рассмотрим их на простых примерах. . .

50 Copyright © 2005 In. Bev – All rights reserved. Пример 1. Классика жанра — товарный чек Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее: 1. выделяем ячейку С 7 2. вводим с клавиатуры =СУММ( 3. выделяем диапазон B 2: B 5 4. вводим знак умножения ( звездочка ) 5. выделяем диапазон C 2: C 5 и закрываем скобку функции СУММ — в итоге должно получиться так: 6. чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter Вуаля! Т. е. Excel произвел попарное умножение элементов массивов B 2: B 5 и C 2: C 5 и образовал новый массив стоимостей(в памяти компьютера), а затемсложил всеэлементы этого нового массива. Обратите внимание на фигурные скобки, появившиеся в формуле — отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно — они автоматически появляются при нажатии Ctrl + Shift + Enter.

51 Copyright © 2005 In. Bev – All rights reserved. Пример 2. Разрешите Вас. . . транспонировать? При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т. е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз. Допустим, имеем следующий двумерный массив ячеек, который хотим транспонировать: Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов. вводим функцию транспонирования =ТРАНСП( в качестве аргумента функции выделяем наш массив ячеек A 1:

52 Copyright © 2005 In. Bev – All rights reservedжмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата : Редактирование формулы массива Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D 10) и выдаст предупреждающее сообщение: Для редактирования формулы массива необходимо выделить весь диапазон (A 10: H 11 в нашем случае) и изменить формулу в строке формул (или нажав F 2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter. Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т. е. в диапазон A 10: H 11 в нашем случае) TRANSPOS

53 Copyright © 2005 In. Bev – All rights reserved. Пример 3. Таблица умножения Вспомните детство, школу, свою тетрадку по математике. . . На обороте тетради на обложке было что? Таблица умножения вот такого вида: При помощи формул массива она вся делается в одно движение: выделяем диапазон B 2: K 11 вводим формулу =A 2: A 11*B 1: K 1 жмем Ctrl + Shift + Enter , чтобы Excel воспринял ее как формулу массива и получаем результат:

54 Copyright © 2005 In. Bev – All rights reserved. Пример 4. Выборочное суммирование Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику: В данном случае формула массива синхронно пробегает по всем элементам диапазонов C 3: C 21 и B 3: B 21, проверяя, совпадают ли они с заданными значениями из ячеек G 4 и G 5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

55 Copyright © 2005 In. Bev – All rights reserved Есть вопросы? Microsoft Exce l Workshe e t