我在单元格K40中有以下公式：
=CONCATENATE("['",B39,"' => '",B40,"', '",C39,"' => ",C40,", '",D39,"' => '",D40,"', '",E39,"' => '",E40,"', '",F39,"' => '",F40,"', '",G39,"' => '",G40,"', '",H39,"' => '",H40,"', '",I39,"' => '",I40,"', '",J39,"' => '",J40,"'],")
单元格K41中的这个公式：
=CONCATENATE("['",B39,"' => '",B41,"', '",C39,"' => ",C41,", '",D39,"' => '",D41,"', '",E39,"' => '",E41,"', '",F39,"' => '",F41,"', '",G39,"' => '",G41,"', '",H39,"' => '",H41,"', '",I39,"' => '",I41,"', '",J39,"' => '",J41,"'],")
如您所见，对于每一行，每个奇数字段都需要保持原样，每个偶数字段都需要增加。
给定这两个单元格，我尝试为表中的其余行填写此公式，但对于单元格K42 Excel将其填充为：
=CONCATENATE("['",B41,"' => '",B42,"', '",C41,"' => ",C42,", '",D41,"' => '",D42,"', '",E41,"' => '",E42,"', '",F41,"' => '",F42,"', '",G41,"' => '",G42,"', '",H41,"' => '",H42,"', '",I41,"' => '",I42,"', '",J41,"' => '",J42,"'],")
但它应该填充为：
=CONCATENATE("['",B39,"' => '",B42,"', '",C39,"' => ",C42,", '",D39,"' => '",D42,"', '",E39,"' => '",E42,"', '",F39,"' => '",F42,"', '",G39,"' => '",G42,"', '",H39,"' => '",H42,"', '",I39,"' => '",I42,"', '",J39,"' => '",J42,"'],")
我怎样才能做到这一点？
尝试使用 $ 输入公式，它将修复该行，因此在合并公式时不会对其进行调整：
=CONCATENATE("['",B$39,"' => '",B40,"', '",C$39,"' => ",C40,", '",D$39,"' => '",D40,"', '",E$39,"' => '",E40,"', '",F$39,"' => '",F40,"', '",G$39,"' => '",G40,"', '",H$39,"' => '",H40,"', '",I$39,"' => '",I40,"', '",J$39,"' => '",J40,"'],")