SQL для MS Excel — небольшой полезный практикум

Мой Компьютер, №15, 07.04.2008

Что же представляет из себя простенький SQL-запрос?

Пусть нам дана таблица данных (учет мелкого… нет, откормленного кота… в основном кошки).

 Для дизайну сделала пустые строки между разными категориями кошек.

Обратившись к этой таблице… Каким образом? В Access, или в Oracle, в SQL Server, MySQL или даже в Excel — в каждой среде, программе предусмотрены свои способы обращения к таблице, а также свои способы хранения оной. Не заостряя на этом внимания, замечу, что к теоретической таблице мы теоретически обратиться можем. Вот мы к ней (теоретически) и пристанем.

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

Командую же таблице так:

SELECT TableCat .Имя, TableCat. [Длина шерсти], TableCat .Расцветка

FROM TableCat

Что означает по-человечески: покажи, мне, компьютер, все, что вообще в TableCat есть в столбцах Имя, [Длина шерсти] и Расцветка. А других у нее столбцов и нет.

В скобках замечу, что можно было вместо перечисления названий столбцов просто поставить * или TableCat.*. Но это уже тонкости…

Если меня интересуют не все кошаки, а только длинношерстные-е-е, скажу так, уточняя:

SELECT TableCat .Имя, TableCat. [Длина шерсти], TableCat .Расцветка

FROM TableCat

WHERE TableCat. [Длина шерсти]=” длинная”

Для упорядочения кошек… данных существует оператор ORDER BY, для учета их количества, вообще анализа их полосатости существуют функции… Но не в этом сейчас дело. Как бы мы ни тасовали данные, за хвост или более гуманно, все равно мы должны указать в запросе, какие именно столбцы и в каком количестве мы желаем получить в итоге. Мы заранее изображаем заголовок будущей таблицы, которая получится в результате наших истязаний. Может, из-под дивана вдруг вылезет соседская кошка, совершающая дружественный, но незапланированный визит — лишний столбец не должен появиться из ниоткуда. А вот строк, замечу, может сколько угодно много быть или сколько угодно мало не быть, тут уж как получится.

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

И вот теперь буду рассказывать, как такого добиться.

Для получения такой красоты применяется инструкция TRANSFORM. Применяется она к оператору SELECT, который, таким образом, получается вложенным и окруженным. Нельзя сказать, что последний просто причесывается, приводится в порядок TRANSFORM’ом — они работают вместе и в связке. Посмотрите на работу SELECT’а, отдельного от TRANSFORM — вы увидите, что результат абсолютно другой. В двух словах:

TRANSFORM Count(TableCat.N) AS [Значение]

SELECT TableCat.Владелец, Count(TableCat.N) AS [Итоговое значение N]

FROM TableCat

GROUP BY TableCat.Владелец

PIVOT TableCat.Расцветка;

А теперь будет много слов. После слова TRANSFORM указываем, что именно мы будем считать. Считать будем кошаков… то есть записи, строки в исходной таблице. Функции Count лучше скормить какое-нибудь поле, по возможности числовое, равномерно заполненное данными, иначе при подсчете могут пропуститься строки, где значения этого поля пустые, и число будет меньше. Поле счетчика иногда ведет себя странно, но возьмем пока именно его — поле номера записи N.

То, что стоит после слова SELECT во второй строке, есть перечисление строк — неизвестное количество строк, каждая из которых показывает информацию по кошкам одного владельца (TableCat.Владелец) и… столбец типа «Итого». Что он делает среди строк? Дело в том, что итог считается построчно, поэтому все логично. Понятное дело, столбец «Итого» может быть выброшен, если не нужен.

Третья строка указывает, откуда тащим данные. Четвертая строка — группировка данных по владельцу. Она уточняет, что в заголовках строк — список возможных владельцев животных.

Последняя строка — указание на неизвестное количество столбцов «цвета кошек». Самое, так сказать, интересное.

Команда AS дает прозвище, нужный ник столбцу. Если, к примеру, убрать AS [Итоговое значение N], то тот же «Аксес» по доброте душевной поставит все что угодно — например, Expr1003. Так что за смыслом и дизайн забывать не нужно.

Если все это изобразить графически — вот оно.

Это я тему раскрыла своими словами. Ну, кому построже нужно — вот тут можно почитать:

http://office.microsoft.com/ru-ru/access/HA012315181049.aspx. Или тут:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/output/F1/D2/S5A33B.asp (англ.)

Еще мне понравилась вот эта статья:

http://ewbi.blogs.com/develops/2005/01/access_sql_tran.html (англ.)

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

Для этого в запросе нужно поправить список в операторе SELECT:

TRANSFORM Count(TableCat.N) AS [Значение]

SELECT TableCat.Дом, TableCat.Квартира, TableCat.Владелец

FROM TableCat

GROUP BY TableCat.Владелец, TableCat.Квартира, TableCat.Дом

PIVOT TableCat.Расцветка;

Добавились TableCat.Квартира и TableCat.Дом. То есть получается упорядоченность, сортировка, иерархия по вложению — сначала дом, потом квартира, потом — кто живет (владелец кошака).

Более широкий формат TRANSFORM — с параметром. Пример приведен, например, тут:

http://www.sql.ru/forum/actualthread.aspx?tid=272449.

Вспомнился почему-то бейсиковский оператор INPUT…

Укажу ссылки на интересные примеры. Тут народ борется с датами, упорядочивает по ним

http://www.sql.ru/forum/actualthread.aspx?tid=499405&pg=-1.

Тут упражняются в высшем пилотаже, объединяя по нескольку запросов и уже их трансформируя

http://www.sql.ru/forum/actualthread.aspx?tid=520995.

По запросу заметно, что столбцы и строки неравноправны — попытка создать такую же таблицу, но транспонированную, так просто не пройдет. Если поменять местами то, что идет в разделе PIVOT и в SELECT и GROUP BY — получим ошибку.

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

TRANSFORM Count([N]) AS [Значение]

SELECT [Расцветка], Count([N]) AS [Итоговое значение N]

FROM TableCat

GROUP BY [Расцветка]

PIVOT [Владелец];

…Когда начинаешь пользоваться в Excel макросами, получаешь хотя бы небольшой навык программирования — многие команды в меню смотрятся иначе. Рассматривая, как те же пункты меню записываются программно, начинаешь понимать суть их работы. Выясняешь, что же они делают на самом деле… Сводные таблицы в Excel — это, если хотите, оболочка, в которую заворачивается оператор TRANSFORM (во всяком случае, я поняла именно так). Отличие от запроса — возможность многоэтажной шапки и сбоку, и сверху, начиная со старших версий.

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

Сводная таблица появляется… ну, не знаю с какой версии, но в Excel 97 она уже есть (в Excel 7.0 есть — http://retro.samnet.ru/excel/less6.html, и даже в 5.0 — http://portal.nauu.kiev.ua/excel96/index.htm).

Приступим ее потихонечку потреблять, сравнивая все версии и рассуждая, что да как. А различия версий имеются, хоть они в основном относятся к дизайну и удобству. Навешиваются рюшечки, а как таковых возможностей новых не прибавляется. Да что я придираюсь-то — что можно прибавить в принципе к стандартному запросу TRANSFORM?

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

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

Итак, по нажатии пункта меню в 97-м запускается мастер, шаг первый.

Уточняется, откуда будут брать данные — не поячеечно уточняется, а в целом.

По кнопке Далее переходим на второй шаг.

Там просят указать область точно, тут программа сама выделяет область. Если она не угадала, имеем возможность править — как руками, перебив цифирки и буковки, так и указав мышей. Жмем Далее, переходим на третий шаг…

В Офисах с 2000 по 2003 на первом шаге спрашивают — это у нас будет сводная таблица или сводная диаграмма?

Отличия только в дизайне — в 2003-м красивше спрашивают… На втором шаге — как в 97-м.

Третий же шаг… А вот тут опять проявляется значительное различие между Excel 97 и более старшими Офисами от 2000 вплоть до 2007-го.

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

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

Понажимав ОК и Далее, в 97-м Офисе движемся на следующий пункт, он же последний. В четвертом пункте нужно рассказать, куда будем размещать готовый отчет, заодно можно установить некоторые параметры кнопкой Параметры.

Из последних особо неполезен параметр Автоформат — птицу обязательно снимаем.

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

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

В старших же версиях вы после второго шага как бы сразу перепрыгиваете на четвертый и обалдеваете — а собственно, где?

Все настройки «третьего шага» обнаруживаются под специальной кнопкой Макет (самая левая кнопка). Зачем было так делать — не знаю. Первый раз увидела — растерялась и испугалась. По нажатии обнаруженной кнопки все оказалось как в 97-й версии.

В 2007-м Офисе построение кошек вместе с их хозяевами… построение сводной таблицы начинается в меню Вставка, там на ленте самая левая кнопка — Сводная таблица.

У этой кнопки «подменю» — Сводная таблица.

Жмем, начинаем строить…

Первый шаг — гибрид первого и четвертого, если сравнивать с Excel 97. То есть у нас уточняют, откуда данные будем брать, а потом сразу — куда их полагать.

Если девать некуда — лучше и не соваться, все серьезно. Выбрала — создать чистый лист. После чего никаких шагов дальше не наблюдается вообще!

Таблица «третьего шага», она же кнопка Макет, расположилась прямо на новом листе.

Поля, которые полагается перетаскивать, — справа. Строго не рекомендую начинать сразу с перетаскивания вычисляемого поля (то есть N в нашем предыдущем запросе) — сразу макет сворачивается.

Начните с заголовков строк и столбцов. Могу отметить, что в Офисах с 2000 по 2003 если не нажать кнопку Макет и не настроить, что мы суммируем, что считаем и кто у нас во главе шапки, изображается примерно то же, что по умолчанию идет в 2007-м (2002-й и 2003-й красивее, чем 2000-й).

Ну, и еще — параметров в 2007 больше, чем во всех предыдущих версиях.

Строгий хелпарь по пунктам тут: http://office.microsoft.com/ru-ru/excel/HP051995561049.aspx.

Почитать инфу по 97-му можно тут: http://shkola.lv/index.php?mode=cht&chtid=506 (просто, ясно и крупным шрифтом :-).

Еще можно почитать тут: http://detc.usu.ru/assets/acomp0021/61.htm (на более студенческом уровне. И походите по стрелочкам, там целая книжка).

«Бесплатный электронный учебник по Microsoft Excel» (http://www.help-remont.ru/excel/Glava9/index2.php) — имеется в виду опять же Excel 97.

В самом конце этой страницы

http://www.helloworld.ru/texts/comp/lang/vbasic/excel/main.htm

автор пытается программно создать сводную таблицу из-под Excel 97.

Почитать по старшим офисам — туточки:

http://www.europstyle.ru/obrazovanie/MicrosoftExcel/8.htm.

Какая-то красивая книга по 2003-му наблюдается здесь:

http://www.e-college.ru/xbooks/xbook050/book/index/index.html?part-009*page.htm#i01901

Практическое применение в бухгалтерии:

http://www.dtkt.com.ua/automation/rus/excel.html

Посмотрите урок 20.

Специальный сайт про Excel (так и называется — www.pro-excel.ru) рассказывает о создании сводной таблицы (http://www.pro-excel.ru/?cat=9, есть конспект и даже видеоролик предлагается).

Сайт сделан современно, с наворотами, но удобно.

Более простой, простенькой даже внешностью отличается сайт-учебник на «Народе» (http://leo-arek.narod.ru/62.htm  — это часть, где рассказано о сводных таблицах). Учебник большой, но малокартинчатый, мне понравился.

Рассказ о сводных таблицах идет с упоминанием OLAP-кубов (на всякий случай вот что это такое: http://ru.wikipedia.org/wiki/OLAP).

Инфа обнаруживается даже в народных рефератах — «Обработка табличной информации с помощью сводных таблиц…» (http://www.referats.net/pages/referats/rkr/page.php?id=11972) или просто «Microsoft Excel» (http://www.5ballov.ru/referats/preview/32452/1).

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

Наталья ЛИТВИНЕНКО

Robo User
Web-droid редактор

     

Читайте також