Мой Компьютер, №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).
Какой вариант, какой Офис лучше, не знаю. Лучше та программа, которую вы знаете, которой умеете пользоваться быстро и профессионально.
Наталья ЛИТВИНЕНКО
