Дела печальные
Дела печальные
Да, именно так. Как гласит старая народная пословица, сколько веревочке не виться, а конец всегда будет. Это я к тому, что настало время мне вас огорчить. Как вы думаете, чем отличаются всякие там системные программисты и администраторы от всех остальных пользователей? Насколько мне кажется, ответ прост, как божий день. Человек нормальный, общаясь с компьютером, очень похож на классического бравого капитана эпохи парусного флота. В те времена начальство интересовалось лишь тем, что расположено над палубой. Все, что было ниже, в трюмах например, было недостойно внимания, чтобы узреть весь личный состав, даже была придумана такая команда - свистать всех наверх. Рядовой пользователь примерно так же общается с компьютером. На самом деле он взаимодействует даже не с самой вычислительной техникой, а всего лишь с прикладным интерфейсом. Поэтому такие программы, как, например, текстовые редакторы и особенно игрушки, имеют среди людей куда большее хождение, чем те же языки программирования.
Дело даже не в том, что люди делятся на программистов и не программистов. Просто как-то глупо каждый день изобретать по велосипеду. Достаточно всего один раз написать алгоритм сохранения файла на диск, чтобы потом хоть всю жизнь этим пользоваться. Более того, если модуль прикрепить к какой-нибудь кнопке или пункту меню с однозначным названием, то все последующие пользователи могут вообще не знать, как оно на самом деле работает. Просто чтобы сохранить файл, следует нажать вон на ту кнопочку...
Боже мой, каким прекрасным мог бы быть этот мир, будь в нем хоть маленькая толика постоянства и совершенства. Мечты-мечты, где ваша сладость. На самом деле этот мир далек от совершенства, что не позволяет все вокруг раз и на всегда запрограммировать и упорядочить. Поэтому рано или поздно каждый пользователь вычислительной техники сталкивается с непростой дилеммой: взяться за программирование или "ну его, и так можно обойтись". Выбор осложняется еще и тем, что спросить совета особенно не у кого, скудных знаний английского не хватает для уверенного чтения сопроводительных хэлпов, а русскоязычные книжки напоминают французский справочник по ядерной физике, переведенный на один из редко используемых диалектов тайского и законспектированный на слух малограмотным зулусом, не владеющим ни одним языком, кроме родного.
К сожалению, послать программирование подальше не всегда представляется возможным. Во всяком случае не в Microsoft Excel. Как ни печально, но электронные таблицы, при всей универсальности технологии, не умеют самостоятельно думать. Им требуются постоянные подсказки, которыми как раз и служат инструкции языка программирования. Единственное, чем могу обрадовать, в Microsoft Excel все же можно обойтись без знания Visual Basic и с успехом справляться средствами одних лишь макроинструкций, в простонародье именуемых формулами.
Давайте рассмотрим простой пример. В ваше распоряжение поступила предлинная таблица с массой статистических цифр, например, по количеству и распределению дохода от проката видеокассет в местном ларьке. Ну что вы улыбаетесь, будем считать, что вам повезло и с чистого листа начинать не приходится. Предположим, что таблица имеет два столбца. В одном указывается возраст клиента, а во втором - величина суммы, которую он обычно тратит на прокат этих самых видеокассет в месяц . Для примера я набрал несколько тестовых строк. Чтобы опробовать принцип, этого достаточно. Опять же предположим, что вам требуется дать начальству ответ на интересующий его вопрос: на какую возрастную категорию, с точки зрения доходности, следует сейчас ориентироваться прежде всего. От этого будет зависеть не только конкретно ваша зарплата (если она вообще от чего-нибудь зависит), но и более глобальные понятия, например ассортимент данного конкретного проката. Дети смотрят одно, их родители - другое, родители родителей - третье, а чтобы сразу на всех разных фильмов закупить - тут уж у начальства денег не хватает, да и нерентабельно.
Руководство, как всегда, желает результат получить немедленно, в крайнем случае - минут через пятнадцать. Таким образом ручная обработка данных не подходит. При небольших размерах таблицы можно попробовать отсортировать ее по возрастанию значений в столбце "Возраст", а потом просто просуммировать уже более или менее упорядоченные фрагменты. Однако, если такая работа повторяется часто и таблицы большие (например, несколько тысяч строк), то все же проще воспользоваться встроенными макроформулами. Проще и быстрее. Кроме того, макроформулы обеспечат еще и гибкость результата. Мало ли кто и какие ошибки допустил при наборе данных. Не слишком потом будет весело все пересчитывать заново, если будет найдено хотя бы одно несоответствие. А так таблица сама все пересчитает при малейшем изменении входных данных.
Ну, что ж, приступим. Так как я уже малость ориентируюсь в формулах, то буду сразу использовать нужные, не объясняя почему. Для подробной инструкции и всей газеты не хватит, к тому же все это есть (в смысле информации, а не моих пояснений) в справочной системе, которую я настоятельно рекомендую почитать. Уверяю вас, там найдется много всего интересного, способного увлечь не на один десяток длинных вечеров.
А пока, задача - организовать суммирование по условию. Суммировать надо значение полей в столбце "Сумма в мес.", исходя их значения полей в столбце "Возраст, лет". Собственно, невооруженным глазом видно, что решение следует искать среди функций для баз данных, потому как значение соседних строк в обоих столбцах взаимосвязаны, что и является основным критерием базы данных.
Чтобы в дальнейшем компьютер понял, что от него хотят, условия для суммирования также должны быть похожи на базу данных. С той лишь разницей, что из двух взаимосвязанных полей одно (то есть критерий отбора) уже заполнено, а второе (сумма полученного дохода в конкретной возрастной категории) пока еще пусто и его еще только предстоит заполнить. Для этого стоит добавить в таблицу еще два столбца . Как вы видите, определились три возрастные категории столбца D (до двадцати, до тридцати и до сорока лет).
Теперь пора заняться первым прикосновением к программированию. Это, конечно, не Visual Basic и не Visual C, однако работает не менее эффективно. Выделяем ячейку Е2, в которой должна будет потрудится создаваемая нами формула, и вызываем мастера функций (команда "ФУНКЦИЯ" меню "ВСТАВКА" или соответствующая экранная кнопка на панели инструментов) . В окне мастера функций, слева, расположены названия категорий, а справа - входящие в эти категории функции. Как я уже говорил, интересующая нас задача относится к категории баз данных, стало быть там же следует и искать решение. Им является функция БДСУММ, которая суммирует содержимое полей тех записей, критерий которых соответствует заданному условию. Если вам интересно, нажмите мышью на квадратик с вопросом в левом нижнем углу окна мастера и получите развернутую справку по данной функции, с примерами и рекомендациями. А пока поверьте на слово, БДСУММ - это именно то, что нужно.
Когда искомая функция найдена (т.е. выделена), смело нажимайте на "ОК" - попадете в мастер самой функции . Вы потом почитаете хэлп, а пока "делай, как я". В поле "База_данных" следует стандартным образом (помните, я уже рассказывал о тайном смысле кнопочки в правой части окна ввода) ввести координаты области А1:В10, которая и является областью базы данных. При этом она захватывает и наименования столбцов, это принципиальный момент. В своей работе программа оперирует не строками и ячейками, а названиями полей, формируемыми на основе имен столбцов. В поле "Поле" следует ввести с клавиатуры и обязательно в кавычках "Сумма в мес.". Это подскажет мастеру, что складывать предстоит непосредственно содержимое этого столбца. Я, по натуре, человек ленивый, и чтобы по много раз не писать одно и то же, просто указываю в этом поле адрес ячейки В1, а мастер сам "подхватывает" ее содержимое и соображает, что в ней расположен текст, который и является именем нужного столбца. К тому же компьютер - штука до боли буквоедская, стоит ошибиться при ручном наборе даже в одной запятой, и он откажется нормально работать. Таким образом "ход конем" через указание адреса, вместо написания текста, приобретает и второй смысл. В поле "Критерий" следует указать адрес ячейки, откуда берется критерий отбора. В данном случае им является диапазон D1:D2, в котором первая ячейка содержит текстовое наименование поля, по значению которого следует производить отбор, а последнее - конкретное значение условия отбора. Таким образом из моей инструкции следует, что программе нужно просуммировать содержимое всех записей поля "Сумма в мес." и поля "Возраст, лет", которых меньше или равно двадцати.
Теперь нажмите "ОК" на окне мастера и посмотрите на результат. Если вы ничего не напутали, то напротив ячейки с надписью "20" появится значение семьдесят шесть. Для контроля можете проверить в ручном режиме с калькулятором. Действительно, согласно таблице примера все, кому еще не исполнилось двадцати одного года, вместе потратили на аренду видеокассет в вашем пункте проката именно семьдесят шесть долларов. Если повторить все перечисленные действия с соответствующими поправками для ячеек Е3 и Е4, то таблица примет вполне законченный вид . Как можно увидеть, в возрастной категории до сорока лет было потрачено всего двести девяносто долларов, что свидетельствует о правильности работы полученной инструкции, так как более старых клиентов у вас не было, а сумма всех "баксов" в таблице тоже равна двумстам девяноста.
Ну вот, все почти что и закончено. Помните, начальству-то хотелось получить результат не совсем такой, как у нас. Ему бы категории подкорректировать, чтобы в условиях значилось не только "до", но еще и "от", например от двадцати до тридцати, и так далее. Эта задачка решается достаточно просто, причем двумя способами. Первый, наиболее приемлемый в моем примере, это просто от каждого последующего результата отнять сумму предыдущих. То есть в категории "до тридцати" формулу стоит слегка откорректировать, добавив к ней простенький фрагмент "- Е2", что уменьшит результат на величину расходов предыдущей категории. Значение "до сорока", соответственно, уменьшается на Е2 и Е3. Для контроля сложите вместе итоги по каждой категории, должно получиться ровно двести девяносто. Что и требовалось доказать.
Правда, я признаю, что наиболее изящным является все же второй способ, когда вообще всю работу проделает за вас сам мастер. Но для этого придется несколько переделать исходную таблицу, в смысле расположения полей с условиями отбора для суммирования . Если критерии расположить в строку (в пример с А1 по В2), то мастер все сделает сам. Он сначала выберет из базы все строки, соответствующие первому условия (то есть до тридцати лет), а потом отбросит те из выбранных, которые не соответствуют второму условию (старше двадцати). Этот способ красивее, что уж тут говорить, особенно, когда нужно производить сортировку не по одному параметру (в моем примере - возраст клиента), а сразу по нескольким (например, род занятий, место жительства, партийная принадлежность, жанр просматриваемых фильмов и так далее). Только помните, что при чередовании ячеек с условиями отбора следует руководствоваться элементарной логикой и сначала располагать все "открывающие" критерии (в нашем случае - граница "ДО"), а лишь потом, в порядке важности, "закрывающие". В противном случае может получиться так, что отработка самой первой пары верхнего и нижнего условий полностью выбросит все поля, как не соответствующие требованию, и все следующие критерии вообще не будут проверены. Скажу вам по большому секрету, самой важной профессиональной тайной (или талантом - кому как) настоящего программиста является способность понять, в конце-то концов, как НА САМОМ ДЕЛЕ работает только что написанная программа.
Александр Запольскис
E-mail: leshy@nestor.minsk.by - титульная страница
Да, именно так. Как гласит старая народная пословица, сколько веревочке не виться, а конец всегда будет. Это я к тому, что настало время мне вас огорчить. Как вы думаете, чем отличаются всякие там системные программисты и администраторы от всех остальных пользователей? Насколько мне кажется, ответ прост, как божий день. Человек нормальный, общаясь с компьютером, очень похож на классического бравого капитана эпохи парусного флота. В те времена начальство интересовалось лишь тем, что расположено над палубой. Все, что было ниже, в трюмах например, было недостойно внимания, чтобы узреть весь личный состав, даже была придумана такая команда - свистать всех наверх. Рядовой пользователь примерно так же общается с компьютером. На самом деле он взаимодействует даже не с самой вычислительной техникой, а всего лишь с прикладным интерфейсом. Поэтому такие программы, как, например, текстовые редакторы и особенно игрушки, имеют среди людей куда большее хождение, чем те же языки программирования.
Дело даже не в том, что люди делятся на программистов и не программистов. Просто как-то глупо каждый день изобретать по велосипеду. Достаточно всего один раз написать алгоритм сохранения файла на диск, чтобы потом хоть всю жизнь этим пользоваться. Более того, если модуль прикрепить к какой-нибудь кнопке или пункту меню с однозначным названием, то все последующие пользователи могут вообще не знать, как оно на самом деле работает. Просто чтобы сохранить файл, следует нажать вон на ту кнопочку...
Боже мой, каким прекрасным мог бы быть этот мир, будь в нем хоть маленькая толика постоянства и совершенства. Мечты-мечты, где ваша сладость. На самом деле этот мир далек от совершенства, что не позволяет все вокруг раз и на всегда запрограммировать и упорядочить. Поэтому рано или поздно каждый пользователь вычислительной техники сталкивается с непростой дилеммой: взяться за программирование или "ну его, и так можно обойтись". Выбор осложняется еще и тем, что спросить совета особенно не у кого, скудных знаний английского не хватает для уверенного чтения сопроводительных хэлпов, а русскоязычные книжки напоминают французский справочник по ядерной физике, переведенный на один из редко используемых диалектов тайского и законспектированный на слух малограмотным зулусом, не владеющим ни одним языком, кроме родного.
К сожалению, послать программирование подальше не всегда представляется возможным. Во всяком случае не в Microsoft Excel. Как ни печально, но электронные таблицы, при всей универсальности технологии, не умеют самостоятельно думать. Им требуются постоянные подсказки, которыми как раз и служат инструкции языка программирования. Единственное, чем могу обрадовать, в Microsoft Excel все же можно обойтись без знания Visual Basic и с успехом справляться средствами одних лишь макроинструкций, в простонародье именуемых формулами.
Давайте рассмотрим простой пример. В ваше распоряжение поступила предлинная таблица с массой статистических цифр, например, по количеству и распределению дохода от проката видеокассет в местном ларьке. Ну что вы улыбаетесь, будем считать, что вам повезло и с чистого листа начинать не приходится. Предположим, что таблица имеет два столбца. В одном указывается возраст клиента, а во втором - величина суммы, которую он обычно тратит на прокат этих самых видеокассет в месяц . Для примера я набрал несколько тестовых строк. Чтобы опробовать принцип, этого достаточно. Опять же предположим, что вам требуется дать начальству ответ на интересующий его вопрос: на какую возрастную категорию, с точки зрения доходности, следует сейчас ориентироваться прежде всего. От этого будет зависеть не только конкретно ваша зарплата (если она вообще от чего-нибудь зависит), но и более глобальные понятия, например ассортимент данного конкретного проката. Дети смотрят одно, их родители - другое, родители родителей - третье, а чтобы сразу на всех разных фильмов закупить - тут уж у начальства денег не хватает, да и нерентабельно.
Руководство, как всегда, желает результат получить немедленно, в крайнем случае - минут через пятнадцать. Таким образом ручная обработка данных не подходит. При небольших размерах таблицы можно попробовать отсортировать ее по возрастанию значений в столбце "Возраст", а потом просто просуммировать уже более или менее упорядоченные фрагменты. Однако, если такая работа повторяется часто и таблицы большие (например, несколько тысяч строк), то все же проще воспользоваться встроенными макроформулами. Проще и быстрее. Кроме того, макроформулы обеспечат еще и гибкость результата. Мало ли кто и какие ошибки допустил при наборе данных. Не слишком потом будет весело все пересчитывать заново, если будет найдено хотя бы одно несоответствие. А так таблица сама все пересчитает при малейшем изменении входных данных.
Ну, что ж, приступим. Так как я уже малость ориентируюсь в формулах, то буду сразу использовать нужные, не объясняя почему. Для подробной инструкции и всей газеты не хватит, к тому же все это есть (в смысле информации, а не моих пояснений) в справочной системе, которую я настоятельно рекомендую почитать. Уверяю вас, там найдется много всего интересного, способного увлечь не на один десяток длинных вечеров.
А пока, задача - организовать суммирование по условию. Суммировать надо значение полей в столбце "Сумма в мес.", исходя их значения полей в столбце "Возраст, лет". Собственно, невооруженным глазом видно, что решение следует искать среди функций для баз данных, потому как значение соседних строк в обоих столбцах взаимосвязаны, что и является основным критерием базы данных.
Чтобы в дальнейшем компьютер понял, что от него хотят, условия для суммирования также должны быть похожи на базу данных. С той лишь разницей, что из двух взаимосвязанных полей одно (то есть критерий отбора) уже заполнено, а второе (сумма полученного дохода в конкретной возрастной категории) пока еще пусто и его еще только предстоит заполнить. Для этого стоит добавить в таблицу еще два столбца . Как вы видите, определились три возрастные категории столбца D (до двадцати, до тридцати и до сорока лет).
Теперь пора заняться первым прикосновением к программированию. Это, конечно, не Visual Basic и не Visual C, однако работает не менее эффективно. Выделяем ячейку Е2, в которой должна будет потрудится создаваемая нами формула, и вызываем мастера функций (команда "ФУНКЦИЯ" меню "ВСТАВКА" или соответствующая экранная кнопка на панели инструментов) . В окне мастера функций, слева, расположены названия категорий, а справа - входящие в эти категории функции. Как я уже говорил, интересующая нас задача относится к категории баз данных, стало быть там же следует и искать решение. Им является функция БДСУММ, которая суммирует содержимое полей тех записей, критерий которых соответствует заданному условию. Если вам интересно, нажмите мышью на квадратик с вопросом в левом нижнем углу окна мастера и получите развернутую справку по данной функции, с примерами и рекомендациями. А пока поверьте на слово, БДСУММ - это именно то, что нужно.
Когда искомая функция найдена (т.е. выделена), смело нажимайте на "ОК" - попадете в мастер самой функции . Вы потом почитаете хэлп, а пока "делай, как я". В поле "База_данных" следует стандартным образом (помните, я уже рассказывал о тайном смысле кнопочки в правой части окна ввода) ввести координаты области А1:В10, которая и является областью базы данных. При этом она захватывает и наименования столбцов, это принципиальный момент. В своей работе программа оперирует не строками и ячейками, а названиями полей, формируемыми на основе имен столбцов. В поле "Поле" следует ввести с клавиатуры и обязательно в кавычках "Сумма в мес.". Это подскажет мастеру, что складывать предстоит непосредственно содержимое этого столбца. Я, по натуре, человек ленивый, и чтобы по много раз не писать одно и то же, просто указываю в этом поле адрес ячейки В1, а мастер сам "подхватывает" ее содержимое и соображает, что в ней расположен текст, который и является именем нужного столбца. К тому же компьютер - штука до боли буквоедская, стоит ошибиться при ручном наборе даже в одной запятой, и он откажется нормально работать. Таким образом "ход конем" через указание адреса, вместо написания текста, приобретает и второй смысл. В поле "Критерий" следует указать адрес ячейки, откуда берется критерий отбора. В данном случае им является диапазон D1:D2, в котором первая ячейка содержит текстовое наименование поля, по значению которого следует производить отбор, а последнее - конкретное значение условия отбора. Таким образом из моей инструкции следует, что программе нужно просуммировать содержимое всех записей поля "Сумма в мес." и поля "Возраст, лет", которых меньше или равно двадцати.
Теперь нажмите "ОК" на окне мастера и посмотрите на результат. Если вы ничего не напутали, то напротив ячейки с надписью "20" появится значение семьдесят шесть. Для контроля можете проверить в ручном режиме с калькулятором. Действительно, согласно таблице примера все, кому еще не исполнилось двадцати одного года, вместе потратили на аренду видеокассет в вашем пункте проката именно семьдесят шесть долларов. Если повторить все перечисленные действия с соответствующими поправками для ячеек Е3 и Е4, то таблица примет вполне законченный вид . Как можно увидеть, в возрастной категории до сорока лет было потрачено всего двести девяносто долларов, что свидетельствует о правильности работы полученной инструкции, так как более старых клиентов у вас не было, а сумма всех "баксов" в таблице тоже равна двумстам девяноста.
Ну вот, все почти что и закончено. Помните, начальству-то хотелось получить результат не совсем такой, как у нас. Ему бы категории подкорректировать, чтобы в условиях значилось не только "до", но еще и "от", например от двадцати до тридцати, и так далее. Эта задачка решается достаточно просто, причем двумя способами. Первый, наиболее приемлемый в моем примере, это просто от каждого последующего результата отнять сумму предыдущих. То есть в категории "до тридцати" формулу стоит слегка откорректировать, добавив к ней простенький фрагмент "- Е2", что уменьшит результат на величину расходов предыдущей категории. Значение "до сорока", соответственно, уменьшается на Е2 и Е3. Для контроля сложите вместе итоги по каждой категории, должно получиться ровно двести девяносто. Что и требовалось доказать.
Правда, я признаю, что наиболее изящным является все же второй способ, когда вообще всю работу проделает за вас сам мастер. Но для этого придется несколько переделать исходную таблицу, в смысле расположения полей с условиями отбора для суммирования . Если критерии расположить в строку (в пример с А1 по В2), то мастер все сделает сам. Он сначала выберет из базы все строки, соответствующие первому условия (то есть до тридцати лет), а потом отбросит те из выбранных, которые не соответствуют второму условию (старше двадцати). Этот способ красивее, что уж тут говорить, особенно, когда нужно производить сортировку не по одному параметру (в моем примере - возраст клиента), а сразу по нескольким (например, род занятий, место жительства, партийная принадлежность, жанр просматриваемых фильмов и так далее). Только помните, что при чередовании ячеек с условиями отбора следует руководствоваться элементарной логикой и сначала располагать все "открывающие" критерии (в нашем случае - граница "ДО"), а лишь потом, в порядке важности, "закрывающие". В противном случае может получиться так, что отработка самой первой пары верхнего и нижнего условий полностью выбросит все поля, как не соответствующие требованию, и все следующие критерии вообще не будут проверены. Скажу вам по большому секрету, самой важной профессиональной тайной (или талантом - кому как) настоящего программиста является способность понять, в конце-то концов, как НА САМОМ ДЕЛЕ работает только что написанная программа.
Александр Запольскис
E-mail: leshy@nestor.minsk.by - титульная страница
Компьютерная газета. Статья была опубликована в номере 28 за 1998 год в рубрике soft :: субд
