Пример 7.3. Цель: Научиться применять в формулах относительные ссылки.

Постановка задачи: Пусть, например, цена некоторого товара к концу января была 100 р. Определить цену этого товара к концу февраля, марта, апреля и мая, если она увеличивалась в эти месяцы соответственно на 5%, 9%, 7% и 3%.

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

Решение:

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

В ячейке D1 содержится уже знакомая нам формула (записанная в относительных ссылках!), а в ячейки В2, D2, F2, H2 введен знак %. Причем последнее действие выполнено следующим образом: знак % введен с клавиатуры в ячейку В2, скопирован, затем ячейки D2, F2, H2 выделены (диапазон несвязный – удерживаем клавишу <Ctrl>!) и после этого знак % вставлен во все эти ячейки в одно действие. Затем во все эти ячейки введены числовые подправки. В результате таблица принимает вид:

Формула в ячейке D1 ссылается на ячейки В1 и В2. Это, соответственно, ячейка-оригинал и исходные зависимые ячейки. В ячейке F1 должна находиться формула, по структуре аналогичная той, которая находится в ячейке D1, но ссылаться она должна на ячейки D1 и D2. Расположение последних относительно ячейки F1 такое же, как и у ячеек В1 и В2 относительно ячейки D1. Поэтому ячейка F1 может рассматриваться в качестве ячейки-образа, а ячейки D1 и D2 - в качестве новых влияющих ячеек в смысле определения относительных ссылок. Поэтому формулу в ячейке F1 можно получить копированием формулы из ячейки D1. Но все то же самое справедливо и по отношению к ячейкам Н1 и F1, F2, J1 и Н1, Н2. Поэтому формулу из ячейки D1 можно сразу скопировать в ячейки F1, Н1 и J1 за один шаг. Для этого нужно выполнить следующие действия:

1. Выделить ячейку D1 (с формулой).

2. Произвести ее копирование (<Ctrl> + <Insert>).

3. Выделить ячейки F1, Н1 и J1 (удерживая клавишу <Ctrl>).

4. Вставить за один шаг во все эти ячейки скопированную формулу (<Shift> + <Insert>).

Замечание: Еще проще это можно сделать так: сразу выделить все ячейки D1, F1, Н1 и J1, активизировать ячейку D1 (c помощью клавиши <Tab> или <Shift> + <Tab>), ввести формулу в строке формул и нажать клавиши <Ctrl > + <Enter>.

В результате в нашей таблице возникнут следующие формулы:

Эти формулы автоматически просчитаются и окончательный результат принимает следующую числовую форму:

Совет: Очень часто копированные формулы в связном диапазоне удобнее получать, используя не буфер обмена, а маркер заполнения, протаскивая его в нужном направлении при помощи ЛКМ или ПКМ. Для этого нужно выделить ячейку, содержащую формулу, и перетащить маркер заполнения по горизонтали или по вертикали в соседний (примыкающий к ячейке с формулой) диапазон.

Попробуем реализовать данный совет в нашем случае. Для этого вернемся назад к таблице, которая содержит все заголовки, процентные приросты цены товара по месяцам, цену товара к концу января и формулу в ячейке D1. Именно ячейку D1 (предварительно выделенную!) и надо протащить вправо вдоль первой строки за маркер заполнения, удерживая, например, ЛКМ. Учтем, что при этом все заголовки, находящиеся в ячейках E1, G1, I1 будут уничтожены. Поэтому (чтобы не вводить эти заголовки заново) предварительно вырежем (или скопируем) эти ячейки и вставим их “на время” куда-нибудь в свободное место листа и только после этого выполним процедуру копирования с помощью маркера заполнения. Схематично описанные действия представлены на рисунке:

Промежуточный результат после протаскивания маркера заполнения по «горизонтальному» диапазону Е1:J1 следующий:

Как видим, в ячейках F1, Н1 и J1 появились правильные формулы, а в ячейках E1, G1, I1 возникли «решетки» - ошибки отображения информации (значение такой ошибки имеет вид ), указывающие на то, что ширина этих ячеек недостаточна для адекватного отображения содержащихся в них данных. Неотображенные данные можно увидеть в строке формул при выделении ячеек с ошибкой отображения . Неотображенные данные также можно увидеть и в самих ячейках, если увеличить их ширину (напомним, что наиболее просто это делается исполнением !!ЛКМ по разделителю имен столбцов: данного и смежного с ним справа, - или перетаскиванием вправо этого разделителя; см. рисунки, приведенные ниже, на которых схематически показано, какие действия следует предпринять, чтобы увеличить ширину ячейки).

После «раздвижки» столбцов E, G, I мы увидим, что в ячейках E1, G1, I1 также находятся формулы, полученные “протяжкой” маркера заполнения (правда увидеть их можно только в строке формул, а не в том виде, в котором они показаны ниже на рисунке: причина этого обсуждается далее по тексту):

Интересно отметить, что формула в ячейке Е1 ссылается на ячейки С1 и С2, в которых «лежат» текстовые значения, и понятно, что при возвращении результата по этой формуле должна возникнуть уже вычислительная ошибка (ведь текстовые строки, как правило, нельзя складывать с числами и перемножать между собой!), проявлением чего в Ехсеl служит отображение значения этой ошибки #ЗНАЧ!. Оно указывает на то, что хотя бы в одной из влияющих ячеек находится недопустимый тип аргумента (для функции) или операнда (для формулы). В свою очередь формулы в ячейках G1 и I1 сами ссылаются на ошибочные значения и потому также возвращают значение ошибки #ЗНАЧ!. Таблица на этом шаге будет иметь следующий вид:

Последний шаг, который нужно сделать: вернуть в ячейки E1, G1, I1 недостающие заголовки с помощью процедуры D&D_ЛКМ, что схематично показано ниже:

Таблица примет свой окончательный вид: такой же, как и полученный ранее. При этом значения ошибок #ЗНАЧ! будут замещены названиями столбцов таблицы из ячеек E4, G4, I4.

Пример 7.4. Цели: 1) Научиться рационально организовывать на рабочем листе данные, включающие формулы. 2) Выяснить, какие следствия возникают при копировании и перемещении блоков с формулами, содержащими только относительные и смешанные ссылки.

Постановка задачи: Та же, что и в предыдущем примере, только речь идет не о пяти, а о двенадцати месяцах, к примеру 1996 г., т.е. считаются известными входная цена (цена товара к концу января 100р.) и процентные приросты в каждый из двенадцати месяцев, причем за первые пять месяцев они остаются прежними; в июне процентный прирост составл яет 13% с увеличением на 2% в каждый последующий месяц (пример носит учебный характер: числовые значения не соответствуют реальным данным).

Обсуждение: Ясно, что никакого труда не составляет решить эту задачу по изложенной выше схеме. Проблема в другом: как рационально сформировать необходимую таблицу и как рационально ее разместить? Что касается второй части этой проблемы, то здесь существует два аспекта. Во-первых, если построить “строчную” таблицу, аналогичную построенной в предыдущем примере, но не для пяти, а для двенадцати месяцев, то вся эта таблица в “нормальном” режиме просмотра данных не уместится на экран компьютера (кстати, чтобы изменить масштаб просмотра имеет смысл пользоваться командами Вид / Масштаб). Во-вторых, если таблица сильно «вытянута» по горизонтали, то могут возникнуть проблемы с печатью: в режиме общепринятой вертикальной ориентации страницы (кстати, ориентация страницы при печати настраивается командами Файл / Параметры страницы / Страница / Ориентация и, кроме того, на вкладке Страница имеются другие настройки, необходимые для печати документа) и при “нормальных атрибутах” форматирования часть таблицы распечатается на одной странице, а другая часть – на другой. Одним из вариантов решения обсуждаемой проблемы является “вертикальное” расположение таблицы на листе Ехсеl, например, такое, которое показано на последнем рисунке (правда, не для двенадцати месяцев, а только для четырех, но это в рассматриваемом контексте не принципиально).

Для того, чтобы “получить” такую “вертикальную” таблицу нужно выполнить следующие действия:

1. Выделить исходную “горизонтальную ” таблицу.

2. Скопировать ее (<Ctrl> + <Insert>).

3. Выделить одну свободную ячейку как метку вставки левого верхнего угла “вертикальной” таблицы, которая будет вставлена на следующем шаге.

4. Активизировать команды Правка / Специальная вставка / Вставить Все (или при необходимости: Формулы, или Значения, или Форматы, т.п.) / “Повесить” флажок Транспонировать / <ОК> (отметим, что нужно следить за тем, чтобы вставляемая таблица не “накрыла” непустые ячейки, т.к. их содержимое может быть уничтожено; впрочем это не критично, поскольку перед “уничтожением” Ехсеl задаст вопрос, сделано ли это намеренно или случайно).

5. При необходимости – выделить и удалить старую “горизонтальную” таблицу.

Отметим, что в новой «вертикальной» таблице происходит перенастройка формул, т.к. ссылки в них относительные. К примеру, в ячейке А6 «вертикальной» таблицы (см. последний рисунок) «лежит» формула =A4*(1+B4). При этом перенастроенные формулы «работают» правильно, поскольку описанные действия сохраняют относительное расположение всех влияющих и зависимых ячеек (что как раз и позволяет безболезненно выполнить действие 5 из последнего списка действий). Отметим, что перенастроенные формулы настроились бы неверно, если бы была скопирована лишь часть исходной таблицы, в которой имелись бы ссылки на ячейки, содержащиеся в ее нескопированной части. Например, в рассматриваемом случае это произошло бы, если бы «горизонтальная» таблица была скопирована без своего первого столбца (более общо: без части входных данных).

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

• содержат формулы только с относительными ссылками,

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

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

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

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

.

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

Наши дальнейшие действия состоят из трех шагов:

1. Формирование заголовков строк таблицы в столбцах А и С.

2. Ввод исходных данных в ячейку В1 и во все ячейки столбца D3:D13.

3. Ввод в ячейку В3 главной формулы и воспроизведение всех остальных формул столбца В4:В13 ее копированием (в этом и состоит смысл термина главная формула).

Шаг 1.

Заголовки строк столбцов А и С состоят из названий месяцев, дополненных словом Цена в столбце А и символом % в столбце С. Названия месяцев содержатся во встроенном списке процедуры автозаполнения, и поэтому заманчиво было бы им воспользоваться. Но как «сцепить» отдельное слово с этим списком? Для этого можно использовать встроенную функцию СЦЕПИТЬ (текст1;текст2;...).

Предварительные настройки интерфейса при работе с функциями: <Сервис> / <Параметры> / <Надстройки> / Активизировать флажок Пакет анализа.

Справка

Функция СЦЕПИТЬ объединяет несколько текстовых строк в одну.

Синтаксис: =СЦЕПИТЬ (Текст1;Текст2;...), где Текст1, Текст2, ... - аргументы функции. Их может быть от 1 до 30, и они являются элементами текста, объединяемыми функцией СЦЕПИТЬ в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки, каждая из которых может ссылаться на свою отдельную ячейку.

Замечание: Вместо функции СЦЕПИТЬ для объединения текстовых данных можно использовать оператор &. Последний называется амперсантом, а сама процедура сцепления – конкатенацией.

Посмотрим, как можно применить функцию СЦЕПИТЬ для конкатенации слова Цена и символа % со списком автозаполнения. Для этого в свободной части листа Ехсеl сделаем следующие предварительные «заготовки» и выполним действия, понятные из следующих рисунков:

, .

В ячейку G4 введем функцию СЦЕПИТЬ, аргументами которой являются:

• Значение ячейки F2.

• Пустой текст: два раза набранные двойные кавычки, с пробелом между ними (Пояснение: Нам нужно, чтобы в ячейке G4 возникло текстовое значение Цена янв; пустой текст с пробелом требуется для отделения текста Цена от текста янв пробелом).

• Значение ячейки F4.

Дальнейшие действия соответствуют следующему алгоритму: !ЛКМ по кнопке <Изменить функцию> (Эту кнопку называют также Мастером функций) / Ехсеl предъявляет диалоговое окно: Мастер функций – шаг 1 из 2 /

Рис.7.5. Диалоговое окно Мастер функций: шаг 1 из 2

Рис. 7.6. Диалоговое окно функции Сцепить

В списке Категория выбираем Текстовые / В списке Функция выбираем Сцепить / <ОК> / Ехсеl предъявляет диалоговое окно: Сцепить/!ЛКМ по полю Текст1 / !ЛКМ F2 (по ячейке на рабочем листе) / !ЛКМ <F4> (по клавише на клавиатуре; повторяем удары по клавише <F4> до тех пор, пока в поле Текст1 не возникнет отображение в виде F$2) / !ЛКМ по полю Текст2 / С клавиатуры набираем два раза двойные кавычки и вставляем пробел между ними / !ЛКМ по полю Текст3 /!ЛКМ F4 (по ячейке на рабочем листе) / !ЛКМ <F4> (по клавише на клавиатуре; повторяем удары по клавише <F4> до тех пор, пока в поле Текст3 не возникнет отображение в виде $F4) / <ОК>. Промежуточный результат показан ниже:

После этого ячейку G4 перетаскиваем маркером заполнения на одну ячейку вправо. Имеем следующий промежуточный результат:

Д алее, выделяем блок G4:Н4 и копируем его с помощью маркера заполнения в вертикальном направлении вниз вплоть до ячеек G15:Н15 (в этом случае копирование можно выполнить в стандартном варианте: «протяжкой» маркера, - либо нестандартно: !!ЛКМ по маркеру). Получаем окончательный результат этапа работы с функцией СЦЕПИТЬ:

.

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

Комментарии и советы:

• Относительно клавиши <F4>

В процессе ввода абсолютных или смешанных ссылок (как в нашем примере) приходится вставлять в соответствующие позиции (перед именем строки, перед именем столбца или перед тем и другим) символ $. Это проще всего делать нажатием клавиши <F4>. Последовательные нажатия приводят к изменению позиции вставки этого символа так, что он перемещается циклически, например,

Подчеркнем, что символ $ можно вводить и с клавиатуры (но это значительно менее удобно по сравнению с применением клавиши <F4>).

• Относительно окна функции (в нашем примере это окно Сцепить)

При заполнении полей окна функции приходится ссылаться на диапазоны рабочего листа. В нашем примере это сводилось к исполнению ударов ЛКМ по ячейкам листа. Часто окно функции загораживает необходимые отсылочные ячейки. Это легко исправить, поскольку окно «плавает» над листом и его можно перетаскивать мышью, применяя процедуру ПиО_ЛКМ и освобождая необходимое пространство рабочего листа. Кроме этого, имеется возможность, свернуть окно до узкой полосы с помощью кнопок, которые находятся с правой стороны каждого поля заполнения окна функции. Эти кнопки присутствуют и в свернутых окнах. Исполнение удара ЛКМ по такой кнопке приводит к развертыванию окна функции, т.е. к возврату в первоначальное положение.

• Относительно ввода ссылок в окне функции

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

• Относительно оператора конкатенации &

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

Пусть, к примеру, имеет место следующая исходная ситуация

,

при которой в ячейку В36 предварительно введен текст северный, а в ячейку С36 – текст ветер. В ячейке D36 требуется получить текстовую строку Сильный северный ветер. Тогда в эту ячейку нужно с клавиатуры ввести формулу:

(обратите внимание на пустой текст с пробелом между двойными кавычками и на то, что текст Сильный берется также в двойные кавычки).

На ячейки В36 и С36 можно сослаться в процессе ввода или редактирования формулы, исполняя по ним !ЛКМ, или набрать их имена непосредственно с клавиатуры (в «латинице»)^. Окончательная фраза имеет вид

• Относительно главной формулы

Ввод главной формулы (в нашем примере имеется одна главная формула, в других случаях их может быть более одной) это наиболее ответственный этап работы с пользовательской таблицей, построенной на основе «формульного» автозаполнения. В рассматриваемом примере (на Шаге 1)) главная формула находится в ячейке G4 и имеет вид =СЦЕПИТЬ(F$2;" ";$F4). Обратите внимание и проанализируйте самостоятельно, почему знак $ в первой ссылке стоит перед числом, а во второй – перед буквой. Подсказка здесь такая: при копировании вправо эта формула должна «зацепляться» за столбец с названиями месяцев (столбец F), а при копировании вниз она должна «зацепляться» за строку с текстом Цена и знаком % (строка 2).

Подчеркнем еще одно обстоятельство, связанное с главной формулой. Главную формулу мы сначала копировали с помощью маркера заполнения по горизонтали, а затем полученный автозаполнением «горизонтальный» блок с (двумя) формулами копировался по вертикали (при этом также использовался маркер заполнения). Однако эти действия можно выполнить и в обратном порядке: сначала главную формулу можно скопировать по вертикали, а затем полученный вертикальный блок (предварительно выделенный!) – скопировать вправо по горизонтали. Результат от этого не изменится (проверьте!).

• Относительно перенастройки формул при перемещении и копировании блоков с формулами

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

Посмотрим на результат:

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

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

Из последней схемы особенно отчетливо видно, что перенастройка формул произошла по всему перемещаемому блоку (формулы настроились верно).

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

Посмотрим на результат:

.

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

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

.

Из последней схемы отчетливо видно, что формулы скопированного блока «зацепляются» за ту же строку и тот же столбец, что и формулы исходного блока (ведь ссылки – смешанные обоих форматов $A1 и A$1!), но произошел сдвиг влияющих ячеек, соответствующий сдвигу блока в целом (на две ячейки вправо по горизонтали и на две ячейки вниз по вертикали). В результате формулы скопированного блока настроились неверно.

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

Шаг 2.

Итак, выполнен Шаг 1 поставленной задачи. Проблема копирования или перемещения блоков с формулами обсуждалась на шаге 1 не случайно: ведь из полученных в диапазоне G4:Н15 данных на следующем шаге нужно сформировать заголовки строк новой таблицы. Копированием столбцов G4:G15 и Н4:Н15 этого достичь нельзя, но казалось бы это можно сделать их перемещением и вставкой в требуемые позиции листа. Однако и в таком варианте действий присутствует свой “подводный камень”: конечно, “разнести” столбцы G4:G15 и Н4:Н15 можно (используя, например, процедуру D&D_ ЛКМ), отображаемые данные при этом сохранятся. Но ведь эти столбцы содержат формулы, которые ссылаются на отдельные ячейки F2, G2 и ячейки диапазона F4:F15 рабочего листа. Последние носят вспомогательный характер, и после того, как новая таблица будет сформирована, их надо будет каким-либо образом “уничтожить”. Нужно осознавать, что, как правило, работа в Ехсеl должна быть завершена печатью документа, а отображение ячеек F2, G2, F4:F15 будет диссонировать со всем остальным содержанием напечатанного документа. Просто очистить эти ячейки (Правка / Очистить) или удалить их (Правка / Удалить) нельзя: это мгновенно приведет к неправильному отображению информации в зависимых от них ячейках. Самым простейшим вариантом решения такой задачи – задачи уничтожения “лишних” данных - является следующий: не печатать ту часть рабочего листа, в которой содержатся вспомогательные данные. Еще лучше под них отвести специальный лист, но в этом случае возникает неудобство, связанное с тем, что в процессе работы нужно переключаться с одного листа на другой. Правда часто бывает, что этого не избежать: структурированность (как правило, разнородных) данных, разнесенных по различным рабочим листам или даже книгам, перекрывает упомянутое неудобство.

Однако существуют три простых способа уничтожения “лишних” данных:

• Скрыть строки/столбцы, в которых они находятся.

• Назначить соответствующим ячейкам скрытый формат.

• Использовать команды Правка / Специальная вставка / Копировать: Значения / <ОК> или процедуру D&D_ПКМ с выбором из контекстного меню команды Копировать только значения.

Алгоритм первого способа: !ЛКМ по имени строки (или столбца) / <Формат> / Строка (или Столбец) / Скрыть.

Замечание: Вместо !ЛКМ по имени строки можно исполнить !ЛКМ по любой выделенной ячейке строки.

Иногда требуется отобразить скрытые строку или столбец. Алгоритм отображения для строки (для столбца аналогичен) таков:

К урсор на разделитель имен строк, между которыми “лежит” скрытая строка так, чтобы он принял форму / !!ЛКМ. (или: Выделить любые две подряд идущие ячейки в строках, между которыми «лежит» скрытая строка / <Формат> / <Строка> /<Отобразить>).

Отметим, что при скрытии строк/столбцов данные, в них содержащиеся, не уничтожаются; просто соответствующие строки или столбцы Ехсеl не отображаются на листе и при печати. Например, если имена столбцов на листе идут в следующем порядке А, D, E, …, то это означает, что столбцы В, С являются скрытыми, но при желании их можно «раскрыть» с помощью описанного выше алгоритма.

Чтобы реализовать второй способ предварительно требуется создать скрытый формат. Это делается весьма просто: <Ctrl> + <1> / Число / Все форматы (или Пользовательские форматы) / В поле Тип три раза с клавиатуры ввести точку с запятой (;;;) / <ОК>.

Алгоритм второго способа: Выделить ячейки или блоки, которые нужно скрыть / <Ctrl> + <1> / Число / Все форматы (или Пользовательские форматы) / В списке Тип выбрать созданный скрытый формат (;;;)/ ОК.

Отметим, что при назначении ячейке скрытого формата данные в ней не уничтожаются, лишь не отображаются: для того, чтобы их «увидеть» (правда, в строке формул), нужно исполнить по ней !ЛКМ.

Результатом применения третьего способа является «физическое» уничтожение (т.е. удаление) формул в соответствующих ячейках; при этом формулы замещаются своими собственными значениями. В нашем случае формулы, полученные на основе функции СЦЕПИТЬ, выполнили свое предназначение: создали необходимые ряды текстовых данных, - и могут быть удалены при условии, что возвращаемые ими данные сохранятся в тех же самых ячейках, где «лежали» формулы. Именно такая ситуация реализуется в третьем способе. В частности, это означает, что блоки с текстовыми константами – заголовками строк будущей таблицы – можно будет (после уничтожения формул, но с сохранением возвращаемых ими значений) «спокойно» перетаскивать ЛКМ в нужные позиции листа, а «бывшие» влияющие ячейки («бывшие» - потому, что они перестали быть таковыми, т.к. формулы, которые на них ссылались, уничтожены) могут быть безболезненно удалены.

Алгоритм третьего способа: Выделить блок с формулами / Подвести курсор к границе блока так, чтобы он принял вид наклонной стрелки / D&D_ПКМ в любое место листа (не отпуская ПКМ) с возвратом на исходное место / Отпустить ПКМ / Из контекстного меню выбрать Копировать только значения.

Схема реализации алгоритма третьего способа представлена ниже на рисунке:

Замечание: После выделения блока с формулами его можно скопировать, выделить ячейку под левый верхний угол места вставки копии и, далее, вместо процедуры D&D_ПКМ использовать команды Правка / Специальная вставка / В списке Копировать выбрать: Значения.

Теперь уже легко получить результат Шага 2:

Шаг 3.

После выполнения Шага 2 легко реализовать и Шаг 3, копируя главную формулу с помощью маркера заполнения вдоль столбца В, согласно схемы:

,

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

Замечание: Вместо протаскивания маркера заполнения в этом случае можно исполнить !!ЛКМ по маркеру: результат будет тот же.

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

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