Для студентов EXCEL

10. Столбцы № п.п., № телефона, Количество приобретенных путевок (туров) и Число лет, прошедших со времени первого обращения, проще всего заполнить, ссылаясь на исходный Лист1. Для этого для заполнения столбца № п.п. на Листе2 в ячейке A2 набрать =Лист1!A2 (либо набрать = и щелкнуть по ячейке A2 Листа1, при этом необходимый адрес поставит сам Excel). Затем распространить формулу, используя маркер автозаполнения, на весь диапазон записей. Аналогично заполнить столбцы № телефона, Количество приобретенных путевок (туров), Число лет, прошедших со времени первого обращения.

11. Для заполнения столбца Ф.И.О. использовать оператор & (объе-

динения или сцепления) и текстовую функцию ЛЕВСИМВ, которая выбирает левый, т. е. первый символ текстовой строки. Кавычки используются для включения в результат символов любого текста, в нашем случае, этими символами будут пробел после фамилии и точки после инициалов. Наша задача перенести фамилии с исходного Листа1 и добавить к ним инициалы. Для этого на Листе2 в ячейку B2 ввести следующую формулу(адреса ячеек лучше указывать, щелкнув по ним):

=Лист1!B2&" "&ЛЕВСИМВ(Лист1!C2)&"."&ЛЕВСИМВ(Лист1!D2)&"."

12.Распространить формулу, используя маркер автозаполнения, на ячейки B2:B11.

13.Закрыть Лист1, оставив на экране только Лист2. Перевести Лист2 в полноэкранный режим.

14.Найти записи, содержащие фамилии, которые заканчиваются на

а. Для этого в столбце Ф.И.О. нажать | Условие| | Заканчивает-

113

ся на| а????? | Ок. При правильном выполнении должны остаться только три записи (Сидорова, Стрельцова, Лаврова). Знаки маски отбора ????? показывают, что 5 самых правых знаков не проверяются, т.е. проверяется 6 знак от конца, что соответствует последней букве фамилии.

15. Скопировать найденные записи вместе с заголовком на Лист3, используя специальную вставку и установив связь. Для этого выделить найденные записи вместе с заголовком, скопировать их в буфер обмена, перейти на Лист3 и там выполнить Правка|(Главная)| Вставить|

Специальная вставка| Вставить связь.

16.Переименовать Лист3. Для этого нажать правой кнопкой мыши по ярлычку (Лист3)| Переименовать| Женщины.

17.Закрыть этот лист и вернуться на Лист2.

18.Отобразить все записи: в столбце Фамилия нажать | Все.

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

19.Вставить две строки перед таблицей и скопировать заголовки таблицы в первую строку. Вторая строка предназначена для ввода условий расширенного поиска. В в ячейку D2 ввести >2, а в ячейку E2 ввести >3.

20.Используя расширенный поиск, найти клиентов, которые обращаются в фирму более 3 лет и купили более 2 путевок. Для этого вы-

полнить: Данные| Фильтр| Расширенный фильтр. В появившемся окне

выбрать Фильтровать список на месте| Исходный диапазон| A3:F13

( т. е. всю исходную таблицу)| Диапазон условий| A1:F2 (добавленные ранее две строки)|ОK.

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

22.Переименовать Лист4 в Постоянные. Закрыть текущий лист.

23.На Листе2 вернуть все записи. Для этого ввести: Данные|

Фильтр| Отобразить все.

114

24.Удалить ранее добавленные строки.

25.Добавить в таблицу столбец Скидки (столбец G).

26.Ввести в ячейку G2 логическую формулу, позволяющую выбрать кандидатов на скидки. На скидку имеют право клиенты, которые пользуются услугами фирмы не менее 5 лет (помечаются знаком ++); либо те, кто приобретает не менее двух путевок сразу (помечаются знаком +)

=ЕСЛИ(E2>5;ЕСЛИ(D2>0;"++");ЕСЛИ(D2>=2;"+";"-"))

27.Распространить формулу, используя маркер автозаполнения, на ячейки G2:G11.

28.В последней строке столбца D (ячейка D12) найти среднее значение числа приобретенных туров, используя функцию

=срзнач(d2:d11)

29.Ввести в начало строки 12 текст (Среднее число путевок) и оформить ее.

30.Отсортировать таблицу по возрастанию количества приобретенных туров.

31.Подготовить таблицу к печати. Для этого установить на экране разбивку на страницы: Кнопка ―Office”| Параметры Excel| Дополни-

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

115

Пример 2

1.Ввести в ячейки A1:C3 числа от 1 до 9;

2.Ввести в ячейку E1 число

365;

3.Выделить диапазон A5:C7

4.Ввести формулу =A1:C3*E1

5.Одновременно нажать три клавиши + +

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

умножен на число, стоящее в ячейке E1, а именно, на 365.

6. Изменить число в ячейке E1, задав вместо 365 число 366, и посмотреть, изменится ли массив данных A5:C7.

Пример 3

1.Ввести в ячейки A1:C3 числа от 1 до 9;

2.Ввести в ячейки A5:C7 числа от 100 до 108;

3.Выделить диапазон

A9:C11.

4.Ввести формулу

=A1:C3+ A5:C7.

5.Одновременно нажать

+ +

Формула будет помещена в фигурные скобки. Получается массив, каждый элемент которого равен сумме соответст-

вующих элементов диапазонов A1:C3 и A5:C7.

6.Изменить число в ячейке E1, задав вместо 365 число 366, и посмотреть, изменится ли массив данных A5:C7;

7.Попытаться изменить значение в ячейке 117 в ячейке C11. Когда появится сообщение «Нельзя изменять часть массива», нажать кнопку

в строке формул (на рисунке эта кнопка обведена).

Пример 4

1.Ввести в ячейки A1:C3 числа от 1 до 9;

2.Выделить диапазон A5:C7;

3.Ввести формулу =SIN(A1:C3);

117

4. Одновременно нажать три клавиши + + Формула будет помещена в фигурные скобки. В результате получа-

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

Пример 5

1.Ввести в ячейки A1:C3 любые, но не последовательные, числа;

2.Выделить диапазон A1:C3, нажать правую кнопку мыши, выбрать Имя диапазона и ввести имя

3.Ввести в ячейки E1:G3 любые числа, отличные от введенных;

4.Выделить диапазон E1:G3, выбрать Формулы| Присвоить имя и ввести имя

Имена в формулах легче запомнить, чем адреса ячеек, поэтому вместо абсолютных ссылок можно использовать именованные области (одна или несколько ячеек). Необходимо соблюдать следующие правила при создании имен: имена могут содержать не более 255 символов; имена должны начинаться с буквы и могут содержать любой символ, кроме пробела; имена не должны быть похожи на ссылки, такие, как ВЗ, С4; имена не должны использовать функции Excel, такие, как, например, СУММ, ЕСЛИ и т. п.

5.Выделить диапазон A7:C9

6.Ввести формулу =МУМНОЖ(RAZ;Dva)

7.Одновременно нажать три клавиши + +

8.Выделить диапазон E7:G9

9.Ввести формулу = RAZ*Dva

10.Одновременно нажать три клавиши + +

Врезультате в ячейках A7:C9 получается массив, составленный по правилам умножения матриц, а в ячейках E7:G9 получается массив, каждый элемент которого есть произведение элементов исходных массивов, стоящих на одинаковых местах. Обратите внимание, что результат отличается.

Решение системы линейных уравнений

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

Решаем следующую систему алгебраических уравнений:

118

5x1 + 17 x2 – 3x3 + 37x4 = 14, 7x1 + 2 x2 + 15x3 + 3x4 = –6.

1.Занести в ячейки A2:E5 числовые коэффициенты, соответствующие рассматриваемой системе.

2.Решаем систему матричным методом.

2.1.В ячейках A8:D11 найти значение обратной матрицы. Для этого выделить диапазон A8:D11, в ячейке A8 набрать формулу =МОБР(A2:D5)

изакончить ввод формулы од-

новременным нажатием клавиш + + . Формула будет помещена в фигурные скобки.

2.2.В ячейках A14:A17 найти решение заданной сис-

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

=МУМНОЖ(A8:D11;E2:E5)

и одновременно нажать три клави-

ши + + .

3. Проверить результат, выполнив умножение матрицы исходных коэффициентов на столбец ре-

шения =МУМНОЖ(A2:D5;A14:A14).

Если решение выполнено верно, должен получиться столбец свободных членов.

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

119

лается ссылка на их значения в предыдущей итерации. Первая итерация

=A2:E2/$A$2 =A3:E3-$A$8:$E$8*$A3

4.1.Вторая итерация

=A8:E8 =A9:E9/$B$9

=A10:E10-$A$15:$E$15*$B10

4.2.Третья итерация

=A14:E15 =A16:E16/$C$16 =A17:E17-A21:E21*$C$17

4.3.Четвертая итерация

=A19:E19-$A$28:$E$28*$D19 =A22:E22/$D$22

4.4.Обратная первая итерация

=A25:E25-$A$27:$E$27*$C25 =A27:E28

4.5.Обратная вторая итерация

=A31:E31-A32:E32*B31 =A32:E34

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

4.7.Оформить ячейки таблицы.

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

120

Тема 11. Консолидация данных.

Под термином «КОНСОЛИДАЦИЯ» подразумевается ряд стандартных операций с несколькими рабочими таблицами и рабочими книгами. В некоторых случаях консолидация может включать в себя создание связанных формул. Основной фактор, влияющий на консолидацию данных — способ размещения информации в рабочих таблицах. Если размещение информации во всех таблицах одинаково, то говорят о консолидации по позиции. В том случае, когда размещение информации не идентично, но достаточно похоже, то можно объединить данные по заголовкам строк и/или столбцов. Такая консолидация называется консолидацией по категориям. Если же рабочие таблицы имеют мало общего друг с другом, то необходимо отредактировать листы, чтобы они стали единообразными.

1. Ввести информацию и оформить таблицу «Число туров по фирме «Синяя птица»».

2.Ввести информацию и оформить таблицу «Число туров по фирме «Пилигрим»».

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

3.1.Поставить курсор в ячейке A17 и выполнить: Данные| Кон-

солидация| Функция |Сумма.

3.2.Активизировать флажки напротив Подписи верхней строки и Значения левого столбца.

3.3.Указать диапазоны консолидируемых данных: Ссылки|

A3:D6| Добавить| A9:D13| ОК.

121

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

3.5.Выполнить консолидацию тех же массивов, убрав флажок на-

против Подписи верхней строки.

3.6.Выполнить консолидацию тех же массивов, убрав флажок на-

против Значения левого столбца.

3.7.Выполнить консолидацию тех же массивов, убрав оба флаж-

ка.

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

4.Переименовать лист с исходными таблицами, назвав его «Консолидация_статическая».

5.Вставить новый лист и назвать его «Консолидация_динамическая».

6.Установить на экране два окна («Консолидация_статическая» и «Консолидация_динамическая») рядом: Вид| Новое Окно| Рядом. Перейти в окно «Консолидация_динамическая» и выполнить консолидацию таблиц «Число туров по фирме «Синяя птица»» и «Число туров по фирме «Пилигрим»» так же, как в предыдущем случае, установив

дополнительно флажок Создавать связи с исходными данными.

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

6.2.Убрать с экрана второе окно, отжав соответствующую кнопку (или закрыв второе окно).

7.Провести консолидацию данных задания пункта 3 с помощью команды Главная| Вставить| Специальная вставка.

7.1.Выделить диапазон A3:D6 и скопировать в буфер обмена.

7.2.В ячейке F2 (свободном месте рабочего листа) выполнить:

Главная| Вставить| Специальная вставка| Вставить Все| Операции Нет.

7.3.Выделить вторую таблицу — диапазон и A10:D13 и скопировать ее в буфер обмена.

7.4.В ячейке F2 выполнить консолидацию следующим образом:

Главная| Вставить| Специальная вставка| Вставить Все| Операции Сложение.

122