第一组数据（单一数组）：

G4的公式为：=CONCATENATE("[",IF(ISBLANK(A4),"",CONCATENATE("""",A4,"""")),IF(ISBLANK(B4),"",CONCATENATE(",""",B4,"""")),IF(ISBLANK(C4),"",CONCATENATE(",""",C4,"""")),"]")

分解为：

=CONCATENATE("[",
	IF(ISBLANK(A4),"",CONCATENATE("""",A4,"""")),
	IF(ISBLANK(B4),"",CONCATENATE(",""",B4,"""")),
	IF(ISBLANK(C4),"",CONCATENATE(",""",C4,"""")),
	"]")

第二组数据（多重键值对数组，截图中的效果漏了一个双引号和逗号，下面的文字里已修正）：

G12的公式为：=CONCATENATE("[",IF(AND(A12<>"",B12<>""),CONCATENATE("{""name"":""",A12,""",""val"":""",B12,"""}"),""),IF(AND(C12<>"",D12<>""),CONCATENATE(",{""name"":""",C12,""",""val"":""",D12,"""}"),""),IF(AND(E12<>"",F12<>""),CONCATENATE(",{""name"":""",E12,""",""val"":""",F12,"""}"),""),"]")

分解为：

=CONCATENATE("[",  IF(AND(A12<>"",B12<>""),CONCATENATE("{""name"":""",A12,""",""val"":""",B12,"""}"),""),  IF(AND(C12<>"",D12<>""),CONCATENATE(",{""name"":""",C12,""",""val"":""",D12,"""}"),""),  IF(AND(E12<>"",F12<>""),CONCATENATE(",{""name"":""",E12,""",""val"":""",F12,"""}"),""),  "]")

说明：

每列的数据都是选填的，因此判断非空才组装数据；第二组数据中名称和值是对应的，需要联合判断；

ISBLANK("单元格","空的显示内容","非空的显示内容")函数判断是否为空；

AND(条件1=值1,条件2=值2,...)函数多条件判断  <>""表示不等于空；

CONCATENATE(数据1,数据2,数据3...)是拼接函数；

json数据包含双引号，在公式输入的时候两个双引号表示双引号，又因为公式中的字符串需要用双引号包裹，四个双引号代表了一个双引号字符；

如果要把结果显示到另一个工作表中，例如数据录入sheet1表，在sheet2表中如何获取到sheet1表录入的数据：sheet2中输入公式‘=sheet1!A12’，就能输出sheet1表中的A12单元格内容；