(Электронные таблицы: применяем с пользой; часть 15)
(описание используемых условных обозначений, а также список других публикаций канала по теме электронных таблиц, находится здесь).
Продолжаю публиковать описание некоторых возможностей программ для работы с электронными таблицами, о которых знает не каждый.
1. Полезное дублирование данных из других ячеек
В очень многих организациях электронные таблицы применяются для ведения различной документации, потому что на то они и таблицы, чтобы в них было удобно размещать по столбцам и строкам данные. При этом как-то забывается, что иногда минимальное использование возможностей табличных процессоров позволяет заметно упростить работу. Рассмотрим это на таком незатейливом примере. Пусть на неком предприятии мастером участка ведётся табель учёта рабочего времени, где записываются смены, в которые работают члены бригады из трёх человек, а также количество отработанных ими часов. Допустим также, что подобная таблица для каждого месяца года оформляется в виде отдельного листа книги (файла электронной таблицы).
Легко видеть, что форма табеля такова, что даты в строках 3 и 10 повторяют друг друга. Скорее всего в каждом новом месяце будет делаться следующее: мастер создаст копию листа и проставит новые даты сначала в одной, а затем в другой упомянутой строке. Если же в ячейку “D10” ввести вот такую формулу:
=D3
а затем размножить её маркером заполнения вправо, то простановка новых дат нужна будет только в строке 3, так как в строке 10 эти же даты повторятся автоматически.
Численность и состав бригады также может меняться – в рассматриваемом примере предусмотрена возможность увеличения её до пяти человек. Аналогично, можно было бы в ячейку “C11” ввести формулу
=C4
и размножить её вниз, однако при этом в ячейках “C14” и “C15” стали бы отображаться нули, так как “C7” и “C8” пустые. Поэтому в “C11” (для последующего тиражирования) была бы более уместна другая формула:
=ЕСЛИ(C4="";"";C4)
=IF(C4="";"";C4)
Как нетрудно догадаться, она проверяет, является ли «дублируемая» ячейка пустой – в этом случае формула также выдаст пустую строку, а при наличии данных – выполнит их повторение.
2. Автонумерация
Данная способность табличных процессоров является разновидностью автозаполнения (Пособие, с. 40). Рассмотрим её следующем примере. Допустим, лаборантами для исследований было отобрано 15 образцов воды из родников, у которых далее определили значения общей жёсткости, причём для каждого образца выполнили по три параллельных измерения. Подготовим электронную таблицу, в которую можно занести результаты всех замеров.
Сначала в ячейку “A2” введём «Образец 1»:
Если после ввода эту ячейку выделить, навести курсор мыши на маркер заполнения (Пособие, с. 25) и «протащить» его вниз, то «само собой» получится вот это:
Аналогично программа «догадается», как именно нужно заполнить ячейки, в случае, если в “B1” ввести «Измерение1» (можно даже без пробела) и тоже воспользоваться маркером заполнения:
Итак – основа создаваемой таблицы уже готова и остаётся лишь только заполнить её экспериментальными данными, а также (если необходимо) навести оформительского «лоска» – прорисовать границы ячеек, сделать цветную заливку и т. п.
3. Комментарии в формулах
Во многих языках программирования предусмотрены способы оставлять пояснения в тексте самой программы. Поскольку в электронных таблицах реализуется принцип «программирование без программирования», то было бы несправедливым, если бы в формулах напрочь отсутствовала возможность их же комментировать.
Рассмотрим следующий модельный случай. Пусть на некотором химическом предприятии в технологических процессах используется ряд различных смесей органических растворителей. Смеси по утверждённым рецептурам готовятся работниками отдельного участка, а задание рабочим на приготовление определённого количества конкретной композиции выдаётся мастером этого участка, и документально оформляется в виде соответствующей карточки. Это может быть организовано следующим образом. Мастер открывает файл с созданной в среде электронных таблиц карточкой, которая содержит рецептуру композиции. В карточке указывается значение массы партии, которую предстоит приготовить, и благодаря стоящим в нужных ячейках формулам автоматически рассчитываются необходимые количества компонентов. Далее карточка распечатывается на принтере и отдаётся на исполнение.
В реальных условиях нередка ситуация, когда в силу разных обстоятельств рецептуру приходится несколько модифицировать, например, немного изменять значения концентраций компонентов. Разумеется, сведения о вносимых правках можно записывать где-нибудь отдельно, но электронные таблицы позволяют сделать это прямо в самой карточке, причём так, чтобы внешний её вид оставался прежним. Добиться такого довольно просто: значение концентрации компонента можно представить как результат вычисления по формуле. В приведённом примере с карточкой в ячейке “D10” (численное значение концентрации толуола) может располагаться запись, для просмотра которой достаточно выделить саму ячейку и заглянуть в строку формул (строку ввода):
=55-5+2
Смысл её довольно прост: изначальная рецептура Композиции № 6 содержала 55% толуола, затем (например, по причине временной потребности экономии данного компонента) её уменьшили на 5%, а спустя какое-то время – подняли на 2%.
Для большей ясности внутри формулы можно оставить именно текстовый комментарий с пояснением конкретной причины изменения. Так в ячейке “D13” можно расположить такое выражение:
=10+3*ЕСЛИ(0=0;1;"для снижения производственных затрат - бутилацетат дешевле изопропилки")
=10+3*IF(0=0;1;"для снижения производственных затрат - бутилацетат дешевле изопропилки")
Обратите внимание, как оно работает: использованная в нём функция проверяет истинность условия 0=0 и поскольку оно верно (причём всегда), то функция всегда возвращает значение своего второго аргумента, то есть 1 (единицу), а она далее умножается на величину, на которую пришлось подкорректировать концентрацию бутилацетата. Как легко видеть, третий аргумент (собственно текст комментария) при такой записи никогда не сможет оказаться результатом выполнения функции. Формулу можно записать иначе:
=10+3+ЕСЛИ(1;0;"текст комментария")
=10+3+IF(1;0;"текст комментария")
Синтаксис функции таков, что первым её аргументом является логическое значение ИСТИНА/ЛОЖЬ, вместо которых можно просто указывать числа 1 или 0 . В данном случае вся функция возвращает ноль, который далее прибавляется к стоящим в формуле числам. Ещё один работоспособный вариант для создания «спрятанных» комментариев – использование функции, вычисляющей количество символов в текстовой сроке:
=17-3+0*ДЛСТР("текст комментария")
=17-3+0*LEN("текст комментария")
Текстовый комментарий можно добавить даже в ячейку, содержащую текстовые же данные. Допустим, ранее Композиция № 6 готовилась с использованием абсолютизированного этилового спирта, который затем заменили на изопропиловый. Скрытое напоминание об этом в ячейке “C12” может выглядеть следующим образом:
="Изопропиловый спирт"&ЕСЛИ(1;"";"замена для пресечения нецелевого расхода этанола")
="Изопропиловый спирт"&IF(1;"";"замена для пресечения нецелевого расхода этанола")
Источник (URL): http://shurichimik.narod.ru/compcreative/10-e-tables.htm
Перечень публикаций на канале
См. также: Электронные таблицы: применяем с пользой (часть 3)