Консолидация

Способ 1. С помощью формул

Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:

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

Самый простой способ решения задачи "в лоб" - ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

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

Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:

=СУММ('2001 год:2003 год'!B3)

Фактически - это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

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

Для того, чтобы выполнить такую консолидацию:

1. Заранее откройте исходные файлы

2. Создайте новую пустую книгу (Ctrl + N)

3. Установите в нее активную ячейку и выберите на вкладке (в меню) Данные - Консолидация (Data - Consolidate). Откроется соответствующее окно:

4. Установите курсор в строку Ссылка (Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить (Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.

5. Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:

 

Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels). Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

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

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

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

Алгоритм (Колмогоров) – это всякая система вычислений, выполняемых по строго определенным правилам, которая после какого-либо числа шагов заведомо приводит к решению поставленной задачи.

Алгоритм (Марков) – это точное предписание, определяющее вычислительный процесс, идущий от варьируемых исходных данных к искомому результату.

Для того, чтобы произвольное описание последователь­ности действий было алгоритмом, оно должно обладать сле­дующими свойствами:

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

• Понятность. Каждая команда алгоритма должна быть по­нятна тому, кто исполняет алгоритм (исполнитель); в про­тивном случае эта команда и, ледовательно, весь алгоритм в целом, не может быть выполнена. Составим полный спи­сок команд, которые умеет выполнять исполнитель алго­ритма, и назовем его системой команд исполнителя (СКИ). Тогда понятными будет являться только те коман­ды, которые попадают в этот список.

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

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

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

Основными способами записи алгоритмов являются [2]:

· словесный;

· словесно-формульный;

· на школьном алгоритмическом языке;

· графический (блок-схема);

· на языке программирования высокого уровня.

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

Графический способ представления алгоритмов является более компактным и наглядным по сравнению со словесным.

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

 

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

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

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

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

 

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

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

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

 

Для того чтобы создать сводную таблицу и диаграмму, сделайте следующее:

 

Выделите таблицу.

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

Шаг первый. Выберите пункты:

a) создать таблицу в списке или базе данных Microsoft Excel.

b) вид создаваемого отчета выберите Сводная диаграмма (со сводной таблицей).

Шаг второй. Укажите диапазон, содержащий исходные данные. Мастер предлагает свой диапазон, при необходимости его можно изменить.

Шаг третий. Выберите Поместить таблицу в новый лист и нажмите Готово. На экране появится поле для диаграммы и список полей сводной таблицы.

Перетащите мышкой в ячейку для полей категорий поле Спорт. В поля рядов - Год, и в элементы данных - Продажи.

Теперь отредактируйте тип диаграммы - сделайте обычную гистограмму.

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

a) Выберите вкладку Диаграммы и отметьте Пользовательский размер.

b) Теперь во вкладке Страница выберите ориентацию Книжная.