(Электронные таблицы: применяем с пользой; часть 12)
(описание используемых условных обозначений, а также список других публикаций канала по теме электронных таблиц, находится здесь).
Дополнение к более ранней публикации, также содержащее описание не всем знакомых возможностей табличных процессоров.
1. Список листов в книге
Листов в книге может быть довольно много, а если вдобавок к этому имена у них длинные, то их ярлычки внизу окна табличного процессора перестанут умещаться. Обычно это затрудняет поиск и переход к нужному листу. Если щёлкнуть правой кнопкой мыши перед ярлычками,
то можно вызвать список всех листов, в котором найти нужный проще:
2. Выпадающий список для заполнения ячеек (проверка данных)
Автоподстановка при заполнении ячеек листа данными (Пособие, с. 17) – вещь удобная, при этом она оставляет пользователю возможность не обращать внимание на предлагаемое программой и вводить информацию свободно. Возможны случаи, когда такое самовольство пользователя должно быть исключено или более приемлемым будет использование при заполнении ячеек выбора значений из уже готового выпадающего списка. Как следует поступать в подобных случаях, мне недавно объяснил один коллега, а я, в свою очередь, хочу поделиться этой информацией с другими.
Итак, допустим нам необходимо заполнять следующую табличку, в которой графа «Элемент» должна содержать упомянутый выше конечный набор наименований:
Сам этот набор (перечень) приведём где-нибудь за пределами таблички:
Выделите ячейку “B2”, после чего сделайте следующее.
На вкладке ленты «Данные» в секции «Работа с данными» выберите команду «Проверка данных...»:
Откроется диалоговое окно «Проверка вводимых значений», где на вкладке «Параметры» укажите сначала тип данных «Список» (1), а затем – в появившемся поле укажите диапазон ячеек, данные которого следует использовать в качестве источника элементов списка (2):
В программном меню выберите команду «Данные» → «Проверка...» и появится диалоговое окно «Проверка вводимых значений», в котором на вкладке «Условие» нужно разрешить использовать информацию из некоторого диапазона ячеек для формирования элементов списка (1), затем указать ссылку на такой источник (2):
После нажатия «ОК» справа от ячейки “B2” появится раскрывающая список кнопка. Маркером заполнения откопируйте эту ячейку вниз настолько, насколько вам нужно – указание на необходимость проверки данных распространится и на эти ячейки тоже:
Теперь для заполнения графы «Элемент» достаточно будет раскрывать список и выбирать нужный его пункт:
Данные в проверяемые ячейки можно вводить и с клавиатуры, но при попытке оставить в них информацию, не соответствующую списку, программа не позволит это сделать, выдав предупреждение.
3. Решение уравнений при помощи опции «Подбор параметра»
У нас на факультете была шутка, что химики признают только две функциональные зависимости – линейную и логарифмическую, ну а то, что не хочет в них укладываться, они линеаризируют и логарифмируют. Данное высказывание несмотря на свой несерьёзный характер указывает на нашу симпатию к градуировочным графикам, имеющим вид прямой линии (Пособие, с. 57). Тем не менее иногда приходится работать с калибровкой, в которой зависимость аналитического сигнала y от концентрации определяемого компонента x совсем нелинейна. Она, например, может быть такой, какая изображена на рисунке ниже и как видно её можно неплохо аппроксимировать полиномом третьей степени, график которого выходит из начала координат (рядом с кривой приведено уравнение полинома с вычисленными по методу наименьших квадратов коэффициентами):
Допустим, для исследуемого образца было получено значение аналитического сигнала y = 3,5 . С учётом того, что математическое выражение y(x) градуировочного графика известно, для нахождения концентрации x в исследуемом образце необходимо решить следующее кубическое уравнение:
0,000764·x³ – 0,041659·x² + 0,807323·x = 3,5
На листе, содержащем результаты замеров аналитического сигнала у серии стандартных образцов, введите в ячейку “A13” какое-нибудь число (например, 1), а в ячейку “B13” – формулу
=0,000764*A13^3-0,041659*A13^2+0,807323*A13
Выглядеть результат может примерно так:
Конечно, вводя вручную в “A13” разные числа, вполне реально добиться того, чтобы результат вычисления по формуле в “B13” давал 3,5 или близкую величину, но гораздо проще и быстрее это сделать, если воспользоваться специальной функцией табличного процессора.
На вкладке «Данные» в секции «Прогноз» раскройте меню кнопки «Анализ "что если"» и выберите команду «Подбор параметра...»:
В программном меню выберите команду «Сервис» → «Подбор параметра...».
Появится небольшое диалоговое окно, в котором нужно будет указать следующие параметры:
После нажатия «ОК» программа попытается самостоятельно найти требующееся значение, по завершении чего выдаст сообщение:
Перечень публикаций на канале